“I just tried restoring a copy of my database but now the original’s in recovery”
That was the panicked question that I was asked earlier today and as it’s something that I’ve seen come up more than a couple of times, I thought I’d write up a lunchtime quickie to explain the issue.
If you ever restore a backup to a new database, there’s something that you should probably be aware of otherwise you could easily find yourself in this situation.
Let’s have a look at what happens when we try to restore a copy of the SQLUndercover database using SSMS. We’re going to kick this off by right clicking on ‘SQLUndercover’ and selecting restore database.
We’ll change the destination database and destination files so we restore a new copy of that database.
Excellent, the restore finished and as we can see in SSMS, we’ve now got a copy of sqlundercover. But hang on a minute, why is our original database now showing restoring????
Did we somehow restore the backup against our original database?
No we didn’t, what we’re seeing here is the effect of one of the default options that is always set when you right click a database and choose restore. Let’s have a look at the options screen when we do the restore.
See that option right there, the one that says ‘Leave source database in the restoring state’? That there’s your problem and will always be selected by default when you right click a database and choose ‘restore database’. Simply make sure that you uncheck that option and you’ll never have the problem again.
But what if you’ve come here because you’ve already got your database in the restoring state, how do you get it back up and running again?
You can simply run the following code and you’ll be back up and running.
RESTORE DATABASE SQLUndercover WITH RECOVERY
SQL Undercover 