Friday, April 22, 2011

Validating Backups

Question: What's the best way to validate a database?

Answer: Don't do it. Create a backup and run dbvalid.exe against that.

Here are the advantages:

  • If dbvalid says your backup copy is valid, then you know two things: that your backup is valid, and your running database is valid... or at least it was when the backup was taken. If you run dbvalid on your running database first and then create a backup, you don't know for sure if your backup is valid... the backup process itself could have introduced disk errors, for example.

  • You can run dbvalid in read-only mode on your backup copy, thus avoiding false validation errors caused by transactions in progress.

  • You can move the backup copy to a different computer and run dbvalid there, thus avoiding database performance problems.

  • You can combine and automate and schedule the whole process without interfering with day-to-day operations, and without forgetting to run one or the other (backup and validation).

Question: What's the best way to validate incremental transaction log backups?

Answer: By running dbsrv12 -ad to apply each new log backup to a copy of the database backup.

Here are the advantages:
  • If you run dbsrv12 -ad right away, to apply each incremental transaction log backup file to a running copy of your previous full database backup file, then you know that all the transaction log backups are fully operational and ready when you need them... because dbsrv12 -ad is exactly what you'll be using after disaster strikes.

  • The running copy of your database backup is relatively up to date, in case that's the version you need when it comes time to restore... but, you don't have to use it, if you need to back up to an earlier restore point.

  • Only one extra copy of the database is required, above and beyond the running database, the full database backup file and all the incremental log backup files. That extra copy may also be used to validate the database as described above; if you start the database with dbsrv12 -r when running dbvalid, you can still run dbsrv12 -ad later on to apply more transaction logs.
Knowing your backup is valid is more important than knowing your running database is valid.

No comments: