Sql-server – Automated regular tests of your backups

automationbackuprecoverysql server

I was doing some investigation on how other DBAs go about performing automated regular tests of their database backups to ensure recoverability in case of disaster and I'm quite disappointed at what sparse topics I found on the topic.

I'll try to describe the solution that I came up with for my company in the comments, but how do you guys ensure that your backups are actually recoverable?

  1. Do you recover your backups to test servers on regular basis? Using scripts?
  2. Third-party software with this capability?
  3. Running verify-only on the backups?
  4. Letting your junior DBA do recovery tests?

Let me know if I should rephrase or add anything to the question.

UPDATE

After some time and polishing, i decided to publish my script if anyone's interested. https://github.com/curiebabz/SSARS

Best Answer

Provided that you have alerts set up on the jobs, Log Shipping is a feature that is available on all editions, doesn't require a whole lot of effort to set up, and I believe fulfills all of your requirements.

I would avoid any wizards that you might stumble upon in Management Studio. Essentially you need three jobs for the following tasks:

  1. Initialize the secondaries (restore the latest full backup with NORECOVERY)

  2. Take full and log backups

  3. Restore log backups with STANDBY, NORECOVERY

Essentially, testing the restores of your full and database backups will be verifying them (and you can add other tasks to this process, such as bringing a restored database into recovery and checking data, running CHECKDB, etc)

I go into great detail about how I have implemented log shipping (different purpose, but generally the same approach):

Related Question