How to find SQL Server Version
A version is a specific form of something that varies from other forms of the same version. This article will explain the complete overview to find the SQL Server version running on your system.
We can get the SQL Server version in multiple ways, and some of them are listed below:
Let us discuss each in detail.
Using a Query
It is the easiest way to see the SQL Server version installed on our system. We can use the @@VERSION function to get all of the SQL Server instance's version details. This function returns a one-line string output containing all the relevant information about the SQL Server. It will display the following information in the output:
Now, we will execute the below query to see the current version of the SQL Server:
Here is the output:
The below table will explain this information in a meaningful form:
Using Console/Command Prompt
Command prompt simulates the input field in a text-based user interface screen with the Windows GUI. It can be used to run commands and carry out advanced administrative tasks.
We can find the SQL Server version using console using the below steps:
Here we need to change the name in place of servername and instancename.
The following image shows the SQL Server version on the cmd screen. We can repeat this process in other instances.
Using SQL Server Management Studio
SSMS is a valuable tool for managing the SQL Server installation. When we open SSMS for any SQL Server instance, we can see the product version appears under the Object Explorer tab between the parentheses. We can identify the SQL Server product version by this set of numbers. See the below image where the product version is shown on the red rectangular box:
Let us convert this meaningless set of numbers into a meaningful product version. The product version is specified as 15.0.2000, which we convert into more meaningful and useful information. In this set of numbers, the first two digits (15) indicate the numeric definition of the SQL Server product name.
We can find the SQL Server version against the product name with the help of the following table quickly:
However, the first two digits provide the product name, and we can get more information about the SQL Server instances. The remaining numbers (0.2000) determine the service pack and cumulative upgrade levels details. We can get more details regarding the remaining numbers; we need to visit the BuildNumbers website:
PowerShell is a powerful cross-platform tool for automation tasks made up of a command-line shell, a scripting language, and simplifying configuration. It can automate almost every Windows, Linux, and macOS activity, including Active Directory and Exchange. Microsoft has two modules of SQL Server PowerShell, but both are not installed with SSMS starting with version 17.0:
We can also find the installed SQL server version using PowerShell by entering the following command:
Using Log Files
We can also find the SQL Server version in the SS error logs file. This file can be used to troubleshoot SQL Server issues because it records user-defined events as well as specific device events. It will also help us to get SQL Server version details. The first few lines of this file provide detailed information regarding the SQL Server's version.
The error log is located in "Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG" by default. However, we can also have an option SERVERPROPERTY function to get the location of the error log file. We can find the position of the error log file using the following query:
It will display the below output:
Now, we can open the error log file in the given location. Here we can see the detailed information about SQL Server build.
We can also read the error log by using the stored procedure sp_readerrorlog. The first line of its output shows the SQL Server instance details:
Using SQL Server Properties
We can also use the SQL Server properties method to get the SQL server version information. It is one of the easiest methods to get the SQL server version details.
In this method, we need to navigate to the C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn. Now, search the sqlservr executable file, right-click on it to open the context menu, and select the Properties option. We will get the wizard where we will click the Details tab to see the product version and product name.
This article will explain various methods to check the SQL Server version and learned how to convert them into meaningful information.