What extra information can we get from a backup file? | SansSQL

Monday, December 10, 2012

What extra information can we get from a backup file?

Consider a situation where you got a alert saying the "disk is almost full" and you are browsing the disk to find out what can be removed. During this process you come across a orphaned backup file with just some name specified to that file and you do not know from which database this backup file is generated, when was it taken, is it a full or differential or log backup, etc, etc...
We all know that if we have the proper backup file(s), we can recover a database fully, but without the proper information we cannot recover. In this case, we do not know the information about the backup file that was found.

So how to proceed?
One way is to restore the backup and check,
  1. But restoring of a backup taken in higher version is not allowed in lower version. For example, a backup taken in SQL Server 2008 cannot be restored in  SQL Server 2005.
  2. Without a full backup we cannot start a new database restore and in this case we do not know what is the backup type.
So what next?
Another way is to check without restoring. For this, SQL Server provides additional commands that can be used with the backup file using which we can get extra information from the backup file.
These commands are
  1. RESTORE HEADERONLY
  2. RESTORE FILELISTONLY
  3. RESTORE LABELONLY
RESTORE HEADERONLY - Returns the backup header information of the specified backup.
And the header information includes information about
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
Position Position of the backup set in the volume (for use with the FILE = option).
DeviceType Number corresponding to the device used for the backup operation.
Disk:
2 = Logical
102 = Physical
Tape:
5 = Logical
105 = Physical
Virtual Device:
7 = Logical
107 = Physical
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.
DatabaseCreationDate Date and time the database was created.
BackupSize Size of the backup, in bytes.
FirstLSN Log sequence number of the first log record in the backup set.
LastLSN Log sequence number of the next log record after the backup set.
CheckpointLSN Log sequence number of the most recent checkpoint at the time the backup was created.
DatabaseBackupLSN Log sequence number of the most recent full database backup.
BackupStartDate Date and time that the backup operation began.
BackupFinishDate Date and time that the backup operation finished.
SortOrder Server sort order. This column is valid for database backups only. Provided for backward compatibility.
CodePage Server code page or character set used by the server.
UnicodeLocaleId Server Unicode locale ID configuration option used for Unicode character data sorting. Provided for backward compatibility.
UnicodeComparisonStyle Server Unicode comparison style configuration option, which provides additional control over the sorting of Unicode data. Provided for backward compatibility.
CompatibilityLevel Compatibility level setting of the database from which the backup was created.
SoftwareVendorId Software vendor identification number. For SQL Server, this number is 4608 (or hexadecimal 0x1200).
SoftwareVersionMajor Major version number of the server that created the backup set.
SoftwareVersionMinor Minor version number of the server that created the backup set.
SoftwareVersionBuild Build number of the server that created the backup set.
MachineName Name of the computer that performed the backup operation.
Flags  Individual flags bit meanings if set to 1:
1 = Log backup contains bulk-logged operations.
2 = Snapshot backup.
4 = Database was read-only when backed up.
8 = Database was in single-user mode when backed up.
16 = Backup contains backup checksums.
32 = Database was damaged when backed up, but the backup operation was requested to continue despite errors.
64 = Tail log backup.
128 = Tail log backup with incomplete metadata.
256 = Tail log backup with NORECOVERY.
BindingID
Binding ID for the database
RecoveryForkID ID for the ending recovery fork. This column corresponds to last_recovery_fork_guid in the backupset table.
Collation Collation used by the database.
FamilyGUID ID of the original database when created. This value stays the same when the database is restored.
HasBulkLoggedData 1 = Log backup containing bulk-logged operations.
IsSnapshot 1 = Snapshot backup.
IsReadOnly 1 = Database was read-only when backed up.
IsSingleUser 1 = Database was single-user when backed up.
HasBackupChecksums 1 = Backup contains backup checksums.
IsDamaged 1 = Database was damaged when backed up, but the backup operation was requested to continue despite errors.
BeginsLogChain 1 = This is the first in a continuous chain of log backups. A log chain begins with the first log backup taken after the database is created or when it is switched from the Simple to the Full or Bulk-Logged Recovery Model.
HasIncompleteMetaData 1 = A tail-log backup with incomplete meta-data.
IsForceOffline 1 = Backup taken with NORECOVERY; the database was taken offline by backup.
IsCopyOnly 1 = A copy-only backup.
FirstRecoveryForkID ID for the starting recovery fork. This column corresponds to first_recovery_fork_guid in the backupset table.
ForkPointLSN If FirstRecoveryForkID is not equal to RecoveryForkID, this is the log sequence number of the fork point. Otherwise, this value is NULL.
RecoveryModel Recovery model for the Database, one of:
FULL
BULK-LOGGED
SIMPLE
DifferentialBaseLSN For a single-based differential backup, the value equals the FirstLSN of the differential base; changes with LSNs greater than or equal to DifferentialBaseLSN are included in the differential. For non-differential backup types, the value is always NULL.
DifferentialBaseGUID For a single-based differential backup, the value is the unique identifier of the differential base.
BackupTypeDescriptionBackup type as string, one of:
DATABASE
TRANSACTION LOG
FILE OR FILEGROUP
DATABASE DIFFERENTIAL
FILE DIFFERENTIAL PARTIAL
PARTIAL DIFFERENTIAL
BackupSetGUID Unique identification number of the backup set, by which it is identified on the media.
CompressedBackupSize Byte count of the backup set. For uncompressed backups, this value is the same as BackupSize.

Example:

RESTORE FILELISTONLY - Returns the information about list of the database and log files contained in the backup. 
Column name
Description
LogicalName Logical name of the file.
PhysicalName Physical or operating-system name of the file.
Type The type of file, one of:
L = Microsoft SQL Server log file
D = SQL Server data file
F = Full Text Catalog 
FileGroupName Name of the filegroup that contains the file.
Size Current size in bytes.
MaxSize Maximum allowed size in bytes.
FileID File identifier, unique within the database.
CreateLSN Log sequence number at which the file was created.
DropLSN The log sequence number at which the file was dropped. If the file has not been dropped, this value is NULL.
UniqueID Globally unique identifier of the file.
ReadOnlyLSN Log sequence number at which the filegroup containing the file changed from read-write to read-only (the most recent change).
ReadWriteLSN Log sequence number at which the filegroup containing the file changed from read-only to read-write (the most recent change).
BackupSizeInBytes Size of the backup for this file in bytes.
SourceBlockSize Block size of the physical device containing the file in bytes (not the backup device).
FileGroupID ID of the filegroup.
LogGroupGUID NULL.
DifferentialBaseLSN For differential backups, changes with log sequence numbers greater than or equal to DifferentialBaseLSN are included in the differential.
DifferentialBaseGUID For differential backups, the unique identifier of the differential base.
For other backup types, the value is NULL.
IsReadOnly 1 = The file is read-only.
IsPresent 1 = The file is present in the backup.

Example:
RESTORE LABELONLY - Returns the information about backup media of the given backup. 
Column name
Description
MediaName Name of the media.
MediaSetId Unique identification number of the media set.
FamilyCount Number of media families in the media set.
FamilySequenceNumber Sequence number of this family.
MediaFamilyId Unique identification number for the media family.
MediaSequenceNumber Sequence number of this media in the media family.
MediaLabelPresent Whether the media description contains:
1 = Microsoft Tape Format media label
0 = Media description
MediaDescription Media description, in free-form text, or the Tape Format media label.
SoftwareName Name of the backup software that wrote the label.
SoftwareVendorId Unique vendor identification number of the software vendor that wrote the backup.
MediaDate Date and time the label was written.
Mirror_Count Number of mirrors in the set (1-4).
IsCompressed
Whether the backup is compressed:
0 = not compressed
1 =compressed

Example:



Ads