What is in My Database Backup File

Suppose you are a new DBA in an environment. You are tasked with restoring a database, but you don’t have a single clue as to what is in any of the database backup files. Maybe the backup file names might give you a clue, and then again maybe not. Possibly the names are so cryptic that they are meaningless as to the content of the backup.  

The Microsoft SQL Server team has made it easy for you to retrieve the information in a backup file by using one of the following two commands:


The RESTORE HEADERONLY command displays information about all the backup sets stored in a backup device. Consider a situation where I have a backup named “C:tempMyBackup.bak”. This backup name is meaningless and provides no clue as to what is contained in the backup. In order to determine what is in this backup file, I can run the following command:


When I run this command, I will get a row returned for every backup file that is contained in this operating system file. Remember SQL Server allows you to have multiple database backups that are stored within a single operating system file. 

For each row returned from the RESTORE HEADERONLY command, I can find out things like:

  • The database that was backed up
  • The time the backup was taken
  • The type of backup (full, differential, or transaction log)
  • The version of the backup
  • The size of the backup

The RESTORE HEADERONLY command only provides information about the different backups contained in a single operating system backup file. If you wanted to find out information about a particular file you can use the RESTORE FILELISTONLY command. Here is an example where I reviewed the third file in the MyBackup.bak file looking for more information.


Following is some of the information you can find out about a single backup file using the RESTORE FILELISTONLY command:

  • The logical and physical names of the database
  • Type of file (data, log, full text, or filestream)
  • The file group name where the database lives
  • The max size of the database

Next time you want to find out information about what is contained in a backup file consider using one of these two different RESTORE commands.

# # #

See All Articles by Columnist Gregory A. Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles