Backup before you restore4
April 22, 2015 by Kenneth Fisher
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.
Category: Backups, Microsoft SQL Server, SQLServerPedia Syndication | Tags: backups, microsoft sql server
4 thoughts on “Backup before you restore”
Leave a Reply to Kenneth Fisher Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
I strongly second this from experience (unfortunately).
One of my worst days as an accidental DBA was when I performed a quick restore on a production database and it got stuck on “restoring”.
Yep. Try doing that quick restore and then having someone from another team come to you an hour later to say that all their work for the last 24 hours has disappeared. Not as disastrous maybe but certainly embarrassing.
[…] Part 2 Introduction to Team Foundation Server (TFS) Build for Business Intelligence (BI) Projects Backup before you restore Simplified Editing of Hosts file with PowerShell Painlessly Get Data from PowerShell to Excel SQL […]
[…] Something global. The database was restored over for whatever reason and the permissions weren’t correctly re-applied afterwards. Hard to prove but easy to disprove by looking at the restore history. Hopefully you scripted out the permissions ahead of time or at least took a backup before doing your restore. […]