SQL SERVER – Identify Version of SQL Server from Backup File

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:

FROM DISK = N'D:\data\SQLAuthority.bak'

When you run above script, it gives quite a few important details.

SQL SERVER - Identify Version of SQL Server from Backup File databseversion

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 VersionInternal Database VersionDatabase Compatibility Level
SQL Server 2017869140
SQL Server 2016852130
SQL Server 2014782120
SQL Server 2012706110
SQL Server 2008 R2660/661100
SQL Server 2008655100

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)

SQL Backup, SQL DMV, SQL Scripts, SQL Server, SQL Server Internal
Previous Post
SQL SERVER – Creating Temporary and Global Temporary Stored Procedures
Next Post
SQL SERVER – Identifying Deprecated SQL Server Features with Extended Events

Related Posts

11 Comments. Leave new

Leave a Reply