Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

What is in your SQL Server backup files?


By:   |   Read Comments   |   Related Tips: More > Backup

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.

Solution
Along 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'

BackupName AdventureWorks-Full Database Backup
BackupDescription NULL
BackupType 1
ExpirationDate NULL
Compressed 0
Position 1
DeviceType 2
UserName EDGENB2\Sysadmin
ServerName EDGENB2\TEST1
DatabaseName AdventureWorks
DatabaseVersion 611
DatabaseCreationDate 38985.72449
BackupSize 173091840
FirstLSN 41000000054400000
LastLSN 41000000056800000
CheckpointLSN 41000000054400000
DatabaseBackupLSN 41000000041600000
BackupStartDate 1/3/2007 8:15:41 PM
BackupFinishDate 1/3/2007 8:15:41 PM
SortOrder 52
CodePage 0
UnicodeLocaleId 1033
UnicodeComparisonStyle 196609
CompatibilityLevel 90
SoftwareVendorId 4608
SoftwareVersionMajor 9
SoftwareVersionMinor 0
SoftwareVersionBuild 1399
MachineName EDGENB2
Flags 512
BindingID 5956B629-86DF-4000-BAC0-52194A773D3B
RecoveryForkID B935AAC8-BB1A-4C10-AD0B-014DFEF2FC72
Collation SQL_Latin1_General_CP1_CI_AS
FamilyGUID B935AAC8-BB1A-4C10-AD0B-014DFEF2FC72
HasBulkLoggedData 0
IsSnapshot 0
IsReadOnly 0
IsSingleUser 0
HasBackupChecksums 0
IsDamaged 0
BeginsLogChain 0
HasIncompleteMetaData 0
IsForceOffline 0
IsCopyOnly 0
FirstRecoveryForkID B935AAC8-BB1A-4C10-AD0B-014DFEF2FC72
ForkPointLSN NULL
RecoveryModel FULL
DifferentialBaseLSN NULL
DifferentialBaseGUID NULL
BackupTypeDescription Database
BackupSetGUID 1389292F-F593-425D-BD36-325FCEA0E02A

 

RESTORE FILELISTONLY FROM DISK='C:\Backup\Adv_Full.bak'

LogicalName AdventureWorks_Data AdventureWorks_Log
PhysicalName C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\ AdventureWorks_Data.mdf C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\ AdventureWorks_Log.ldf
Type D L
FileGroupName PRIMARY NULL
Size 188678144 2097152
MaxSize 35184372080640 2199023255552
FileId 1 2
CreateLSN 0 0
DropLSN 0 0
UniqueId 94EDC99D-D0E0-4146-95DA-1756D6C92348 EB9DB2B3-BE70-4F76-8345-7FF07FB705C7
ReadOnlyLSN 0 0
ReadWriteLSN 0 0
BackupSizeInBytes 172163072 0
SourceBlockSize 512 512
FileGroupId 1 0
LogGroupGUID NULL NULL
DifferentialBaseLSN 41000000041600000 0
DifferentialBaseGUID 6493F201-EBBA-47DD-BBDA-83A2772A8DA3 00000000-0000-0000-0000-000000000000
IsReadOnly 0 0
IsPresent 1 1

 

RESTORE LABELONLY FROM DISK='C:\Backup\Adv_Full.bak'

MediaName NULL
MediaSetId 23979995-927B-4FEB-9B5E-8CF18356AB39
FamilyCount 1
FamilySequenceNumber 1
MediaFamilyId 86C7DF2E-0000-0000-0000-000000000000
MediaSequenceNumber 1
MediaLabelPresent 0
MediaDescription NULL
SoftwareName Microsoft SQL Server
SoftwareVendorId 4608
MediaDate 1/3/07 8:15 PM
MirrorCount 1

 

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.

BackupName AdventureWorks-Full Database Backup AdventureWorks-Transaction Log Backup AdventureWorks-Transaction Log Backup
BackupDescription NULL NULL NULL
BackupType 1 2 2
Position 1 2 3
BackupSize 173091840 74752 8192
FirstLSN 41000000054400000 41000000054400000 41000000059200000
LastLSN 41000000056800000 41000000059200000 41000000059200000
CheckpointLSN 41000000054400000 41000000054400000 41000000054400000
DatabaseBackupLSN 41000000041600000 41000000054400000 41000000054400000
BackupStartDate 1/3/07 8:15 PM 1/3/07 8:39 PM 1/3/07 8:40 PM
BackupFinishDate 1/3/07 8:15 PM 1/3/07 8:39 PM 1/3/07 8:40 PM
BackupTypeDescription Database Transaction Log Transaction Log
BackupSetGUID 1389292F-F593-425D-BD36-325FCEA0E02A 1DAB6FAA-14AD-4C3C-8081-6A15CB170782 285DC2A1-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 = 1NORECOVERY

RESTORE LOG AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak' WITH FILE = NORECOVERY

RESTORE LOG AdventureWorks FROM DISK='C:\Backup\Adv_Full.bak' WITH FILE = 3RECOVERY

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'

Next Steps



Last Update:






About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools