How to detect database version of SQL Server backups

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.

image

image

image

image

image

image

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.

  1. 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
  2. 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.

Advertisements

About Alex Feng

SQL Server DBA in Alibaba Cloud Computing
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s