Determine database version from a bak file

Created by Cheng Kah Poh, Modified on Mon, 04 Jul 2022 at 03:17 PM by Cheng Kah Poh

Scenario:
Have you ever imagine you can determine the version of SQL Server from a bak or a MDF file? I have been in situations where I received a bak or MDF and LDF files from a vendor to restore \ create a database for the implementation of a new application, most of the times I receive complete detail of the supported versions of SQL Server by the product but what happens if I don’t get that information?

SQL Server offers a way to get the actual version where the files were created, let’s review the process to using a database restore.


Solution:

RESTORE HEADERONLY

This command does not restores the backup as the name suggest, it just returns metadata (information) about the backup file you are examining.

Syntax:

 RESTORE HEADERONLY FROM DISK = 'physical_backup_device_name'

After you have created a New Query window, you can change the database context to any database, I selected master.  The syntax I will use is as follows:  RESTORE HEADERONLY FROM DISK ='c:\Program Files\Microsoft SQL Server\MSSQL13.X3DataSQL2k16\MSSQL\Backup\x3erpv12.bak'.  You will need to substitute the path and filename accordingly. 

Here is an example of the RESTORE command and its output with two different files:


Here are the quick list lf all the SQL Server versions and their compatibility levels from SQL Server 2008 to the latest version of SQL Server. 

 


Thanks.

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select atleast one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article