如何使用Powershell检查SQL服务器版本?

What's the easiest way to check for the SQL Server Edition and Version using powershell?

使用powershell检查SQL Server版本和版本最简单的方法是什么?

8 个解决方案

#1

Invoke-Sqlcmd -Query "SELECT @@VERSION;" -QueryTimeout 3

http://msdn.microsoft.com/en-us/library/cc281847.aspx

http://msdn.microsoft.com/en-us/library/cc281847.aspx

#2

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
}

#3

[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.

显然，将“.”替换为实例的名称。如果您想查看所有可用的方法，请访问这里。

#4

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
}

#5

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。

#6

To add to Brendan's code.. this fails if your machine is 64-bit, so you need to test appropriately.

加入布伦丹的代码。如果您的机器是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
}

#7

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中安装的最新累积更新/服务包的构建号

#8

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