One question I always ask all of my customers before I start Comprehensive Database Performance Health Check is that if they have taken backup of their server. The answer to this question honestly does not matter to me during consultancy I never change anything which brings to the situation where they need to restore the database. However, when I asked this question to my client, they said they have an issue to identify a version of SQL Server from the backup file.
Let me elaborate on this subject. My client had multiple versions of SQL Server. They had SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 and SQL Server 2016 (they have yet not moved to SQL Server 2017). Each of this version has a database named “DevDepartment”. Now before the consultancy started they took backup of this database in a single folder for all the versions. Now their backup folder had 4 backups with a similar name – “Backup-DevDepartment+[DynamicTimeStamp]”. They were indeed very much confused with all the files and were not sure which file belongs to which database server.
Well, the answer is very simple. We can figure out details about the server, login and database version by just checking the header details of the backup files.
Here is the script which can give you important server details:
RESTORE HEADERONLY FROM DISK = N'D:\data\SQLAuthority.bak'
When you run above script, it gives quite a few important details.
Pay attention to columns – ServerName, UserName, DatabaseName, DatabaseVersion. We can figure out what was the original server name and database version from those columns.
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.
| SQL Server Version | Internal Database Version | Database Compatibility Level |
|---|---|---|
| SQL Server 2017 | 869 | 140 |
| SQL Server 2016 | 852 | 130 |
| SQL Server 2014 | 782 | 120 |
| SQL Server 2012 | 706 | 110 |
| SQL Server 2008 R2 | 660/661 | 100 |
| SQL Server 2008 | 655 | 100 |
You can compare the column DatabaseVersion from the resultset with Internal Database version and can determine the version of the database. Well, I hope this was a very simple tutorial where we can figure out the version of SQL Server from the backup file.
Reference: Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
Thank u
My pleasure.
Thank you sir
Excelente… Gracias…
I am having an issue as i have 2012 version and i assuming the backup file is from either 2014 or 2017 version.
When i run the given script in 2012 it is giving error
“The media family on device ‘C:\test\DB.bak’ is incorrectly formed. SQL Server cannot process this media family.”
Is there other way for me to find the version of SQL from the back up file ?
Thank you
This was awesome! Thank you so much! I have customers that use anywhere from SQL 2008 to SQL 2017. When getting a copy of their database, this allows me to restore it to their version for any troubleshooting I may have to do specific to the SQL Version. This is better than having to restore it to SQL 2016/2017 to find out the version, and then re-restoring it to the appropriate SQL Version!!
Thanks – sent it to colleagues!
SQL Server Version Internal Database Version Database Compatibility Level
SQL Server 2019 904 150
How would you recommend getting this into a queryable format? I tried declaring a temp table using the schema in https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-headeronly-transact-sql?view=sql-server-ver15 , but it still returns “Column name or number of supplied values does not match table definition.”.
Specifically, I’m trying to script version comparison of the DatabaseVersion of the backup, compared to the version of the SQL server’s Master DB. If the backup version is higher than the Master version, then I’ll report back to the user they need to use another higher version of SQL server. However, I cannot extract the Database Version via script.
Thank you for all the solutions you post.