How do I check for the SQL Server Version using Powershell?

Question

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

score 34 · Answer 1 · 2011-09-29T13:28:35.463

34

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
}

enter image description here

edited Sep 29 '11 at 13:28

answered Sep 29 '11 at 11:47

On my system differs from info in SSMS. – Yarl Feb 08 '19 at 13:43
1

use .PatchLevel instead of .Version. The ...\Setup\Version key isn't the actual version you see from SSMS. – Hiram Apr 19 '19 at 17:05
How can we make it work for remote sql server? $p does not get correct values for remote machines. – Murali Dhar Darshan Oct 26 '20 at 09:40

score 31 · Accepted Answer · answered Sep 28 '11 at 17:28

31

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

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

answered Sep 28 '11 at 17:28

manojlds

290,304
63
469
417

6

requires you to login to the instance. not exactly native PS – Nick Kavadias Mar 30 '15 at 09:14
1

This 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? – Mark Mar 25 '17 at 00:46
1

This 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? – mqutub Dec 10 '18 at 18:54

score 20 · Answer 3 · edited Apr 30 '12 at 20:22

20

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

edited Apr 30 '12 at 20:22

Community

1
1

answered Sep 28 '11 at 18:32

Ben Thul

31,080
4
45
68

1

This is great because it allows you to then easily use the version number (or whatever you want) in the rest of your script. – Sean Long Nov 13 '14 at 18:51
Works great, however, the user (running the script) must be able to authenticate (e.g. using "Windows authentication" to run this code as it is). – Eddie Kumar Jun 04 '19 at 16:18
Yep. 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. – Ben Thul Jun 04 '19 at 17:06

brendan62269 · Answer 4 · 2012-12-14T03:44:16.877

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
}

score 3 · Answer 5 · answered Jan 19 '16 at 15:51

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
}

score 3 · Answer 6 · edited Oct 27 '16 at 20:22

3

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

edited Oct 27 '16 at 20:22

Warren P

65,725
40
181
316

answered Aug 31 '16 at 06:05

Mohamed

806
13
30

Have you heard of pasting TEXT in when you want to share code or commands? It's l33t. – Warren P Oct 27 '16 at 20:20

score 2 · Answer 7 · answered Dec 10 '18 at 18:16

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.

To get this to work, replace "YourInstanceNameHere" with the name of your instance. Don't touch the $ if you do it won't work.

$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/mqutub and I didn't want it to go uncredited.

Adriano Carneiro · Answer 8 · 2011-09-28T17:29:37.400

1

All you need is to connect to SQL Server and run this query:

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

edited Sep 28 '11 at 17:29

answered Sep 28 '11 at 17:23

Adriano Carneiro

57,693
12
90
123

James K. · Answer 9 · 2022-07-29T12:07:43.440

Just an expansion of Ben Thul's answer, It loops through a list of all my DB Servers and prints out the current version of the database engine:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

$computers = @(‘XXXX-OMG-DB-01’,’XXXX-PRO-DB-01’,’XXXX-PRO-DB-02’,
               ’XXXX-QAT-DB-01', 'XXXX-TST-DB-01’,'YYYY-PRO-DB-01',
               'YYYY-PRO-DB-02','YYYY-QAT-DB-01','YYYY-QAT-DB-02',
               'YYYY-TST-DB-01','ZZZZ-DEV-DB-01','ZZZZ-DEV-DB-02')

$computers | % { 
    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $_ 
    if ($null -eq $srv.ComputerNamePhysicalNetBIOS) {
        $s = $_.tostring() + ' is unavailable'
        $s.tostring()
    } else {
        $srv.ComputerNamePhysicalNetBIOS + ' ' +
        $srv.VersionString + ' ' +
        $srv.DatabaseEngineEdition 
    }
 }

score -1 · Answer 10 · answered Feb 04 '15 at 05:35

Well, here's the old school way, that's easy:

sqlcmd -Q "select @@version;"

And here's how I use it from 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

How do I check for the SQL Server Version using Powershell?

10 Answers10

Linked