6

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

HansL
  • 61
  • 1
  • 1
  • 4

3 Answers3

6

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):

Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
0

We make use of Ola Hallengren's Backup scripts to do your daily backups to our network storage and we're having 3 main regions: Americas, Asia and emea. Emea having around 1.000 databases, Americas and Asia having 200 databases each. Based on this MSSQLTips article, I created a stored procedure taking 3 parameters: Database name, server name and backup path.

On our CMS server we have a management related database, which contains 3 tables for this stored procedure: servers and databases to exclude from restoring (if necessary), table to log failed database restores and a table which just contains all processed databases.

Then an SQL agent job is iterating through all folders on the network storage, extracting server- and database name, and then executing the stored procedure with those as parameters for each server- and database-folder using powershell.

Everything, except the management database, is running on a dedicated SQL server which only purpose is to perform these database automated restore tests, notifying through e-mail if any restores failed.

HansL
  • 61
  • 1
  • 1
  • 4
0

We use Ola Hallengren's backup scripts to perform the backups.

We have created a custom TSQL script that simply runs on a development server once a week which scans the backups and restores the latest Full / Diff and TLog (Whatever is needed really to restore to the most recent backup).

Very simple approach but it works for us in making sure the backups are working if we ever need them.

Lawrage
  • 446
  • 4
  • 11