Search

Thursday, February 16, 2012

Finding the details of Backup File without restoring it

Sometime we may have to find details of a SQL backup file without restoring it. Some of the common requirements are:
  1. When the backup Started and Finished.
  2. Is the backup file is good.
  3. Who had taken this backup.
  4.  What is the type of backup.
  5. Is backup Compressed
  6. Database Creation Date
  7. SQL Server Version
  8. From which system backup was taken
  9. Database Collation
  10. Recovery Model
We can get all the information wsing below query:

RESTORE HEADERONLY FROM DISK = N'C:\Temp\Data.bak'

Output: 

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

No comments:

Post a Comment