How to Determine SQL Server Version?

In this fast growing technological world, sometimes we forget to take small things ahead with us. This is exactly what happens when users wants to know how to determine SQL Server version of an MDF file in the database. Well, there are a few ways to do so but it differs in various situations.

To get the right solution, we must understand the reasons for it. The only users can select the right method. Moving ahead, to know these reasons, we have mentioned some common causes.

Why Users Need to Determine SQL MDF Version

Well, every user might have a different reason to check the SQL Server MDF file version. However, we are going to mention the most common ones here that we got in our mails. It will help users understand their root cause & guide to select the right solution.

• Security & Patch Updates: Users often want to know the SQL Server MDF file as they want to be updated with SQL security & patch updates. This is because SQL provides security updates to a limited older versions & users need to know if they fall in that categoryor not.
• Compatibility with Tools: It’s not a big deal for users to attach or plug other tools with their SQL Server. In fact, SQL Server database is itself  allows the compatibility to various applications & tools. However, for this, users must be aware of the SQL Server version or MDF file version to continue. 
• Error Fixing: In several issues like any errors in the database, corurption of the files etc, users need to to fix such issues for which they need to determine SQL Server version of the MDF file.
• Features Exploration: Different SQL versions comes with different features. In this case, whenever new features get launched for SQL Server, users must check if their SQL database is eligible for getting the benefits from these features.

Apart form this, there can be various other causes that varies from person to person. However, all of these causes lead to one query which leads to determine SQL database version.

How to Determine SQL Server Version of An MDF file

How to know the version of SQL Server MDF file when users do not have an active SQL environment like SQL server, SSMS, configuration manager. This is the question users must be concerned about.

So, users need to Free Download SQL MDF ViewerTool from SysTools. This solves the entire problem pretty easily without an issue. Download this software & then follow the five simple steps as mentione below:

Step-1. Launch the Software in system to start checking SQL MDF version.

Step-2. Click on Open button to Add the MDF file & corresponding NDF file.

Step-3. While scanning data for corruption It Shows the Database Version.

Step-4. After scanning, users can Preview Data objects without environment.

This is the free & easist way for users to determine SQL DB version without having an SQL environment. Even after having an SQL environment, users can rely in this easy & feature loaded solution.

Command to Check SQL Server Version

In case users want to determine the version of MDF file in SQL Server using T-SQL CMD, they must have SQL environment. The command for this is quite simple but there are two issues mentioned below:

• It shows the Version of SQL Server but not of an MDF file.
• SQL Server Management Studio must be installed on the system.

The command to check SQL Server version is:

SELECT @@VERSION;

Output Example:

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Web Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Apart from this, there are a few more ways without command but using the SQL environment mentioned below.

Also Read: How to Merge MDF and NDF Files in SQL Server Database?

Ways to Determine SQL Server Version with SQL Environment

We have a few ways to determine SQL DB version using the SQL environment which includes the SSMS non-CMD method, EXE file method, and SERVERPROPERTY function method.

1. SSMS Method

• Open the SSMS and then connect to the instance.
• Right Click on the SQL Server Instance to expand options.
• Hit the Properties option to continue & Go to the General tab.
• Now, Check the Product Version or Version category to find out.
  
  

Here is the list that determine SQL database version associated with different version numbers:

• 8.0 for SQL Server 2000
• 9.0 for SQL Server 2005
• 10.0 for SQL Server 2008
• 10.5 for SQL Server 2008 R2
• 11.0 for SQL Server 2012
• 12.0 for SQL Server 2014
• 13.0 for SQL Server 2016
• 14.0 for SQL Server 2017
• 15.0 for SQL Server 2019
• 16.0 for SQL Server 2022

2. EXE File Method

In case the SQL Server isn’t running & users need to get the version, they can opt for this method. All the steps here are mentioned below:

1. Open the Windows Explorer.
2. Navigate to the SQL Server installed folder.
3. For example of SQL 2008 version:
   

   "C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn"

4. Search for sqlservr.exe followed by a Right Click and Select Properties option.

In the image, we can see that the product version is showing 10.0.2573.0. As mentioned above, 10 Represent SQL Server 2008 Server.

3. SERVERPROPERTY Function Method

Last but not least, we have a method to determine SQL DB version using the SERVERPROPERTY function.

Here, users simply need to execute a command of SQL Server function which results with the output of the SQL Server version. This check SQL server version CMD looks like this:

SELECT
  CASE 
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'     
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017' 
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '15%' THEN 'SQL2019' 
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '16%' THEN 'SQL2022' 
     ELSE 'unknown'
  END AS MajorVersion,
  SERVERPROPERTY('ProductLevel') AS ProductLevel,
  SERVERPROPERTY('Edition') AS Edition,
  SERVERPROPERTY('ProductVersion') AS ProductVersion

The Output for the same from the database is like this:

The Final Say

Undoubtedly the experts & SQL MVPs prefer the automated solution as it costs nothing & is hassle-free. However, other options are considerable too if users are proficient in SQL technicalities & do have an active SQL environment.

Now, there will be no more queries for learning how to determine SQL Server version of an MDF file within the database. Let it be CMD method or the easiest one, all of them are mentioned in such a way, that users can get the solution.