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

3
  • This is another great application for log shipping. Pretty trivial to set up a job that restores your full backups every week, your log backups in the meantime, and alert on any issues... Jan 23, 2016 at 17:01
  • Not sure I get how you would use it in such a way. Do you have any articles describing such a scenario?
    – HansL
    Jan 23, 2016 at 19:18
  • 2
    Just about any article on log shipping. Think about it, if you're constantly restoring your full and log backups on another server, and monitoring for success, doesn't that fulfill just about everything on your checklist? Verifying a backup is pretty useless on its own - what's really important about a backup is that it can be restored. Jan 23, 2016 at 19:27

3 Answers 3

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

3
  • ♦, Good explanation with answer. +1 Jan 25, 2016 at 5:16
  • Okay, thanks. I need to setup a restore test solution for our americas region, so maybe I'll give it a try and provide some comments afterwards.
    – HansL
    Jan 25, 2016 at 7:08
  • I've read your article and it's quite interesting and might help me in another scenario that I'm dealing with. But as far as automatic restores, it either seems to be a bit static in the sense that you specify which databases to restore, or ends up looking a bit like my current solution, looping through folder structures to get the correct files needed regardless of how many databases there are. But great read, thanks.
    – HansL
    Mar 7, 2016 at 10:26
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.

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.