Just like insurance premiums, database backup systems are a grudge purchase but losing your data if you don’t have proper coverage can have significant impact on your business.

The key questions to consider are:

  • How much data can you afford to lose should you have a disaster
  • How long can you afford to be off the air
  • Physical constraints
  • Disk space for backups (MUST be on separate media to your database and transaction logs)
  • Network bandwidth when sending backups off-site
  • Cost

if you do decide that losing your data is a significant loss, below are some strategies for backups.

You should be taking regular backups of your database. Backups need to be stored off-site and must be carefully indexed to ensure you can find the files you need quickly.

Your production database should be backed up regularly to a local disk, and then the backup file itself be transported to a safe off-site location. I have outlined some common backup strategies below:

Running a ‘full’ backup takes a snapshot of your database at a point in time. This allows you to restore your data as it was at the time that the backup was taken.

Generally your production database will also produce ‘Transaction Logs’. These transaction logs record all the database updates made since the last full backup and allow you to restore your database back to a point in time by ‘replaying’ these transactions. These transaction logs themselves should be backed up and again stored off-site. Note also that transaction log files MUST be stored on separate disk drives to your database backup. Given this backup scenario, to recover, you restore your most recent Full backup, then ‘replay’ Transaction log backups created since the last full backup.

You can also take Differential backups, which backup all changes to the database since your last full backup. Given this backup scenario, to recover, you restore your most recent Full backup, then your most recent Differential backup, then any Transaction log backups created since the last Differential backup.

Another strategy is to backup and/or mirror your data to a ‘hot’ remote server in real time. This adds to your disaster recovery cost, but then again, what does it cost you to be offline for an extended period?

Whichever strategy you choose, you need to be sure that it is automated and that you have procedures set up to ensure any manual steps (e.g. changing the tape daily) continue to be performed, even if the person usually responsible is on leave or sick.

Finally, whichever strategy you choose, you should test your backup and recovery process. You should, at some stage, test restoring your full and/or differential and/or transaction log backup files to a test server, just to be sure that reality matches theory.

There are several other regular maintenance tasks which should be performed on your SQL database to ensure performance is optimised … stay tuned for my next blog post.