SQL Server – What version of SQL did backup file originate?

Background

Unfortunately, dealing with a hard-drive crash and now trying to review and possibly restore the backup files.

Question

So now the question is what version of SQL Server was installed on the crashed server.

 

Investing using Backup file

Transact SQL – Restore HeaderOnly

SQL:


restore headeronly
from  disk ='e:\tmp\DBLAB_backup_2015_08_04_001526_4046147.bak'

 

Output:

SQLServerFromBackupfile

 

Explanation:

  1. In our case
    • Database Version is 661 –> SQL Server 2008
    • SoftwareVersionMajor and SoftwareVersionMinor are 10 and 50; and that transpose to MS SQL Server v2008/R2

 

Documentation:

https://msdn.microsoft.com/en-us/library/ms178536.aspx

 

Column Name Description Sample
Backup Name Backup up set name  DBLAB_backup_2015_08_04_001526_4046147
Backup Type Backup type ( 1 = Database, 2 = Transaction log, 4 = File, 5 = Differential database, 6 = Differential file, 7 = Partial, 8 = Differential partial )  1
Compressed Whether the backup set is compressed using software-based compression ( 0 = No, 1 = Yes )  1
Username  User name that performed the backup operation. LABDomain\MSSQLService
Computer  Name of the server that wrote the backup set.  LABDB
Database Name  Name of the database that was backed up.  LABDB
DatabaseVersion Version of the database from which the backup was created.  661 ( SQL Server 2008 )
DatabaseCreationDate Date and time the database was created.  2013-10-17 23:02:52.000
BackupSize Size of the backup, in bytes.  14864384
 SoftwareVersionMajor  Major version number of the server that created the backup set. 10
 SoftwareVersionMinor  Minor version number of the server that created the backup set.  50
 MachineName  Name of the computer that performed the backup operation.  DBLAB
 HasBackupChecksums  1 = Backup contains backup checksums.  0
 IsDamaged  1 = Database was damaged when backed up, but the backup operation was requested to continue despite errors.  0
 CompressedBackupSize  Byte count of the backup set. For uncompressed backups, this value is the same as BackupSize.To calculate the compression ratio, use CompressedBackupSize and BackupSize.  4769366
 containment  Indicates the containment status of the database ( 0 = database containment is off, 1 = database is in partial containment )  0

 

 

Summary

In retrospect, the server crash is causing us to review things that we had an operational blindside to.

As an example, are we using compression, checksum, etc?

Leave a Reply

Please log in using one of these methods to post your comment:

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