Sometimes you need to verify exact version of Microsoft SQL Server and you can do it in a different ways. GUI way of doing this is to access Help > About in Microsoft SQL Server Management Studio which will give you something like this:
Well this is good, but can you readily translate 11.0.5058.0 into SQL Server XXXX SPX? Maybe you can, but I don’t. So the better way is to employ some SQL commands to get this information in more readable form:
SELECT @@VERSION
Will return you something like this (but in online):
Microsoft SQL Server 2012 – 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
Well at least it does show you that 11.0.5058.0 is SQL Server 2012, but doesn’t disclose what SP level you have… Then next command (works starting from SQL Server 2000) will help you:
SELECT
SERVERPROPERTY('productversion'),
SERVERPROPERTY('productlevel'),
SERVERPROPERTY('edition')
This returns following details (build, SP level, edition):
11.0.5058.0 SP2 Enterprise Edition (64-bit)
But other frequent scenario if when you don’t have access to the server and have only build number provided to you, here you may refer to the following comprehensive list of SQL Server builds resolved to product name and SP/update level (pay attention that below on that page you may find the links for the similar data on Exchange and SharePoint though these two list don’t look as impressive as the list of SQL Server versions):
http://sqlserverbuilds.blogspot.ru
This should be enough to establish exact SQL version.
As a bonus there are couple of alternative way of checking SQL server version information. PowerShell way (almost).
1) Using Invoke-sqlcmd, strictly speaking is just use of old good sqlcmd but inside of PowerShell – so this is not a pure PowerShell method, bur rather an example of how to execute SQL query in PowerShell:
# First line only required to query named instance # No need to use it for default instance on localhost Set-Location SQLSERVER:\SQL\MyComputer\MyInstance Invoke-Sqlcmd -Query "SELECT @@VERSION;" -QueryTimeout 3
It should look similar to this (and the build on the screenshot is SQL Server 2014 SP1):
2) Reading relevant registry keys with PowerShell (this is a bit cooler as it enumerates your instances):
$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
}
I took this script from Shawn Melton‘s answer to “How do I check for the SQL Server Version using Powershell?” question on stackoverflow.com
Here is an output from my test server with two instances:
