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:
- What type of backup file is it?
- When the backup started and finished?
- Is the file good or corrupted?
- What is the database which is backed up in this file? [In case you did not follow naming convention]
- Is the backup compressed?
- 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: 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