You should always be recoverable. If you haven’t heard that before, take note of it. It’s important.

A while back I wrote about Operational Recovery. This is when there isn’t a disaster but you still need to restore some or all of a database. Someone forgot the WHERE clause on an UPDATE or maybe there was an unexpected cascading delete that deleted 90% of the database (yes I’ve had that one happen to me). Before I make any change to a database I want to be able to back out. I might take a backup, or I might rely on the existing backups and just copy the rows in any tables I’m going to change. One way or another I make sure I can back out any changes I make to a database.

Here is where it gets interesting. When you restore over a database you are making a change to that database. Let that sink in for a moment. You have to restore a database for some reason, disaster or operational and once you are done the database has been changed. So what happens if you mess up? The backup you restored was miss-named, or maybe you restore the wrong database. Or as has happened to me on more than one occasion, once you are done someone says “Hey wait, I needed the data from that table”.

Remember what I said before? You should always be recoverable. So how would you be recoverable after a restore? Take a backup before you do the restore. In fact if you are using the GUI then by default it’s going to want to take a tail log backup of any databases set to FULL Recovery. Personally I prefer to take a full backup before any restores if at all possible. It makes it easier if things go pear-shaped. And if it does go well, I have the full backup from the restore right? I can delete this one after a week or so.

Filed under: Backups, Microsoft SQL Server, SQLServerPedia Syndication Tagged: backups, microsoft sql server