How to Check SQL Server Version When Service is Not Running

By: Sergey Gigoyan | Comments (1) | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > SQL Server Configurations

Problem

We need to know the SQL Server versions and editions installed on a machine. If the SQL Server service is running we can easily get its version using the SQL Server Management Studio Object Explorer or by running the corresponding query. However, this approach will not work if the SQL Server instance is stopped. In this article, we will discuss ways to view SQL Server versions that are not running.

Solution

When SQL Server is running, we can simply connect to the instance and get the version by using the following code:

SELECT @@VERSION

As you can see, the installed version is Microsoft SQL Server 2014 SP3 and the edition is Enterprise Evaluation:

We can also see the version and edition of the instance from the instance's properties (on the "General" tab):

However, when the instance is not running, these methods do not work. Thus, let's move on to discussing our specific problem, checking the version of the SQL Server when it is not running.

Find SQL Server Version using SQL Server Configuration Manager

A way to find the version and edition of the instance is by using the SQL Server Configuration Manager. To do so, right-click on the instance name and choose "Properties":

On the "Advanced" tab we can find the version and the edition:

Find SQL Server Version Using the sqlservr file

Another way to get the SQL Server version is by using the "sqlservr" file. This file is located in the "Binn" folder. In our case, the location is: "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn". This is the default location. Here, the disk drive where SQL Server is installed is "C" and MSSQL12.MSSQLSERVER corresponds to the current version. These can be different for different configurations and versions. We can easily find the "sqlservr" file using File Explorer by typing the file name there. After finding the file, we right-click on it and choose "Properties":

On the "Details" tab, we can see the version of the instance:

Find SQL Server Version using the SQL Server Installation Center

The final option we will discuss today is getting the version and edition of the instances via the SQL Server Installation Center. We can find it on the Start Menu under "Microsoft SQL Server <version>" then selecting "SQL Server <version> Installation Center" as shown below:

Click on it and open the Installation Center. Then, we will select "Tools":

Here we can see that the second link on the right side is called "Installed SQL Server features discovery report". This is exactly what we need:

If we click on it, the discovery report opens and we can see the detailed information about all the installed instances on the machine including their editions and versions:

microsoft sql server installed features report

This method is the most informative compared to the other two methods. This is because it shows not only specific instance-related information, but also information about all installed instances and SQL Server features on the host. Additionally, not only the instances' versions and editions are visible, but also some additional information such as whether the instance is clustered or not. Therefore, if the need is to get detailed information about all instances installed on the host regardless of whether they are running or not, I prefer this method.

Conclusion

When the SQL Server instance is not running, connecting to the instance is not possible and therefore, getting the version using the SQL Server Management Studio is not possible. In these cases, the SQL Server Installation Center can be used to get detailed information about the installed SQL Server versions on the host. The version of SQL Server can also be found in SQL Server Configuration Manager and in the properties of the sqlservr file of the corresponding instance.

Next Steps

For additional information, please use the following links:

How to tell what SQL Server version you are running

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, December 29, 2020 - 12:07:14 AM - Sergey Aleshechkin

Another method is check for registry
Here is the powershell script to read registry and find all instances with version and editions
$sqlServerRoot='Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server'
if (Test-Path "$sqlServerRoot\Instance Names\SQL")
{
$inst=Get-Item -Path "$sqlServerRoot\Instance Names\SQL" | select -ExpandProperty Property
foreach ($i in $inst)
{
$instance_name=(Get-ItemProperty "$sqlServerRoot\Instance Names\SQL").$i
$instance_name
(Get-ItemProperty "$sqlServerRoot\$instance_name\Setup").Edition
(Get-ItemProperty "$sqlServerRoot\$instance_name\Setup").PatchLevel
}
}
if (Test-Path "$sqlServerRoot\Instance Names\OLAP")
{
$inst=Get-Item -Path "$sqlServerRoot\Instance Names\OLAP" | select -ExpandProperty Property
foreach ($i in $inst)
{
$instance_name=(Get-ItemProperty "$sqlServerRoot\Instance Names\OLAP").$i
$instance_name
(Get-ItemProperty "$sqlServerRoot\$instance_name\Setup").Edition
(Get-ItemProperty "$sqlServerRoot\$instance_name\Setup").PatchLevel
}
}
if (Test-Path "$sqlServerRoot\Instance Names\RS")
{
$inst=Get-Item -Path "$sqlServerRoot\Instance Names\RS" | select -ExpandProperty Property
foreach ($i in $inst)
{
$instance_name=(Get-ItemProperty "$sqlServerRoot\Instance Names\RS").$i
$instance_name
(Get-ItemProperty "$sqlServerRoot\$instance_name\Setup").Edition
(Get-ItemProperty "$sqlServerRoot\$instance_name\Setup").PatchLevel
}
}
$verList='90','100','110','120','130','140','150'
foreach ($ver in $verList)
{
if (Test-Path "$sqlServerRoot\$ver\DTS\Setup")
{
"MsDtsServer$ver"
(get-itemproperty "$sqlServerRoot\$ver\DTS\Setup").Edition
(get-itemproperty "$sqlServerRoot\$ver\DTS\Setup").PatchLevel
}
}