Viewing Information About Backups

A complete history of all SQL Server backup and restore operations on a server instance is stored in the msdb database. This topic introduces the backup and restore history tables and also the Transact-SQL statements that are used to access backup history.

The topic also describes how to do the following:

• List database and transaction log files.
• View the media-header information.
• View the backup-header information.

Additionally, this topic compares the media-header information to backup-header information. For more information, see "Comparison of Media-Header and Backup-Header Information," later in this topic.

Overview of Backup and Restore History Tables

This section introduces the history tables that store backup and restore metadata in the msdb system database.

To delete old rows from backup and restore history tables

• sp_delete_backuphistory (Transact-SQL)

To delete all rows for a specific database from backup and restore history tables

• sp_delete_database_backuphistory (Transact-SQL)

Transact-SQL Statements for Accessing Backup History

The restore information statements correspond with information stored in certain backup history tables.

Column-Naming Conventions

For historical reasons, two different naming conventions exist. Old columns retain their original names. However, columns that are new in SQL Server 2005, follow the naming conventions shown in the following table.

Listing Database and Transaction Log Files

Information that is displayed when the database and transaction log files are listed in a backup includes the logical name, physical name, file type (database or log), filegroup membership, file size (in bytes), the maximum allowed file size, and the predefined file growth size (in bytes). This information is useful, in the following situations, to determine the names of the files in a database backup before you restore the database backup:

• You have lost a disk drive that contains one or more of the files for a database.
  You can list the files in the database backup to determine which files were affected, and then restore those files onto a different drive when you restore the whole database; or restore just those files and apply any transaction log backups created since the database was backed up.
• You are restoring a database from one server onto another server, but the directory structure and drive mapping does not exist on the server.
  Listing the files in the backup let you determine which files are affected. For example, the backup contains a file that it has to restore to drive E, but the destination server does not have a drive E. The file must be relocated to another location, such as drive Z, when the file is restored.

For more information about the file information that is returned, see RESTORE FILELISTONLY (Transact-SQL).

To view the data and log files in a backup set

• RESTORE FILELISTONLY (Transact-SQL)
• ReadFileList (SMO)

Viewing the Media-Header Information

Viewing the media header displays information about the media itself, instead of about the backups on the media. Media header information that is displayed includes the media name, description, name of the software that created the media header, and the date the media header was written.

To view media header information

• RESTORE LABELONLY (Transact-SQL)
• How to: View the Properties and Contents of a Logical Backup Device (SQL Server Management Studio)
• How to: View the Contents of a Backup Tape or File (SQL Server Management Studio)
• ReadMediaHeader (SMO)

For more information, see "Comparison of Media-Header and Backup-Header Information," later in this topic.

Viewing the Backup-Header Information

Viewing the backup header displays information about all SQL Server and non-SQL Server backup sets on the media. Information that is displayed includes the types of backup devices that are used, the types of backup (for example, database, transaction, file, or differential database), and backup start and stop date/time information. This information is useful when you have to determine which backup set on the tape to restore, or the backups that are contained on the media.

To view backup header information

• RESTORE HEADERONLY (Transact-SQL)
• How to: View the Contents of a Backup Tape or File (SQL Server Management Studio)
• How to: View the Properties and Contents of a Logical Backup Device (SQL Server Management Studio)
• ReadBackupHeader (SMO)

For more information, see "Comparison of Media-Header and Backup-Header Information," later in this topic.

Identifying Which Backup Set to Restore

You can use information in the backup header to identify which backup set to restore. The Database Engine numbers each backup set on the backup media. This lets you identify the backup set you want to restore by using its position on the media. For example, the following media contains three backup sets.

To restore a specific backup set, specify the position number of the backup set you want to restore. For example, to restore the second backup set, specify 2 as the backup set to restore.

Comparison of Media-Header and Backup-Header Information

The following illustration provides an example of the differences between viewing backup-header and media-header information. Obtaining the media header requires retrieving information from only the start of the tape. Obtaining the backup header requires scanning the whole tape to look at the header of every backup set.

For information about how to view the media-header, see "Viewing the Media-Header Information," earlier in this topic.

For information about how to view the backup header information for all backup sets on a backup device, see "Viewing the Backup-Header Information," earlier in this topic.

See Also

Concepts

Verifying Backups

Other Resources

BACKUP (Transact-SQL)
Working with Backup Media in SQL Server

Help and Information

Getting SQL Server 2005 Assistance

Change History