If you work with lots of different SQL Server backup files, is there a way to find out what version of SQL Server they came from without having to restore it? Short answer, Yes!   If you work with many backup files from different versions of SQL Server, you might find this T-SQL command useful.  This T-SQL command also shows other information about the backup file that might be helpful.  In this blog, let's take a look at this T-SQL statement and how it works.

The T-SQL command that I'm talking about is RESTORE HEADER and here is how you would use it. Log into SQL Server Management Studio (SSMS) and open a New Query window.  You will also need to know the physical location of the backup file. 

In my example, the backup file is located under:  c:\Program Files\Microsoft SQLServer\MSSQL13.X3DataSQL2k16\MSSQL\Backup and the backup file name is x3erpv12.bak.

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 the output returned by the RESTORE HEADERONLY statement.  If you notice there is a DatabaseVersion column that contains a value of 852. (Note that you may need to scroll to the right to be able to see the DatabaseVersion column).  It would be nice if instead of showing 852 the value would read SQL 2016, but it doesn't.  So, over the years have collected the different values for each DatabaseVersion. It looks like this:

539(SQL 2000), 611(SQL 2005),  655(SQL 2008), 661(SQL 2008R2), 706(SQL 2012), 782(SQL 2014) , 852(SQL 2016), 869 (SQL 2017)

So in our example 852 means the backup file came from a SQL Server 2016 version. 

That's how you find out from which version of SQL Server a given backup file came.  But I also wanted to point out a few other bits of information about the RESTORE HEADERONLY command.   In the screenshot, I moved the columns around and placed them near each other intentionally, your results will not be in the same order.  The other columns include information such as the RecoveryModel (Full / Simple / Bulk Logged) or Simple), UserName(user name that performed the backup on the database), Collation(should be Latin1_General_BIN) , BackupTypeDescription(Database /  Transaction Log / File / Differential file / Partial / Differential Partial), and the ServerName (where the database resided at the time when it was backed up).  These are just a few of the bits of information you can obtain from the backup without having to restore the backup first!

Over the years, this has saved me considerable time trying to figure out from which SQL Server certain backup files came. I hope you give this command a try.