How to Check SQL Server Version When Service is Not Running


By:   |   Updated: 2020-12-17   |   Comments (1)   |   Related: 1 | 2 | 3 | 4 | More > 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:

object explorer

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

object explorer

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":

sql server

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

sql server properties

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":

sql server

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

microsoft sql server

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:

microsoft sql server

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

sql server installation center

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:

sql server installation center

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:






get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan 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.

View all my tips


Article Last Updated: 2020-12-17

Comments For This Article




Tuesday, December 29, 2020 - 12:07:14 AM - Sergey Aleshechkin Back To Top (87962)
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
}
}


download














get free sql tips
agree to terms