Collected Wisdom and Good Ideas for Backup and Restore

The following list is an accumulation of experiences that are really not documented in one place, but all belong together in a proper review of backups and restores .

• Make sure that you label each tape appropriately with information such as what is on it, date and time, and so on.

• Tapes go bad. They are a mechanical device and also magnetically sensitive. Both are causes of failure. Plan for this. Buy extra tapes and be aware of the possible contingencies within your chosen backup strategy.

• Try to avoid putting too much data on a tape. SQL Server allows you to append multiple backups on a tape as space permits . The problem is if all your backups are on one tape and that tape goes bad, you lose all your backups. Spread your risk. Use multiple rotations .

• Cycle your tapes appropriately. Do not keep backing up databases to the same tapes repeatedly. This wears down the tape mechanism, and you might get confused about what is exactly on the tape.

• Store your tapes properly. Label them. Keep them in their cases. Recovery time is extended if you do not know what is on each tape.

• Consider multiple copies of your backups and rotate one copy to an off-site location. For a small shop, this can mean keeping a copy in a bank safe deposit box. For a large shop, there are sites that specialize in off-site data storage. Remember, a bank safe deposit box is only open during banking hours ”a dedicated off-site provider is open 24/7. Additionally, make sure all critical parties are on the list to retrieve the tapes. Just like anything, you get what you pay for.

• Many companies have rotations that go into permanent off-site storage due to government regulations. However, in 10 years , will the tape media that you have off-site be restorable? Technology changes very fast. That QIC40 tape from 1992 is pretty useless today if you do not have a QIC40 tape drive and the accompanying software. Plan for this. This is where backing up to disk and then to tape is better.

  Tip

  SQL Server will not always support restoring backups from one version of SQL Server to another (such as restoring SQL Server 6.5 backups to a SQL Server 2000 instance), so if you need to keep backups for some time, you might want to consider periodically restoring them, upgrading the database to the newer version, and then backing it up.

• If you have to restore at a remote location, make sure that location has the same tape drives you do. If you have LTO technology, but the remote site has DLT, you needlessly waste time. This needs to be planned for.

• Know what versions of software you are running. Older versions of SQL Server can be incompatible with current ones. The physical characteristics of SQL Server storage changed from SQL Server 6.5 to SQL Server 7.0. Have xp_msver , @@version, and SQLDIAG information stored with your backups.

• Have copies of your binaries stored off site, too. This should include the operating system, SQL Server, and the associated license keys, too.

• At a minimum, make regular backups of the master and msdb databases in addition to your user database.

• Backups do not validate the data they are backing up. Run DBCC CHECKDB as part of a regular maintenance plan. Remember, you can back up corruption.

  Tip

  You might want to restore the backup and perform DBCC CHECKDB to bolster your confidence in your backup strategy and the backups itself.

• Automate as much of your backup strategy as possible. Human intervention causes many frequent problems. Use the SQL Server Agent and possibly the Database Maintenance Wizard to simplify automation if it is appropriate.

• Review the backup logs. This sounds obvious, but many people just change the tapes without bothering to see if the backup even ran. For example, a backup might have aborted due to a faulty tape drive.

• You are only as safe as the last good backup you restored. Tapes can and do go bad. No one can guarantee the quality of a backup until it has been restored. As such, restore from your backups frequently to test their validity and the process you have in place for recovery. Tape vendors generally specify how long their products will hold information. Written specifications often indicate many years. However, do not rely on the published media life numbers . The more use, the more wear on the tape.