Site Search:

• All About Backups
• SQL Server 2000
  • Overview
  • Types of Failure
  • Recovery Models
  • System Databases
  • Backup Types
  • Database Files
  • Memory
  • Configuration
  • Troubleshooting
  • MSDB Tables
• SQL Server 2005
• SQL Server 2008 (Late 2008)

SQL Server 2000 Backups and msdb Tables

Important msdb Tables Provide Insightful Backup Related Information

There are a few system tables within the msdb database that you may wish to be aware of for backup and recovery operations. You can query these tables to obtain valuable information.

backupfile

This table keeps track of all of the data files and log files that have been backed up. The backup set id is identified in the table along with logical and physical information about each file that is backed up as part of the backup set.

backupset

This table contains information about each backupset. One of the key columns in this table is the "position" column, which is used as the FILE clause in a restore operation to ensure that you are restoring the right backupset from the backup device.

backupmediafamily

This table provides both physical and logical information for backup media. If you are using a backup device, you'll find its logical name, physical path, and media_set_id. The media_set_id column within this table can be used to join with the media_set_id column in the backupset table so that you can associate backup sets with the appropriate device by device name.

restorefile

Contains one row for each restored file.

restorefilegroup

Contains one row for each restored filegroup.

restorehistory

Contains one row for each system restore operation. Contains good information such as: database name, when the restore occurred, user, what backup set was used, what options were specified in the RESTORE clause, and whether or not it was a point in time recovery.