How to Check Microsoft SQL Server Version: Step-by-Step Guide for Users

Knowing which version, edition, and service pack of Microsoft SQL Server you’re working with is super important when managing databases.

To find out your SQL Server version, you can run a simple query in SQL Server Management Studio (SSMS): SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY('productlevel'), SERVERPROPERTY('edition').

This little query helps us get the exact details we need about the version and the edition of SQL Server, keeping us informed about what we’re working with.

Another handy method is using the @@VERSION function. This function returns all the version details in a one-liner, providing an easy way to quickly check the SQL Server version. It’s a straightforward solution if we’re looking for a quick confirmation without diving into deep configurations.

Don’t forget about compatibility levels. We can find out the SQL Server version by checking the database compatibility level. Just right-click on a database in SSMS, select “Properties,” and go to the “Options” page to see the compatibility level. Such simple steps ensure we maintain the right settings across our databases, avoiding any version-related issues.

Identifying The SQL Server Version Using SQL Server Management Studio (SSMS)

SSMS offers a couple of straightforward ways to check the version of your SQL Server. We can either use the Object Explorer or run a Query.

Using The Object Explorer

Using SSMS, we start by connecting to the Database Engine. Open SSMS and log in to your SQL Server instance. Once connected, look for the Object Explorer on the left-hand side.

In the Object Explorer, right-click on the server name at the top of the tree. Select Properties from the context menu. In the Properties window, you’ll see details including the version number, edition, and build number.

The Product Version field gives the exact SQL Server version. This includes the major and minor version numbers, both critical for understanding your SQL Server’s features and compatibility.

Running A Query

Another way to find the SQL Server version is by running a query. Open a new query window in SSMS. Type and execute the following:

SELECT @@VERSION

The @@VERSION function returns a single string with comprehensive details. This includes the SQL Server version number, build number, and the SQL Server build date.

Alternatively, use the SERVERPROPERTY function for more granular details. Execute the following:

SELECT SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('ProductMajorVersion'), SERVERPROPERTY('ProductMinorVersion'), SERVERPROPERTY('Edition')

This query returns several pieces of information:

• ProductVersion: Complete version and build numbers.
• ProductMajorVersion: Main version of SQL Server.
• ProductMinorVersion: Sub-version of SQL Server.
• Edition: Edition of SQL Server (e.g., Standard, Enterprise).

Using SSMS, whether through the Object Explorer or running queries, allows us to get detailed and easy-to-access SQL Server version information. This is helpful for managing installations, troubleshooting issues, and ensuring compatibility.

Checking The SQL Server Version Using Command Prompt

We can check the SQL Server version using the Command Prompt in two different ways: by using the sqlcmd utility and by checking the Windows registry. Below, we’ll cover each method step-by-step.

Using Sqlcmd Utility

Using the sqlcmd utility is a straightforward approach. It allows us to interact directly with the SQL Server using T-SQL commands. Here’s how we do it:

1. Open Command Prompt: Press Win + R, type cmd, and hit Enter.
2. Run Sqlcmd: Type the following command and press Enter:

   sqlcmd -S localhost -Q "SELECT @@VERSION"
   

   Replace localhost if your SQL Server is hosted on another machine.

This command will display the SQL Server version currently installed. The output will look something like this:

Using sqlcmd is quick and effective, whether you are an admin or a developer.

Using The Windows Registry

Another way to find the SQL Server version is by checking entries in the Windows Registry. Here’s how:

1. Open Registry Editor: Press Win + R, type regedit, and hit Enter. Be cautious with changes in the registry.
2. Navigate to the SQL Server registry: Go to the following path:

   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server
   

3. Find version information: Look under the instance name for a key labeled CurrentVersion. This will tell you the version of SQL Server installed.

The registry method gives us detailed insights, especially useful when multiple SQL Server instances are installed.

By following these steps, we can effortlessly check the version of SQL Server using the Command Prompt, ensuring we’re always informed about our server environment.

Determining SQL Server Version Through PowerShell

We’ll explore two primary methods to determine the SQL Server version using PowerShell: leveraging built-in cmdlets and querying the Windows Registry.

Using Get-Service Cmdlet

A quick way to find the SQL Server version is by leveraging the Get-Service cmdlet in PowerShell. This cmdlet helps us retrieve the status of Windows services. We can target SQL Server services by looking for those named “MSSQL$”.

Here’s an example command:

Get-Service | Where-Object { $_.Name -match "^MSSQL$.*" }

This command lists all SQL Server instances running on your machine.

To get detailed info, we combine it with Invoke-Sqlcmd:

Invoke-Sqlcmd -Query "SELECT @@VERSION"

Combining both allows us to identify the version details.

Using Get-Service makes it easy to see which SQL Server instances are active. It’s a straightforward solution for quick checks.

Querying The Windows Registry

For more detailed version information, querying the Windows Registry is effective. The SQL Server version details are stored here, and PowerShell can access these entries.

We begin by navigating to the registry path:

Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"

This command lists all installed SQL Server instances.

Next, we retrieve version info for a specific instance:

Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL$InstanceName\MSSQLServer\CurrentVersion"

Replace InstanceName with the actual name of your instance.

This method is ideal for in-depth checks. It provides a reliable way to gather specific version details directly from the system’s registry.

Both methods are useful depending on your needs. Using cmdlets is quick and effective for active instances, while registry queries offer detailed insights.