By: Greg Robidoux Final Updated: 2018-01-16 Comments (23)Associated Tips: 1 2 3 Even more gt;SQL Machine Options
Oddly enough, you go to Add/Remove Programs - Microsoft SQL Server 2005 - Change - Choose an instance then click the 'Report' button in the right-hand corner. It will show version, edition, patch level, and language.
Problem
One concern that I feel often experienced with is identifying what program pack will be set up on the SQL Server. In this suggestion we look at how to find the version of SQL Machine that is running and furthermore what service package or hotfix can be installed.
Answer
There are usually a several ways to figure out the version of SQL Server that is definitely installed which we will cover in this suggestion.
How to tell what version of SQL Server you are running?
One easy method to do this is to run SELECT @@version
Another option is usually to correct click on on the SQL Machine instance title in SSMS and select Attributes
You can furthermore use the SERVERPROPERTY functionality to get details about the version of SQL Server
Examine out all of the methods in this suggestion.
Technique 1 - to find SQL Machine version
The 1st will be by making use of either Business Manager or SQL Server Management Recording studio and correct clicking on the example name and selectingQualities. In the general area you will see information such as on the right after screenshots. The 'Item version' or 'Version' provides you a number of the version that will be set up. As you can discover with the SQL Machine 2000 screenshot it also shows you the service pack that can be set up.
This will appear equivalent for all of these variations.
Technique 2 - to discover SQL Server version
Another choice is definitely to operate the T-SQL command to provide you this information. As you can discover the result from the various versions can be pretty much the exact same. The one factor that you do not obtain when you do this will be the Services Pack name like you perform in Organization Supervisor.
When this can be run it will provide you details like as the using:
SQL Machine 2000
Microsoft SQL Machine 2000 - 8.00.760 (Intel A86) December 17 2002 14:22:05 Copyright (chemical) 1988-2003 Microsoft Corporation Creator Edition on Windows NT 5.1 (Construct 2600: Services Group 2)
SQL Machine 2005
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel A86) April 14 2005 00:33:37 Copyright (d) 1988-2005 Microsoft Corporation Programmer Edition on Windows NT 5.1 (Construct 2600: Assistance Pack 2)
SQL Server 2008
Microsoft SQL Server 2008 (SP1) - 10.0.2573.0 (Times64) Feb 4 2011 11:27:06 Copyright (m) 1988-2008 Microsoft Company Designer Version (64-bit) on Windows NT 6.1(Build 7601: Assistance Package 1)
SQL Machine 2008 Ur2
Microsoft SQL Server 2008 L2 (RTM) - 10.50.1600.1 (A64) Apr 2 2010 15:48:46 Copyright (chemical) Microsoft Corporation Enterprise Release (64-bit) on Home windows NT 6.0 lt;X64gt; (Build 6002: Provider Pack 2)
SQL Server 2012
Microsoft SQL Machine 2012 - 11.0.2100.60 (Times64) Feb 10 2012 19:39:15 Copyright (g) Microsoft Corporation Internet Copy (64-little bit) on Home windows NT 6.1 lt;Back button64gt; (Construct 7601: Service Pack 1) (Hypervisor)
SQL Machine 2014
Microsoft SQL Server 2014 - 12.0.2254.0 (A64) Jul 25 2014 18:52:51 Copyright (d) Microsoft Company Creator Version (64-bit) on Home windows NT 6.1 lt;back button64gt; (Build 7601: Program Group 1) (Hypervisor)
SQL Machine 2016
Microsoft SQL Machine 2016 (RTM) - 13.0.1601.5 (A64) Interest 29 2016 23:23:58 Copyright (chemical) Microsoft Company Programmer Model (64-little bit) on Home windows 10 Professional 6.3 lt;back button64gt; (Build 14393: )
SQL Server 2017
Microsoft SQL Machine 2017 (RTM) - 14.0.1000.169 (Times64) August 22 2017 17:04:49 Copyright (M) 2017 Microsoft Corporation Designer Copy (64-bit) on Home windows 10 Pro 10.0 lt;X64gt; (Construct 16299: )
Method 3 - to find SQL Machine version
Another option to figure out what version of SQL Machine is installed is to appear at the version of the SQL Machine files. This is usually an choice if SQL Server is not really running and you need to find the version.
Open up Windows Explorer and get around to the folder where SQL Machine is installed such as 'C:Plan FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLBinn', for SQL Machine 2008.
Come across filesqlservr.exeand correct click and select qualities and a window will open like the one below. Right here we can discover that this document is version 10.0.2573.0.
Method 4 - to find SQL Machine version
One of our readers, Bill Pomicter, furthermore suggested this technique using the SERVERPROPERTY function.
When run it results results very similar to the right after:
What perform these SQL Machine Version Figures Mean?
So now that you have got this amount like as 9.00.1399.06 or 8.00.760 what do these actually indicate? The first digits relate to the version of SQL Server like as:
8.0 for SQL Server 2000
9.0 for SQL Server 2005
10.0 for SQL Server 2008
10.5 for SQL Machine 2008 Ur2
11.0 for SQL Machine 2012
12.0 for SQL Server 2014
13.0 for SQL Server 2016
14.0 for SQL Machine 2017
Here are a couple of links to other content articles that give you the details of what is installed for SQL Server 6.0, 6.5, 7.0, 2000, 2005, 2008, 2008 L2, 2012, 2014, 2016 and 2017 structured on the version amount that is certainly returned.
SQL Server Plots and Program Pack Info (this is usually the almost all complete list)
Next Methods
Maintain these hyperlinks convenient to figure out what version of SQL Server you are operating
Final Updated: 2018-01-16
About the writer
Greg Robidoux is usually the President of Edgewood Solutions and a co-founder of MSSQLTips.com. View all my guidelines
Related Sources
By: K. Brian Kelley Last Updated: 2011-12-13 Comments (8)Related Suggestions: 1 2 3 Even more gt;SQL Machine Constructions
Issue
I was recently trying to determine what version of SQL Server was installed on a particular server. I found this tip to figure out the version and tried to implement the code in the command word. However, I do not know what all the details indicates. Could someone clarify it? Examine out this tip to understand about the SELECT @@Edition command word in SQL Server.
Alternative
Right here will be the code that I ran from the suggestion:
Allow's appear at the output of this predicament on a specific SQL Server. You'll discover it below:
Looking at the first series of the output, the initial thing we can determine can be the version of SQL Machine: 'Microsoft SQL Machine 2008' in this illustration. If the SQL Server has been recently updated with a SQL Machine service pack, then next to the version you should see the assistance pack level. Right here, we know this server offers had SQL Server 2008 SP3 applied since we find '(SP3)' in the result. If you discover (RTM), then you know that no SQL Server-related assistance pack provides been set up. RTM stands for release-to-manufacturing , which means when the item is initial launched.
Then there is usually a set of four amounts in the format of A.B.M.N. In this case, it's10.0.5500.0. The very first two numbers (10.0) inform us the numeric version of SQL Server. Going back to SQL Server 2000, we possess the pursuing versions, as of the composing of this tip:
SQL Server #
SQL Machine Edition
8.00
SQL Server 2000
9.00
SQL Machine 2005
10.0
SQL Machine 2008
10.5
SQL Machine 2008 R2
The last fifty percent of that set of four amounts corresponds to the construct of SQL Machine at that version and service pack. The best resource to fit up what has been installed on your SQL Machine compared to the version you're also receiving back is usually the SQL Machine Version Data source at SQLSecurity.com. 5500.0 can be SQL Server 2008 SP3 with no cumulative up-dates or hotfixes regarding to the database.
After identifying the specific configuration making use of the version database, the just thing staying on the first line is usually '(Back button64)'. This final piece of info informs us what version of SQL Machine is running with regard to chip architecture. It will be entirely achievable to operate a SQL Machine back button86 installation on an back button64 system. Whatever the situation, if you observe (Times86), that shows you that the x86 version of SQL Machine was set up.
The middle two outlines contain details on when the SQL Machine was created as well as the copyright notice for SQL Machine itself. There'h not a entire great deal to glean here, so let's take a look at that final range. The first issue we find is usually the model of SQL Machine. In this instance, that would end up being 'Creator Release.' Some functions are limited owing to version. For instance, Developer Edition is a specifically licensed model of SQL Machine that enables you to check and make use of Enterprise Edition functions on, say, a development workstation working Home windows 7. Designer Edition is usually by no means to become used for a creation server. To perform so would be a infringement of the licensing contract. Knowing what version of SQL Server is essential. Understanding what edition of SQL Machine for that version is equally essential.
After that we notice confirmation that it is certainly working the 64-little bit version of SQL Machine. It then tells us the operating program. Home windows NT 6.1 corresponds to Windows 7. Therefore this particular SQL Server is running on a workstation. We're also then informed if the Operating-system is usually 32 or 64-bit. In this situation, it's 64-bit. Finally, we notice the build and the support pack version.
This last bit of details is often confused with the SQL Machine version. People see the 'Services Package 1' and think that means they are usually running a version of SQL Machine with SQL Server Service Package 1 applied. The services package version for SQL Server is often going to be on the first collection. The service package version after the operating program refers to the OS. So we're not just working on Home windows 7, but Windows 7 SP1.
Putting all of this jointly, from the concern we can determine that we are usually operating SQL Server 2008 SP3 Builder Edition (64-bit) on the 64-bit version of Home windows 7 SP1. We could drill down in to find if a cumulative update or hotfix has been applied on the SQL Machine side by getting that four part amount (in this situation we've currently performed so with 10.0.5500.0) and comparing it with the version database if that'beds required.
Next Actions
Test performing SELECT @@Edition; on various SQL Web servers to exercise interpreting the results.
Verify this tip to find other methods of identifying the SQL Machine version.
Last Updated: 2011-12-13
About the writer
K. Brian Kelley is certainly a SQL Machine author and columnist concentrating mainly on SQL Machine protection. Look at all my tips