Having a complete backup history of all of your backup and restore operations can become quite useful at times.
All the data that you might need to be related to this, like who created the backup, at what time, on what media, and many other details are stored in the system databases of the SQL Server instance. As we said, the complete history of backup and restore operations are stored in the system database, specifically the msdb database.
In this article, we will try to introduce you to the main tables which store information related to backups and to the most useful T-SQL statements that are used to access the history of your backups.
Also, we will cover in small detail the times you should use media-header information compared to backup-header information for your backups, and a few details about database and transaction log files.
Backup History tables
The tables that are used to store backup history information are part of the system msdb database and they are:
- backupfile – each row represents a data or log file that has been backed up
- backupfilegroup – each row represents a filegroup in a backup set
- backupmediafamily – each row represents a media family. In case the media family is in a mirrored media set, then the family will have a separate row for each mirror in the media set
- backupmediaset – each row represents a backup media set
- backupset – each row represents a backup set
When a restore operation is performed on a database, backup history tables are going to be modified.
Access Backup History with T-SQL statements
In earlier versions of SQL Server, all users had the possibility to get information about the backup sets and devices by using the following statements:
RESTORE FILELISTONLYRESTORE HEADERONLYRESTORE LABELONLYRESTORE VERIFYONLY
But because these commands give access to information about the content of backup files, since SQL Server 2008 these statements will need access to CREATE DATABASE permission before you can run them.
RESTORE FILELISTONLY uses information from backupfile table in order to return a result set that contains a list of the database and the log files that were part of a specific backup set.
RESTORE HEADERONLY uses information from backupset table to retrieve details of all backup header information for all of the backup sets that are defined on a specific backup device. The result of running this query is also a result set.
RESTORE LABELONLY uses information from backupmediaset will return information about backup media family from a specified backup device.
Media-Header Information
By viewing the media header you will see information about the media itself, not about the backups on the media. This information that is displayed will include the media name, its description, the software tool that was responsible for creating the media header, and the day when the media header has been written.
Backup-Header Information
This section displays information about all SQL Server and non-SQL Server backup sets that are present on the media. The information will include what types of backup devices are used, the types of backups (database, transaction, file, or differential), and the start and stop date/time of the backup.
This will be helpful when you have to determine the backup set which is set on tape that you want to restore, or if you just want to know what backups are on that media.
Which Backup Set to restore?
You will be able to use the information in the backup header in order to be able to identify which backup set to restore.
The Database Engine will index with a number each backup set on the backup media which will allow you to simply identify the backup set you have to restore by using its position on the media.
Media-Header vs Backup-Header information
The difference between these two commands is in the information they return. Obtaining information about the media header will require retrieving information only from the start of the tape, while the backup header information will require reading the whole tape to look at the header of each backup set.
Database and Transaction Log Files for Backup History
You might wonder why these files would help you in the case of backup history. Well, the information displayed when the database and transaction log files are listed inside a backup will include the physical name along with the logical name, the type of file, filegroup membership, size of the file, and the maximum allowed size and also predefined file growth size.
With all of this information at your hands, you can determine, in the following situations, the names of the databases on a backup before you restore the backup itself.
-
- In case you’ve lost a disk drive that had one or more errors on the files of a database.
- You will be able to list the files in the backup and then determine which of them were affected. After that, you can restore those specific files on another drive at the time you restore the entire database. Otherwise, restore just those specific files and later apply any transaction log backups that you made since the database was backed up.
- In case you are in the process of restoring a database from one server to another, but the directory structure and the mapping of the drive are not yet created on the server
- For this situation, listing the files in the backup will help you to determine what files were affected. As an example, if the backup contains a file that it has to restore to a drive G, but the server that is set as a destination does not have a drive with the letter G. The file will have to be relocated to another location, such as drive Z, at the time the file is restored.
- In case you’ve lost a disk drive that had one or more errors on the files of a database.