Trigger Happy Rotating Header Image

December, 2013:

Automated Backup Testing

There has been some interest expressed in my script for automated backup testing.  I, rather glibly, said in my last post that I could share the scripts with you.  That was before I took another good look at them, however.  It turns out that my solution is integrated rather heavily into my database maintenance solution which is many years old and probably not as good as Ola Hallengren’s – the only reason I still use it is that I wrote it before I knew of Ola’s and it’s not broken…

I have extracted the main functionality and post it here as a starting point for you.  It’s possible that I could further develop this into more of a solution that you can just install and use, but for now, here is something that should get you started.

The main limitation of this script is that it pulls database backup information from MSDB and then executes RESTORE – all in the same stored procedure, which means you’d be doing your test restores on your production server.  If you happen to have the capacity (both space and resource capacity) to do this, it will work just fine, but you probably want to run your tests on some server other than production.  This means that your test server would have to find some other way to get the backup information – perhaps it can monitor a file share where the backups show up, or perhaps it could reach through a linked server.  Perhaps a powershell script is more suitable for this sort of thing once multiple servers are involved.  In any event, the multiple server question makes this a whole different problem (one which I have solved with one client, by the way, but the solution is very specific and it would simply be too much work to generalize it for a blog post like this).  The logic in the script here and this discussion should give you enough to go in the right direction if you need to do this – or you could always contract me 🙂

For now, here is the script – as the header says, please come back to this post and comment if you find issues with it, have updates to suggest or just have something to say about it.


Note that while I have done some work to make sure this script does not do anything exceptionally stupid, it *does* run the DROP DATABASE command – it is probably possible to configure it to do some real damage, so please be careful – consider yourself warned.


Test your backups now

Hello – just a quick post today to remind you to test your backups.  Don’t just look to see if the file gets created and copied offsite, actually restore it somewhere and run CheckDB on it.

I have an agent job that does this for me every day for some of my clients.  It might seem anal, but you’d want to automate it anyway, and once it is set up, it just runs and you can forget about it (more or less) – at least until you get an alert that it failed, which happened to me this morning.  When I looked into it, I found that RESTORE simply errors out when trying to read the file that BACKUP wrote only hours before.  BACKUP reported no errors when writing the file, and as far as it was concerned, the backup was done, but RESTORE just barfed – the file is no good.  If I hadn’t been testing my backups, I would never have known my backup was no good unless I needed it which, I think we can agree, is a *really* bad time to find something like that out.

So what happened to the backup?  What was wrong with it?  I could probably look into it and figure it out, but much like Neo’s ability to dodge bullets in the Matrix, it’s much better if I don’t have to…  I caught it right away and took another backup (which, of course, I immediately tested).  If it happens more than once in a blue moon, I’ll look into it, but this one I’ll just delete and chalk up to an anomaly.  I’m a happy DBA and my client’s data is safe for another day.

I am not aware of any tools that automate backup testing for you, although I admit that I haven’t really looked into it.  The script I wrote is pretty straightforward and I can share it with you – just contact me