What is in your SQL Server backup files?

By: Greg Robidoux   |   Comments (2)   |   Related: > 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:

• RESTORE HEADERONLY - contains a list of backup header information for a backup device
• RESTORE FILELISTONLY - contains a list of the data and log files contained in the backup
• RESTORE LABELONLY - contains information about the backup media

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'

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

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

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.

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'

Next Steps

• A lot of of what was discussed above can be done by using the GUI, but using the T-SQL commands can be much faster
• Keep these commands handy so you know what options you have to determine the contents of your backup files
• Take a look at these other backup and restore tips
  • SQL Server backup and restore across the network
  • Using passwords with backup files
  • Database Maintenance Plans and Backup File Management in SQL Server 2005
  • Verifying Backups with the RESTORE VERIFYONLY Statement
  • Simple script to backup all databases
  • COPY_ONLY Backups with SQL Server 2005
  • SQL Server 2005 Backup Product Options
  • Database Backup and Restore Failure Notifications
  • Backup to multiple files for faster and smaller backup files

About the author

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