Sunday, June 14, 2015

How to check the version of a SQL Server .bak file


RESTORE HEADERONLY is a useful way to get the SQL Server version of a .bak file.  It’s a way to avoid trying to restore, say, a SQL 2008 R2 backup onto a SQL 2008 server, which won't work.  I ran the command from my local SQL 2008 instance against a few different .bak files on my computer; the output fields related to version follow the commands.
RESTORE HEADERONLY FROM DISK = 'D:\dbbackup\base106.bak'
-- DatabaseVersion      SoftwareVersionMajor    SoftwareVersionMinor       SoftwareVersionBuild
-- 611                  9                           0                      4035
-- (SQL Server 2005 backup)

RESTORE HEADERONLY FROM DISK = 'D:\dbbackup\iMISMain15_15.2.5.3815.bak'
-- DatabaseVersion      SoftwareVersionMajor    SoftwareVersionMinor       SoftwareVersionBuild
-- 655                  10                          0                      2531
-- (SQL Server 2008 backup)

RESTORE HEADERONLY FROM DISK = 'D:\dbbackup\ga1520_upgrade.bak'
-- DatabaseVersion      SoftwareVersionMajor    SoftwareVersionMinor       SoftwareVersionBuild
-- 661                  10                          50                           2500
-- (SQL Server 2008 R2 backup)
-- (the header can be read, but this backup cannot be restored onto a SQL 2008 server)

RESTORE HEADERONLY FROM DISK = 'D:\dbbackup\SQl2012\SMR 999999\ABCD Bkup for ASI.bak'
-- BackupName: *** INCOMPLETE ***
-- all values NULL
-- (SQL Server 2012 backup, SQL Server 2008 or SQL Server 2008 R2 instance)
-- (the header cannot be read, and this backup cannot be restored onto a SQL 2008 server)

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.