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.

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'

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

The RESTORE command returns a large set of columns (+50) with greater details of the backup file that could be very handy for other kind of scenarios as well. But for now, let’s focus on the DatabaseVersion column which according to Microsoft Docs its just an INT value that represents the database version:

Taking the values from the two RESTORE examples above, the 706 and 782 values doesn’t say too much. After doing a research about the DatabaseVersion column, I didn’t find an official source from Microsoft to explain what these values are. To my surprise I found the information I was looking for from a source that I particularly like and use very much to check SQL Server build numbers, patches and CUs; a website called SQL Server builds

If we look at the values of the Internal Database Version column from the image above, we can easily match the backup file with the SQL Server version where the database was created. In this case the backups I used for my examples were created on SQL Server 2012 (706) and 2014 (782) and this is correct indeed because I used the Adventure Works sample database from Microsoft GitHub repository.

Thanks for reading!

The post Determine database version from a bak file appeared first on DBA MASTERY.