How do I check for the SQL Server Version using Powershell?
110,978
Solution 1
Invoke-Sqlcmd -Query "SELECT @@VERSION;" -QueryTimeout 3
http://msdn.microsoft.com/en-us/library/cc281847.aspx
Solution 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
}
Solution 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.
Solution 4
To add to Brendan's code.. this fails if your machine is 64-bit, so you need to test appropriately.
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
}
Solution 5
Hacked up advice from this thread (and some others), this went in my 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
}
Related videos on Youtube
Author by
Max Alexander
CoFounder of Ditto. A CRDT based NoSQL database written in rust that can sync over wireless peer to peer or client server. Available for iOS, Android, Web, and Servers. Twitter: @mbalex99
Updated on July 09, 2022Comments
-
Max Alexander almost 2 years
What's the easiest way to check for the SQL Server Edition and Version using powershell?
-
Sean Long over 9 yearsThis is great because it allows you to then easily use the version number (or whatever you want) in the rest of your script.
-
Nick Kavadias about 9 yearsrequires you to login to the instance. not exactly native PS
-
Warren P over 7 yearsHave you heard of pasting TEXT in when you want to share code or commands? It's l33t.
-
Mark about 7 yearsThis works for me but the resulting string is truncated. How do I get the entire (multiline) result back into PowerShell as one long (full/complete/non-truncated) string?
-
mqutub over 5 yearsThis also requires the instance to be up. What if the SQL instance has crashed and fails to start up? How will you get the version then?
-
Yarl about 5 yearsOn my system differs from info in SSMS.
-
Hiram about 5 yearsuse .PatchLevel instead of .Version. The ...\Setup\Version key isn't the actual version you see from SSMS.
-
Eddie Kumar almost 5 yearsWorks great, however, the user (running the script) must be able to authenticate (e.g. using "Windows authentication" to run this code as it is).
-
Ben Thul almost 5 yearsYep. If you need to use SQL authentication, the Server SMO class has a constructor that takes a ServerConnection object as well. You can use that to specify your username/password.
-
Murali Dhar Darshan over 3 yearsHow can we make it work for remote sql server? $p does not get correct values for remote machines.