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.
This command does not restores the backup as the name suggest, it just returns metadata (information) about the backup file you are examining.
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!
Carlos Robles is a Solutions Architect at AWS, a former Microsoft Data Platform MVP, a Friend of Redgate, but more than anything a technology lover. He has worked in the database management field on multiple platforms for over ten years in various industries.
He has diverse experience as a Consultant, DBA and DBA Manager. He is currently working as a Solution Architect, helping customers to solve software/infrastructure problems in their on-premise or cloud environments.
Speaker, author, blogger, mentor, Guatemala SQL User group leader. If you don’t find him chatting with friends about geek stuff, he will be enjoying life with his family.