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:
- RESTORE HEADERONLY
- RESTORE FILELISTONLY
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:
RESTORE HEADERONLY FROM DISK = N’C:tempMyBackup.bak’
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.
RESTORE FILELISTONLY FROM DISK = N’C:tempMyBackup.bak’ WITH FILE = 3;
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.
# # #
