Thursday 29 August 2013

SQL Service Pack Versions

I know this information is available in multiple other places, but I can never find it when I need it, so I'm putting it here for my benefit ... and for anyone who stumbles across this!

First up, it is possible to determine the service pack level directly from SQL using the SERVERPROPERTY function with the 'productlevel' key, for example:

SELECT @@SERVERNAME AS "SQLInstance" , 
SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') AS "Server", 
SERVERPROPERTY ('ProductVersion') AS "Build", 
SERVERPROPERTY ('edition') AS "Edition", 
SERVERPROPERTY ('productlevel') AS "Patch" 

would return results similar to

SQLInstance Server Build Edition Patch
GRUMPY\DBA GRUMPYCLUSTER1 10.50.4000.0 Enterprise Edition (64-bit) SP2

Loop through all your servers and you can quickly keep track of which servers are at what build, and what needs patching.

However, if you just want to check what patch level a certain build number equates to (since you can see that as soon as you connect in SSMS), here are the major releases and their version numbers:

SQL 2000 Shiloh
8.00.194 RTM
8.00.384 SP1
8.00.534 SP2
8.00.760 SP3
8.00.2039 SP4
SQL 2005 Yukon
9.00.1399 RTM
9.00.2047 SP1
9.00.3042 SP2
9.00.4035 SP3
9.00.5000 SP4
SQL 2008 Katmai
10.00.1600 RTM
10.00.2531 SP1
10.00.4000 SP2
10.00.5500 SP3
10.00.6000 SP4
SQL 2008 R2 Kilimanjaro
10.50.1600 RTM
10.50.2500 SP1
10.50.4000 SP2
10.50.6000 SP3
SQL 2012 Denali
11.00.2100 RTM
11.00.3000 SP1
11.00.5058 SP2
11.00.6020 SP3
SQL 2014 Hekaton
12.00.2000 RTM
12.00.4050 SP1 (original)
12.00.4100 SP1 (re-released)
SQL 2016
13.00.200 CTP