How to Check SQL Server Version?

In this article, we will look at several ways to find out the version and edition of the installed Microsoft SQL Server instance. For example, you are a DBA and you have been given the task of scheduling an upgrade on one of the SQL Server hosts. The first thought that comes to your mind is “How do I find out what version and edition of SQL Server is being used on a particular server?”.

We will look at some of the simple ways to get the installed versions of SQL Server. These methods are applicable to all editions of MS SQL Server.

Check MS SQL version in SQL Server Management Studio

Run the SQL Server Management Studio (SSMS) and open the properties of your instance. The version and build number of SQL Server are listed on the General tab.

Use the Transact SQL Query: @@version

Start the SQL Server Management Studio console and log in to the server. Right-click on the server and select New Query. Enter the following code in the query window:

select @@version

Click Execute. The query result will contain complete information about the installed SQL edition, version number, and environment (operating system, etc.) and look something like this.

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) Enterprise Edition (64-bit) on Windows NT 6.3 <X64>

SELECT @@version does not return information about the Service Pack installed. To identify the service pack, go to http://support.microsoft.com/kb/321185/en and look for the version number. In this example, version 12.0.5000.0 means that SP2 is installed.

Table of correspondence between the builds and versions of MS SQL:

You can also run the SELECT @@VIERSION command and get the SQL Server version from the command line:

sqlcmd -S ServerName\InstanceName -E -Q "SELECT @@VERSION"

If the SQL PowerShell module is installed on the computer, run the command:

Invoke-SqlCmd -query "select @@version" -ServerInstance "localhost"

If you need to get the version, edition, and the SP that is installed in a tabular form, you can use the following T-SQL query:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

You can also use the built-in xp_MSVer procedure to get the MSSQL version value:

EXECUTE xp_MSVer 'ProductVersion'
EXECUTE xp_MSVer 'ProductName'

Proceed to the next method if you do not have sufficient rights to authorize using SQL Server Management Studio.

SQL version in sqlservr.exe file properties

This is probably the easiest way. Just navigate to the directory with the SQL Server executables. For example, it could be a directory C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn. Find the sqlservr.exe file and open its properties. Go to the Details tab. The Product version and Product name fields contain the version and edition of the SQL Server.

Product name: Microsoft SQL Server
Product version: 12.0.5000.0

You can also determine the MS SQL Server version from the ERROR log. By default on Windows it is located here Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG

You can view log file changes using PowerShell

Get-Content C:\ Program Files\Microsoft SQL Server\MSSQL.13\MSSQL\LOG\ERRORLOG -Tail 100 | where { $_ -match "Microsoft SQL"}