Search This Blog

What is in your SQL Server backup files?


 Problem
Sometimes you come across SQL Server backup files stored on your file system and it is hard to determine what is in the file. From the filename you may be able to decipher that it is a full backup, differential backup or transaction log backup, but how do you really tell what is in the file.  Luckily SQL Server offers a few additional commands that you can use with your database backup files to determine the contents of the backup files.  These options include HEADERONLY, FILELISTONLY and LABELONLY.
SolutionAlong with the normal backup and restore functionality of the BACKUP and RESTORE commands there are other RESTORE options that allow you to determine what is stored in the backup file.  These commands are helpful if you write multiple backups to the same physical file or maybe you have database backups from another system and are not exactly sure what is stored within the file.  The commands that you can use to see the contents of the backup file are as follows:
Following are sample outputs from each of the commands.  These first three outputs show you the complete output from running the commands against a backup file containing one full backup of the AdventureWorks database.  As you can see there is a lot more information in the files that may or may not be useful to you.

RESTORE HEADERONLY FROM DISK='C:\Backup\Adv_Full.bak'
BackupNameAdventureWorks-Full Database Backup
BackupDescriptionNULL
BackupType1
ExpirationDateNULL
Compressed0
Position1
DeviceType2
UserNameEDGENB2\Sysadmin
ServerNameEDGENB2\TEST1
DatabaseNameAdventureWorks
DatabaseVersion611
DatabaseCreationDate38985.72449
BackupSize173091840
FirstLSN41000000054400000
LastLSN41000000056800000
CheckpointLSN41000000054400000
DatabaseBackupLSN41000000041600000
BackupStartDate1/3/2007 8:15:41 PM
BackupFinishDate1/3/2007 8:15:41 PM
SortOrder52
CodePage0
UnicodeLocaleId1033
UnicodeComparisonStyle196609
CompatibilityLevel90
SoftwareVendorId4608
SoftwareVersionMajor9
SoftwareVersionMinor0
SoftwareVersionBuild1399
MachineNameEDGENB2
Flags512
BindingID5956B629-86DF-4000-BAC0-52194A773D3B
RecoveryForkIDB935AAC8-BB1A-4C10-AD0B-014DFEF2FC72
CollationSQL_Latin1_General_CP1_CI_AS
FamilyGUIDB935AAC8-BB1A-4C10-AD0B-014DFEF2FC72
HasBulkLoggedData0
IsSnapshot0
IsReadOnly0
IsSingleUser0
HasBackupChecksums0
IsDamaged0
BeginsLogChain0
HasIncompleteMetaData0
IsForceOffline0
IsCopyOnly0
FirstRecoveryForkIDB935AAC8-BB1A-4C10-AD0B-014DFEF2FC72
ForkPointLSNNULL
RecoveryModelFULL
DifferentialBaseLSNNULL
DifferentialBaseGUIDNULL
BackupTypeDescriptionDatabase
BackupSetGUID1389292F-F593-425D-BD36-325FCEA0E02A

RESTORE FILELISTONLY FROM DISK='C:\Backup\Adv_Full.bak'
LogicalNameAdventureWorks_DataAdventureWorks_Log
PhysicalNameC:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\ AdventureWorks_Data.mdfC:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\ AdventureWorks_Log.ldf
TypeDL
FileGroupNamePRIMARYNULL
Size1886781442097152
MaxSize351843720806402199023255552
FileId12
CreateLSN00
DropLSN00
UniqueId94EDC99D-D0E0-4146-95DA-1756D6C92348EB9DB2B3-BE70-4F76-8345-7FF07FB705C7
ReadOnlyLSN00
ReadWriteLSN00
BackupSizeInBytes1721630720
SourceBlockSize512512
FileGroupId10
LogGroupGUIDNULLNULL
DifferentialBaseLSN410000000416000000
DifferentialBaseGUID6493F201-EBBA-47DD-BBDA-83A2772A8DA300000000-0000-0000-0000-000000000000
IsReadOnly00
IsPresent11

RESTORE LABELONLY FROM DISK='C:\Backup\Adv_Full.bak'
MediaNameNULL
MediaSetId23979995-927B-4FEB-9B5E-8CF18356AB39
FamilyCount1
FamilySequenceNumber1
MediaFamilyId86C7DF2E-0000-0000-0000-000000000000
MediaSequenceNumber1
MediaLabelPresent0
MediaDescriptionNULL
SoftwareNameMicrosoft SQL Server
SoftwareVendorId4608
MediaDate1/3/07 8:15 PM
MirrorCount1

If we have a backup file that contains multiple backups, using the HEADERONLY option shows us the information for each of the backups.  Following is a condensed view of the RESTORE HEADERONLY output.  As you can see there are three backups in this file; one full backup and two transaction log backups.  This information can be determined by the BackupType.
BackupNameAdventureWorks-Full Database BackupAdventureWorks-Transaction Log BackupAdventureWorks-Transaction Log Backup
BackupDescriptionNULLNULLNULL
BackupType122
Position123
BackupSize173091840747528192
FirstLSN410000000544000004100000005440000041000000059200000
LastLSN410000000568000004100000005920000041000000059200000
CheckpointLSN410000000544000004100000005440000041000000054400000
DatabaseBackupLSN410000000416000004100000005440000041000000054400000
BackupStartDate1/3/07 8:15 PM1/3/07 8:39 PM1/3/07 8:40 PM
BackupFinishDate1/3/07 8:15 PM1/3/07 8:39 PM1/3/07 8:40 PM
BackupTypeDescriptionDatabaseTransaction LogTransaction Log
BackupSetGUID1389292F-F593-425D-BD36-325FCEA0E02A1DAB6FAA-14AD-4C3C-8081-6A15CB170782285DC2A1-1E89-44A5-B9ED-373821C94054

So how does this information help you restore your databases?
When your backup files contain multiple backups in one file you need to specify the position of the file that you are restoring.  This option for the RESTORE command is FILE, but this number corresponds to the Position value.  So if we want to restore these files using the RESTORE command we would issue the following three commands one for each of the backups using the value that is in the Position from the HEADERONLY output..
RESTORE DATABASE AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak' WITH FILE = 1, NORECOVERY
RESTORE LOG AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak' WITH FILE = 2 , NORECOVERY
RESTORE LOG AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak' WITH FILE = 3, RECOVERY
In addition to being able to restore multiple backups from one backup file, we can also use the output from the FILELISTONLY to determine where the default locations will be for the data and log files.  If you take a look at the output above from the FILELISTONLY command and look at the values in the LogicalName and PhysicalName you will see the directory where the database was stored was in the "C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\" directory.  If you just do a RESTORE the data and log files will be created in this directory.  If the directory does not exist or if you want to specify another directory or file name you need to use the WITH MOVE option of the RESTORE command.  This can be done as follows:
RESTORE DATABASE AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak' 
WITH FILE = 1, 
RECOVERY, 
MOVE 'AdventureWorks_Data' TO 'J:\SQLdata\AdventureWorks_Data.mdf', 
MOVE 'AdventureWorks_Log' TO 'X:\SQLlog\AdventureWorks_Log.ldf'