How to tell what SQL Server version you are running
\nBy: Greg Robidoux | Read Comments (14) | Related Tips: 1 | 2 | 3 | More > SQL Server Configurations
\nProblem
\nOne issue that I am often faced with is determining what service pack is installed on the SQL Server. In this tip we look at how to find the version of SQL Server that is running and also what service pack or hotfix is installed.<\/p>\n
Solution
\nThere are a few ways to determine the version of SQL Server that is installed.<\/p>\n
Method 1
\nThe first is by using either Enterprise Manager or SQL Server Management Studio and right clicking on the instance name and selecting properties. In the general section you will see information such as on the following screenshots. The “Product version” or “Version” gives you a number of the version that is installed. As you can see with the SQL Server 2000 screenshot it also shows you the service pack that is installed.<\/p>\n
SQL Server 2000
\nSQL Server 2000 version information<\/p>\n
SQL Server 2005
\nSQL Server 2005 version information<\/p>\n
SQL Server 2008 \/ 2008 R2 \/ 2012 \/ 2014
\nThis will look simiilar for all of these versions.<\/p>\n
SQL Server 2008 version information<\/p>\n
Method 2
\nAnother option is to run the T-SQL command to give you this information. As you can see the output from the different versions is pretty much the same. The one thing that you do not get when you do this is the Service Pack name like you do in Enterprise Manager.<\/p>\n
SELECT @@VERSION
\nWhen this is run it will give you information such as the following:<\/p>\n
SQL Server 2000
\nMicrosoft SQL Server 2000 – 8.00.760 (Intel X86)
\nDec 17 2002 14:22:05
\nCopyright (c) 1988-2003 Microsoft Corporation
\nDeveloper Edition on Windows NT 5.1 (Build 2600: Service Pack 2)<\/p>\n
SQL Server 2005
\nMicrosoft SQL Server 2005 – 9.00.1399.06 (Intel X86)
\nOct 14 2005 00:33:37
\nCopyright (c) 1988-2005 Microsoft Corporation
\nDeveloper Edition on Windows NT 5.1 (Build 2600: Service Pack 2)<\/p>\n
SQL Server 2008
\nMicrosoft SQL Server 2008 (SP1) – 10.0.2573.0 (X64)
\nFeb 4 2011 11:27:06
\nCopyright (c) 1988-2008 Microsoft Corporation
\nDeveloper Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)<\/p>\n
SQL Server 2008 R2
\nMicrosoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64)
\nApr 2 2010 15:48:46
\nCopyright (c) Microsoft Corporation
\nEnterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)<\/p>\n
SQL Server 2012
\nMicrosoft SQL Server 2012 – 11.0.2100.60 (X64)
\nFeb 10 2012 19:39:15
\nCopyright (c) Microsoft Corporation
\nWeb Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)<\/p>\n
SQL Server 2014
\nMicrosoft SQL Server 2014 – 12.0.2254.0 (X64)
\nJul 25 2014 18:52:51
\nCopyright (c) Microsoft Corporation
\nDeveloper Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)<\/p>\n
Method 3
\nAnother option to determine what version of SQL Server is installed is to look at the version of the SQL Server files. This is an option if SQL Server is not running and you need to find the version.<\/p>\n
Open Windows Explorer and navigate to the folder where SQL Server is installed such as “C:\\Program Files\\Microsoft SQL Server\\MSSQL10.SQL2008\\MSSQL\\Binn”, for SQL Server 2008.<\/p>\n
Find file sqlservr.exe and right click and select properties and a window will open like the one below. Here we can see that this file is version 10.0.2573.0.<\/p>\n
SQL Server version from file<\/p>\n
So now that you have this number such as 9.00.1399.06 or 8.00.760 what do these even mean? The first digits refer to the version of SQL Server such as:<\/p>\n
8.0 for SQL Server 2000
\n9.0 for SQL Server 2005
\n10.0 for SQL Server 2008
\n10.5 for SQL Server 2008 R2
\n11.0 for SQL Server 2012
\n12.0 for SQL Server 2014
\nHere are a couple of links to other articles that give you the details of what is installed for SQL Server 6.0, 6.5, 7.0, 2000, 2005, 2008, 2008 R2, 2012 and 2014 based on the version number that is returned.<\/p>\n
SQL Server versions
\nSQL Server Builds and Service Pack Information (this is the most complete list)
\nHow to identify your SQL Server version and edition
\nSQL Server Service packs and Versions
\nSQL Server Service Pack Tips
\nNext Steps
\nKeep these links handy to determine what version of SQL Server you are running<\/p>\n","protected":false},"excerpt":{"rendered":"
How to tell what SQL Server version you are running By: Greg Robidoux | Read Comments (14) | Related Tips: 1 | 2 | 3 | More > SQL Server Configurations Problem One issue that I am often faced with … Read More »<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[34,37,4],"tags":[],"class_list":["post-1835","post","type-post","status-publish","format-standard","hentry","category-mssql","category-sql","category-windows"],"_links":{"self":[{"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1835","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1835"}],"version-history":[{"count":1,"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1835\/revisions"}],"predecessor-version":[{"id":1836,"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1835\/revisions\/1836"}],"wp:attachment":[{"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1835"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1835"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.wildow.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1835"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}