How to find out the version of the installed SQL server?

There are 2 main situations when getting a version of Microsoft SQL Server:

1. You have the ability to connect to the server (via SSMS, sqlcmd or any other client)
2. You do not have the ability to connect to the server, but you have access to the Windows server (or Linux if it is SQL Server 2017)

Consider the first option first. Most of the information is available and maintained up to date in an excellent and highly recommended reading KB321185: Determining the version, release and update level of the SQL Server system and its components

1. Connect to the server via SSMS . Once connected, the object browser will display the release information (in parentheses), along with the user name that is used to connect to a specific instance of SQL Server.

2. Using the query: SELECT @@VERSION; . An example of the output for the latest currently running version of SQL Server 2016 SP1 CU4 (for more information about SQL Server versions and updates, click here ):

    Microsoft SQL Server 2016 (SP1-CU4) (KB4024305) – 13.0.4446.0 (X64) Jul 16 2017 18:08:49 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows … 

3. Query running for all versions of SQL Server since 2000:

    SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion , SERVERPROPERTY('ProductLevel') AS ProductLevel , SERVERPROPERTY('Edition') AS Edition; 

4. An extended version with all possible server properties (taken from the wonderful Glenn Berry diagnostic queries), but some parameters are only available for the latest versions of SQL Server:

    SELECT SERVERPROPERTY('MachineName') AS [MachineName] , SERVERPROPERTY('ServerName') AS [ServerName] , SERVERPROPERTY('InstanceName') AS [Instance] , SERVERPROPERTY('IsClustered') AS [IsClustered] , SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS] , SERVERPROPERTY('Edition') AS [Edition] , SERVERPROPERTY('ProductLevel') AS [ProductLevel] -- What servicing branch (RTM/SP/CU) , SERVERPROPERTY('ProductUpdateLevel') AS [ProductUpdateLevel] -- Within a servicing branch, what CU# is applied , SERVERPROPERTY('ProductVersion') AS [ProductVersion] , SERVERPROPERTY('ProductMajorVersion') AS [ProductMajorVersion] , SERVERPROPERTY('ProductMinorVersion') AS [ProductMinorVersion] , SERVERPROPERTY('ProductBuild') AS [ProductBuild] , SERVERPROPERTY('ProductBuildType') AS [ProductBuildType] -- Is this a GDR or OD hotfix (NULL if on a CU build) , SERVERPROPERTY('ProductUpdateReference') AS [ProductUpdateReference] -- KB article number that is applicable for this build , SERVERPROPERTY('ProcessID') AS [ProcessID] , SERVERPROPERTY('Collation') AS [Collation] , SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled] , SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly] , SERVERPROPERTY('FilestreamConfiguredLevel') AS [FilestreamConfiguredLevel] , SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled] , SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus] , SERVERPROPERTY('InstanceDefaultDataPath') AS [InstanceDefaultDataPath] , SERVERPROPERTY('InstanceDefaultLogPath') AS [InstanceDefaultLogPath] , SERVERPROPERTY('BuildClrVersion') AS [Build CLR Version] , SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported] , SERVERPROPERTY('IsPolybaseInstalled') AS [IsPolybaseInstalled] -- New for SQL Server 2016 , SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS [IsRServicesInstalled] -- New for SQL Server 2016 ; 

5. Starting with the release of SQL Server 2008, you can also use the report on detected installed components of SQL Server. This report can be found on the page Сервис -> Центра установки SQL Server . This tool displays information about all instances of SQL Server installed on the system. These include client tools, such as SQL Server Management Studio. The only thing to remember is that this tool can only be run locally, on a system where an instance of SQL Server is installed. It cannot be used to obtain information about remote servers. Read more here.

Now consider ways to find out the version for the second option when you do not have the ability (or rights) to connect to SQL Server:

1. {InstanceHome}\MSSQL\Binn\sqlservr.exe -v [-s InstanceName]

If only one SQL Server instance is installed on the server, then -s is not needed:

  C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn>sqlservr.exe -v 

Otherwise, you need to specify the name of the instance of interest, for example:

 C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS2014\MSSQL\Binn>sqlservr.exe -v -s SQLEXPRESS2014 

2. If the operating system is Windows, then you can check the registry branch:

    Key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\{VersionNumber}\Tools\Setup Name: Edition 

At the command prompt cmd:

 REG QUERY "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\{VersionNumber}\Tools\Setup" /v /e /f Edition 

3. Through the Powershell script:

    $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 } 

4. Review the first few lines in the error log file for this instance. By default, the error log is placed in the Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n .

For more information, please contact: How do you find SQL Server version / edition without SSMS installed?