How to know SQL Server Version using backup file?

Backup and restore these are common tasks while working with the database. But before restoring the database it is necessary that we should know about some information about backup file because it may be possible that we have created backup file in upper version of SQL Server and we are trying to restore it in lower version of SQL Server. In this is case database can not be restored.

So before facing this problem it is better to know about backup file means in which version it was created.

To get this information we can simply use RESTORE command with HEADERONLY. Check the bellow command -


RESTORE HEADERONLY 
         FROM DISK = N'd:\filename.bak' 

By passing the path of backup file you can run this command, you will get result like this-

 

By seeing SoftwareVersionMajor and SoftwareVersionMinor you can identify the version of the software.

To know about each column help follow this link-

http://technet.microsoft.com/en-us/library/ms178536.aspx

Here you will get description for each column.