Viewing Information About Backups

After backups are created, you may need to view information about the backups, such as:

• A list of the database and transaction log files contained in a specific backup set.
  
  
• The backup header information for all backups on a particular backup media.
  
  
• The media header information for a particular backup medium.

Listing Database and Transaction Log Files

Information displayed when listing the database and transaction log files 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 to determine the names of the files in a database backup before restoring the database backup when:

• You have lost a disk drive containing 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 restoring the entire 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 allows you to determine which files are affected. For example, the backup contains a file that it needs to restore to the E:\ drive, but the destination server does not have an E:\ drive. The file needs to be relocated to another location, such as the C:\ drive, when the file is restored.

Viewing Header Information

Viewing the backup header displays information about all Microsoft® SQL Server™ and foreign backup sets on the media. Information displayed includes the types of backup devices 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 need to determine which backup set on the tape to restore, or the backups that are contained on the media.

Note  Viewing backup header information can take a long time for high-capacity tapes because the entire media needs to be scanned to display information about each backup on the media.

Viewing the media header displays information about the media itself, rather than the backups on the media. Media header information displayed includes the media name, description, name of the software that created the media header, and the date the media header was written. For more information about a detailed list of the header information displayed, see RESTORE LABELONLY.

Note  Viewing the media header is quick because only the media header is read after it has been located one time at the beginning of the media.

The following chart provides an example of the differences between viewing backup header and media header information. In this example, restoring the backup header information for the tape media containing two SQL Server backups and two foreign (Microsoft Windows NT® 4.0 or Microsoft Windows® 2000) backups retrieves information for all backup sets on the media, requiring that the entire tape be scanned. However, restoring the media header requires only information from the single media header written at the beginning of the tape to be retrieved.

To view the data and log files in a backup set

Transact-SQL