Professional SQL Server 2005 Administration (Wrox Professional Guides)

Managing your backups is another important DBA task. The better your maintenance procedure, the faster and more error-free 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 are some tips to 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 to reuse a tape before throwing it away. There is an additional tape cost, but a worn tape can stop a successful restore.

• Set up a storage location where the backups can easily be organized, located, and accessible. For example, if you're not available, someone else needs to perform the restore and will need 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 access to a backup. Moreover, keep a copy of the more recent backups at your location in case they are quickly needed for a restore.

• The backup storage location should be secured where unauthorized individuals would not have access to sensitive data. Moreover, password-protect the backup. Furthermore, for the most sensitive data, use SQL Server 2005 column-level encryption.

• Set up a logistical procedure to promptly move a copy of each backup to the offsite location to prevent it from being destroyed in a disaster.