Managing Backups
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 safely overwritten.
- 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 use the history information stored in msdb to apply all the transaction log backups that relate to a specific database backup when the database backup is restored.
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. This allows for easy identification of the data stored on the backup media or the specific backup set.