RSS Feed

Lesson Learned: Only Restoring WHERE you want


September 17, 2009 by Mike Hillwig

As a DBA, my job is to protect the company’s data at all costs. But what happens when it’s the DBA who causes data loss?

I wrote about this a couple of years ago when I accidentally restored a backup copy on a production instance.

One of the things I do on a regular basis is to restore a copy of our databases onto test and development servers. I have this down to a science.  Today, I did the ultimate DBA f**kup. I ran the restore process on the production database server instead of the test server. 30 seconds into it, one of my coworkers came running into my office asking what was happening. I saw my career flash before my eyes. It couldn’t have happened at a worse time. All three of our business units are in the midst of month-end reconciliations, known as CTCs. This happened about 2:30 and we were down for just about an hour. Worse yet, we had to go back to our noon backup, meaning we lost about two hours worth of data.

My boss at the time was an incredibly good guy. I had honestly expected him to show me the door, and he would have been completely justified in doing so. Instead, he asked how we prevented this from happening in the future. Yes, he was using this as a learning/teaching experience.

Today, any time I have a script that does a restore like this, it has a section at the top that looks something like this:

IF @@servername = ‘prodserver’
PRINT ‘This is the production server. You can”t do that!’

ELSE IF @@servername <> ‘devserver’
PRINT ‘This isn”t the DEV server, you can”t do that!’


— Rest of restore script goes here

I have to admit this little code snippit has has saved my tail a few times since then. I’m also a lot more careful when it comes to doing restores. Mother History is one hell of a teacher.