SSMS- Source Database ‘Restoring’ After Performing a Restore To a New Destination

“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.

recovery4

We’ll change the destination database and destination files so we restore a new copy of that database.

recovery1

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????

recovery2

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.

recovery3

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

One thought on “SSMS- Source Database ‘Restoring’ After Performing a Restore To a New Destination

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: