What is in your SQL Server backup files?

By:   |   Comments (2)   |   Related: > Backup


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.


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.

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
RecoveryModel FULL
DifferentialBaseLSN NULL
DifferentialBaseGUID NULL
BackupTypeDescription Database
BackupSetGUID 1389292F-F593-425D-BD36-325FCEA0E02A
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
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
DifferentialBaseLSN 41000000041600000 0
DifferentialBaseGUID 6493F201-EBBA-47DD-BBDA-83A2772A8DA3 00000000-0000-0000-0000-000000000000
IsReadOnly 0 0
IsPresent 1 1
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 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' 
MOVE 'AdventureWorks_Data' TO 'J:\SQLdata\AdventureWorks_Data.mdf', 
MOVE 'AdventureWorks_Log' TO 'X:\SQLlog\AdventureWorks_Log.ldf'
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, May 30, 2017 - 7:06:02 AM - Greg Robidoux Back To Top (56262)

Hi Michael, you can run this command from a SQL Server Management Studio query window.


Monday, May 29, 2017 - 3:05:02 PM - Michael Back To Top (56202)

Hello, I am new to SQL, with your command RESTORE FILELISTONLY - what program is that run from? I have the back file but want to know what is in there. 




get free sql tips
agree to terms