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.
Enjoy
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.