Finding details of a SQL backup file with out actually restoring it...

We may have to find details of a SQL backup file with out the luxury of restoring it. Some of the common requirements (but not limited to only these) are:

  1. What type of backup file is it?
  2. When the backup started and finished?
  3. Is the file good or corrupted?
  4. What is the database which is backed up in this file? [In case you did not follow naming convention]
  5. Is the backup compressed?
  6. Who took this backup?

We can have all these answers using below code:

RESTORE HEADERONLY
FROM DISK = N'File Name'

Output of this command will contain all the required information from the backup file from the backup’s files metadata. Result from this command (only frequently required information) can be read from below reference:

Column name
Description for SQL Server backup sets
BackupName
Backup set name.
BackupDescription
Backup set description.
BackupType
Backup type:
1 = Database
2 = Transaction log
4 = File
5 = Differential database
6 = Differential file
7 = Partial
8 = Differential partial
ExpirationDate
Expiration date for the backup set.
Compressed
Whether the backup set is compressed using software-based compression:
0 = No
1 = Yes
UserName
User name that performed the backup operation.
ServerName
Name of the server that wrote the backup set.
DatabaseName
Name of the database that was backed up.
DatabaseVersion
Version of the database from which the backup was created.
BackupSize
Size of the backup, in bytes.
DatabaseCreationDate
Date and time the database was created.
BackupStartDate
Date and time that the backup operation began.
BackupFinishDate
Date and time that the backup operation finished.
CompatibilityLevel
Compatibility level setting of the database from which the backup was created.
IsSnapshot
1 = Snapshot backup.
HasBackupChecksums
1 = Backup contains backup checksums.
IsReadOnly
1 = Database was read-only when backed up.
IsSingleUser
1 = Database was single-user when backed up.
IsDamaged
1 = Database was damaged when backed up, but the backup operation was requested to continue despite errors.
HasBackupChecksums
1 = Backup contains backup checksums.
IsForceOffline
1 = Backup taken with NORECOVERY; the database was taken offline by backup.
IsCopyOnly
1 = A copy-only backup.
A copy-only backup does not impact the overall backup and restore procedures for the database
RecoveryModel
Recovery model for the Database, one of:
FULL
BULK-LOGGED
SIMPLE


If you need complete description of possible output or need more information, please visit to http://msdn.microsoft.com/en-us/library/ms178536.aspx

Comments