Oftentimes, we have many different SQL Server (2000, 2005, 2008, etc.) database backups files, and it is hard to remember database version in each backup file. This could cause problems, such as restoring a SQL Server 2008 database backup on a SQL Server 2005 instance would be failed. So, it is necessary to know the database version in the backup file before restoring a database. The following shows one way get it by using RESTORE HEADREONLY.
Firstly, run the following T-SQL command against the database backup file:
RESTORE HEADERONLY FROM DISK = 'D:\Workspace\SQLServer\Backups\AdventureWorks.bak'; GO
If successfully executed, you would get a result record in below, I cut it into several screenshots due to its length.
Here are some useful columns :
- BackupType: Indicates the backup type of the backup file (1 = full, 2 = transaction log, 5 = differential)
- Compressed: 1 for compressed, 0 for non-compressed
- DatabaseName: The name of the database when taking backup
- DatabaseVersion: The version of the database from which the backup was taken
- SoftwareVersionMajor/SoftwareVersionMinor/SoftwareVersionBuild: Indicates the version info of SQL Server from which the backup was taken
- RecoveryModel: The database recovery model when the backup was taken
For a complete description of each column, please refer to RESTORE HEADREONLY in Books Online.
Secondary, using the result of RESTORE HEADERONLY command, we can get the database version of the backup via the following two method.
- Inferring the database version through DatabaseVersion column. You can refer to the following for mappings between SQL Server and number of DatabaseVersion:
- 515 <=> SQL Server 7.0
- 539 <=> SQL Server 2000
- 611/612 <=> SQL Server 2005
- 655 <=> SQL Server 2008
- 661 <=> SQL Server 2008 R2
- Inferring the database version through SoftwareVersionMajor, SoftwareVersionMinor, and SoftwareVersionBuild columns. Replace SoftwareVersionMajor.SoftwareVersionMinor.SoftwareVersionBuild with actually values, and refer to the following article to get exactly SQL Server version info from which the backup was taken. http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx.
So, in my example above, I can learn that the this backup was taken from SQL Server 2008 R2 (661) through method, and from SQL Server 2008 R2 CU7 (10.50.1777) through method 2.
Hope that helps.