Sometime we may have to find details of a SQL backup file without restoring it. Some of the common requirements are:
- When the backup Started and Finished.
- Is the backup file is good.
- Who had taken this backup.
- What is the type of backup.
- Is backup Compressed
- Database Creation Date
- SQL Server Version
- From which system backup was taken
- Database Collation
- 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:
BULK-LOGGED SIMPLE |
No comments:
Post a Comment