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