Professional Microsoft® SQL Server® 2008 Administration

18.6. Managing Backups

Managing your backups is another important DBA task. The better your maintenance procedure, the faster and more accurately the backups will be identified and quickly restored. Meticulously running backups does little good if the backups cannot be identified or, worse, were lost or overwritten. The following tips should help your backup management program:

• Descriptively label all the backups to prevent overwriting or misplacing a backup.

• Set up a retention policy to prevent a tape or disk backup from being overwritten. These may be dictated by corporate policy, government regulations, cost, space, or logistics.

• Tapes can go bad, so set up a reuse policy. Define how many times a tape may be reused before throwing it away. This adds a tape cost, but a worn tape can stop a successful restore.

• Set up a storage location where the backups can easily be organized, found, and accessed. For example, if you're not available and someone else needs to perform the restore, that person must be able to get to the location and correctly identify the backups. You should also keep a copy of the backups stored offsite in a location where they will be protected from a local disaster. This offsite location should allow 24-hour access in case you need a backup. Moreover, keep a copy of the more recent backups locally in case they are quickly needed for a restore.

• The backup storage location should be secured such that unauthorized individuals do not have access to sensitive data. ...