This tip might seem obvious, but I occasionally come into a situation where “something†(usually a third party backup tool) is taking backups of the SQL Server databases, but the system administrators don’t know exactly where, or how to restore the file. The case that triggered this blog post involved a SQL Agent job that generated an error when being modified, and losing the final 32 steps. Whoops. My colleague rang me, and my suggestion was to restore a copy of MSDB from backup so he could script out the job again.
The initial phone call to the sys admin went something like this “Do we take backups of the MSDB database?â€. “Uh, err, let me check… … … No.â€
I jumped on the server and identified that yes, something was backing up MSDB each night, but it was going directly to a device which I had no visibility over. To cut a long story short, the backup was found, but the backup tool apparently only allows you to restore over the top of the database you backed up – not something we wanted to do.
Learning where your backups go, and how to restore your databases to any location is something you don’t have the luxury of doing when you’re under pressure. You need a pre-generated sequence of steps to run through in an emergency to take the guesswork out of it.
In this situation, my colleague ended up re-entering all the missing steps from the description of each step from the job’s history. What should have been a simple task (restore MSDB elsewhere, script out the job, re-install the job) became impossible, and this job had to be running soon.
A similar situation exists in if you haven’t tested every single backup, you may not know that your backup file is corrupted. The odds of a dead backup are small, but it’s always possible. The best way to be sure is to actually restore the backup to another location, but even running a backup verification is better than nothing.