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