Comment puis-je vérifier la version de SQL Server à l'aide de Powershell?

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

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

Juste une option en utilisant le registre, j'ai trouvé qu'il peut être plus rapide sur certains de mes systèmes:

 $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 } 

 [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" "." $srv.Version $srv.EngineEdition 

Évidemment, remplacez "." avec le nom de votre instance. Si vous voulez voir toutes les methods disponibles, allez ici .

Hacked up conseil de ce fil (et d'autres), cela est allé dans mon 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.Ssortingng,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.Ssortingng] $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 } 

Tout ce dont vous avez besoin est de vous connecter à SQL Server et d'exécuter cette requête:

 select @@version 

Ceci, bien sûr, fonctionnera pour n'importe quel outil client.

De plus, ceci est également disponible:

 SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') 

Plus de façons de déterminer la version de SQL Server ici: http://support.microsoft.com/kb/321185

Pour append au code de Brendan .. cela échoue si votre machine est en 64 bits, donc vous devez tester de manière appropriée.

 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.Ssortingng,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.Ssortingng] $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 } 

Essaye ça

 Invoke-SqlCmd -query "select @@version" -ServerInstance "localhost" 

Vérifiez toutes les methods disponibles pour get le numéro de version de la dernière mise à jour cumulative / Service Pack installé dans SQL Server

Eh bien, voici la vieille école, c'est facile:

 sqlcmd -Q "select @@version;" 

Et voici comment je l'utilise de 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_listning } 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