如何使用 Powershell 检查 SQL Server 版本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7587077/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I check for the SQL Server Version using Powershell?
提问by Max Alexander
What's the easiest way to check for the SQL Server Edition and Version using powershell?
使用 powershell 检查 SQL Server 版本和版本的最简单方法是什么？
回答by manojlds
Invoke-Sqlcmd -Query "SELECT @@VERSION;" -QueryTimeout 3
回答by manojlds
Just an option using the registry, I have found it can be quicker on some of my systems:
只是使用注册表的一个选项，我发现它在我的某些系统上可以更快：
$inst = (get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
foreach ($i in $inst)
{
$p = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$i
(Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server$p\Setup").Edition
(Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server$p\Setup").Version
}
回答by Ben Thul
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" "."
$srv.Version
$srv.EngineEdition
Obviously, replace "." with the name of your instance. If you want to see all the methods available, go here.
显然，替换“。” 使用您的实例名称。如果您想查看所有可用的方法，请转到此处。
回答by brendan62269
Hacked up advice from this thread (and some others), this went in my psprofile:
从这个线程（和其他一些线程）中提出建议，这在我的 psprofile 中：
Function Get-SQLSvrVer {
<#
.SYNOPSIS
Checks remote registry for SQL Server Edition and Version.
.DESCRIPTION
Checks remote registry for SQL Server Edition and Version.
.PARAMETER ComputerName
The remote computer your boss is asking about.
.EXAMPLE
PS C:\> Get-SQLSvrVer -ComputerName mymssqlsvr
.EXAMPLE
PS C:\> $list = cat .\sqlsvrs.txt
PS C:\> $list | % { Get-SQLSvrVer $_ | select ServerName,Edition }
.INPUTS
System.String,System.Int32
.OUTPUTS
System.Management.Automation.PSCustomObject
.NOTES
Only sissies need notes...
.LINK
about_functions_advanced
#>
[CmdletBinding()]
param(
# a computer name
[Parameter(Position=0, Mandatory=$true)]
[ValidateNotNullOrEmpty()]
[System.String]
$ComputerName
)
# Test to see if the remote is up
if (Test-Connection -ComputerName $ComputerName -Count 1 -Quiet) {
# create an empty psobject (hashtable)
$SqlVer = New-Object PSObject
# add the remote server name to the psobj
$SqlVer | Add-Member -MemberType NoteProperty -Name ServerName -Value $ComputerName
# set key path for reg data
$key = "SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"
# i have no idea what this does, honestly, i stole it...
$type = [Microsoft.Win32.RegistryHive]::LocalMachine
# set up a .net call, uses the .net thingy above as a reference, could have just put
# 'LocalMachine' here instead of the $type var (but this looks fancier :D )
$regKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $ComputerName)
# make the call
$SqlKey = $regKey.OpenSubKey($key)
# parse each value in the reg_multi InstalledInstances
Foreach($instance in $SqlKey.GetValueNames()){
$instName = $SqlKey.GetValue("$instance") # read the instance name
$instKey = $regKey.OpenSubkey("SOFTWARE\Microsoft\Microsoft SQL Server$instName\Setup") # sub in instance name
# add stuff to the psobj
$SqlVer | Add-Member -MemberType NoteProperty -Name Edition -Value $instKey.GetValue("Edition") -Force # read Ed value
$SqlVer | Add-Member -MemberType NoteProperty -Name Version -Value $instKey.GetValue("Version") -Force # read Ver value
# return an object, useful for many things
$SqlVer
}
} else { Write-Host "Server $ComputerName unavailable..." } # if the connection test fails
}
回答by Mohamed El-Qassas MVP
Try this
尝试这个
Invoke-SqlCmd -query "select @@version" -ServerInstance "localhost"
Check all available method to Get the build number of the latest Cumulative Update / Service Pack that has been installed in SQL Server
检查所有可用方法以获取已安装在 SQL Server 中的最新累积更新/服务包的内部版本号
回答by RelativitySQL
Here is a version I cobbled together from some sources here and there*.
这是我从这里和那里的一些来源拼凑而成的版本*。
This version does not hit the registry, does not hit SQL, and doesn't even require that the instance be running. It does require that you know the instance name. If you don't know the instance name, you should be able to trivially work it out from this code.
此版本不访问注册表，不访问 SQL，甚至不需要实例正在运行。它确实要求您知道实例名称。如果您不知道实例名称，您应该能够从这段代码中轻松解决它。
To get this to work, replace "YourInstanceNameHere" with the name of your instance. Don't touch the $if you do it won't work.
要使其正常工作，请将“YourInstanceNameHere”替换为您的实例名称。$如果你这样做不起作用，请不要碰它。
$ErrorActionPreference = "Stop"
$instanceName = "MSSQL`$YourInstanceNameHere"
$sqlService = Get-Service -Name $instanceName
$WMISQLservices = Get-WmiObject -Class Win32_Product -Filter "Name LIKE 'SQL Server % Database Engine Services'" | Select-Object -Property Name,Vendor,Version,Caption | Get-Unique
foreach ($sqlService in $WMISQLservices)
{
$SQLVersion = $sqlService.Version
$SQLVersionNow = $SQLVersion.Split("{.}")
$SQLvNow = $SQLVersionNow[0]
$thisInstance = Get-WmiObject -Namespace "root\Microsoft\SqlServer\ComputerManagement$SQLvNow" -Class SqlServiceAdvancedProperty | Where-Object {$_.ServiceName -like "*$instanceName*"} | Where-Object {$_.PropertyName -like "VERSION"}
}
$sqlServerInstanceVersion = $thisInstance.PropertyStrValue
if ($sqlServerInstanceVersion)
{
$majorVersion = $thisInstance.PropertyStrValue.Split(".")[0]
$versionFormatted = "MSSQL$($majorVersion)"
}
else
{
throw "ERROR: An error occured while attempting to find the SQL Server version for instance '$($instanceName)'."
}
$versionFormatted
*I also received help from and help from this this friend of mine https://stackoverflow.com/users/1518277/mqutuband I didn't want it to go uncredited.
*我也得到了这个朋友的帮助和帮助https://stackoverflow.com/users/1518277/mqutub我不想让它失去信誉。
回答by Adriano Carneiro
All you need is to connect to SQL Server and run this query:
您只需要连接到 SQL Server 并运行此查询：
select @@version
This, of course, will work for any client tool.
这当然适用于任何客户端工具。
Additionally, this is also available:
此外，这也可用：
SELECT SERVERPROPERTY('productversion'),
SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('edition')
More ways to determine the SQL Server version here: http://support.microsoft.com/kb/321185
在此处确定 SQL Server 版本的更多方法：http: //support.microsoft.com/kb/321185
回答by Andy
To add to Brendan's code.. this fails if your machine is 64-bit, so you need to test appropriately.
要添加到 Brendan 的代码中……如果您的机器是 64 位，这将失败，因此您需要进行适当的测试。
Function Get-SQLSvrVer {
<#
.SYNOPSIS
Checks remote registry for SQL Server Edition and Version.
.DESCRIPTION
Checks remote registry for SQL Server Edition and Version.
.PARAMETER ComputerName
The remote computer your boss is asking about.
.EXAMPLE
PS C:\> Get-SQLSvrVer -ComputerName mymssqlsvr
.EXAMPLE
PS C:\> $list = cat .\sqlsvrs.txt
PS C:\> $list | % { Get-SQLSvrVer $_ | select ServerName,Edition }
.INPUTS
System.String,System.Int32
.OUTPUTS
System.Management.Automation.PSCustomObject
.NOTES
Only sissies need notes...
.LINK
about_functions_advanced
#>
[CmdletBinding()]
param(
# a computer name
[Parameter(Position=0, Mandatory=$true)]
[ValidateNotNullOrEmpty()]
[System.String]
$ComputerName
)
# Test to see if the remote is up
if (Test-Connection -ComputerName $ComputerName -Count 1 -Quiet) {
$SqlVer = New-Object PSObject
$SqlVer | Add-Member -MemberType NoteProperty -Name ServerName -Value $ComputerName
$base = "SOFTWARE\"
$key = "$($base)\Microsoft\Microsoft SQL Server\Instance Names\SQL"
$type = [Microsoft.Win32.RegistryHive]::LocalMachine
$regKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $ComputerName)
$SqlKey = $regKey.OpenSubKey($key)
try {
$SQLKey.GetValueNames()
} catch { # if this failed, it's wrong node
$base = "SOFTWARE\WOW6432Node\"
$key = "$($base)\Microsoft\Microsoft SQL Server\Instance Names\SQL"
$regKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $ComputerName)
$SqlKey = $regKey.OpenSubKey($key)
}
# parse each value in the reg_multi InstalledInstances
Foreach($instance in $SqlKey.GetValueNames()){
$instName = $SqlKey.GetValue("$instance") # read the instance name
$instKey = $regKey.OpenSubkey("$($base)\Microsoft\Microsoft SQL Server$instName\Setup") # sub in instance name
# add stuff to the psobj
$SqlVer | Add-Member -MemberType NoteProperty -Name Edition -Value $instKey.GetValue("Edition") -Force # read Ed value
$SqlVer | Add-Member -MemberType NoteProperty -Name Version -Value $instKey.GetValue("Version") -Force # read Ver value
# return an object, useful for many things
$SqlVer
}
} else { Write-Host "Server $ComputerName unavailable..." } # if the connection test fails
}
回答by Sonia Hamilton
Well, here's the old school way, that's easy:
嗯，这是老派的方式，这很简单：
sqlcmd -Q "select @@version;"
And here's how I use it from Serverspec:
这是我从Serverspec使用它的方法：
require 'windows_spec_helper'
describe 'MS SQL Server Express' do
describe service('MSSQLSERVER') do
it { should be_enabled }
it { should be_running }
end
describe port(1433) do
it { should be_listening }
end
describe command('sqlcmd -Q "select @@version;"') do
its(:stdout) { should match /Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)/ }
end
end
