Managing Backups

It is important to manage your backups carefully to ensure that you can restore your system when needed. Each backup contains the descriptive text you provided when you created the backup, as well as expiration information. This information can be used to:

• Identify a backup.
• Determine when the backup can be overwritten, preventing the backup from being reused accidentally.
• Identify all the backups on a backup medium, such as a tape, to determine which backup needs to be restored.

Additionally, the msdb database contains a complete history of all backup and restore operations on the server. SQL Server Enterprise Manager uses this information to suggest and execute a restore plan that can be used if a database needs to be restored. For example, if a database backup for a user database is created every night, and transaction log backups are created every hour during the day, this backup history information is stored in the msdb database. If the user database needs to be restored, SQL Server Enterprise Manager can apply all the transaction log backups that relate to a specific database backup, when the database backup is restored, by using this history information stored in msdb.

Note If the msdb database needs to be restored, any backup history information saved since the last backup of msdb was created is lost.

When working with backups:

• Maintain backups in a secure place, preferably at a site different from the site where the data resides.
• Keep older backups for a designated amount of time in case the most recent backup is damaged, destroyed, or lost.
• Establish a system for overwriting backups, reusing the oldest backups first.
• Use expiration dates on backups to prevent premature overwriting.
• Label backup media to prevent overwriting critical backups, for easy identification of the data stored on the backup media or the specific backup set. For example, if a tape contains only backups of the sales database for January, label the tape medium to state this fact. Alternatively, if a tape contains backups of three different databases, label each backup set on the tape to identify the contents of each backup set clearly.

See Also