8

I am using SQL Server 2016 (Developer edition) and wanted to create a copy of a database in my Server instance. I did a simple backup and then a restore by right clicking the database again -> Tasks -> Restore.

By changing the name of the database, the database indeed gets restored in a new created database. So far, so good. However, the original database, that I started with, and where I started the restore from, gets a (Restoring...) state which is impossible to change in a normal way.

Of course I know how to restore a database differently without creating this problem and, I have my original database working again, but my question is this:

Is this normal behavior? I think I stumbled upon a bug.

Improve this question

1 Answer 1

Reset to default
17

This is "normal" behaviour in SSMS if you check the box "Take tail-log backup before restore" and the box "Leave source database in the restoring state"

It may not be intuitive and it's probably not the best decision ever to check those boxes by default but you have to un-check the box "leave source database in restoring state" to avoid that behaviour:

enter image description here

So it's not a bug, it's standard SSMS behaviour that changed in a more recent version (I believe those options appeared in 2012 but I'm not sure).

If you want to avoid strange or buggy behaviour in SSMS your best option would be to perform your restores using T-SQL scripts so you have something reproducible even when you change your SSMS version.

See this connect item which clearly states this is by design.

Improve this answer
2
  • there's a blog post about exactly that here sqlundercover.com/2017/08/14/…
    – David Fowler
    Commented Aug 24, 2017 at 10:09
  • 3
    The Take tail-log backup before restore and Leave source database in the restoring state (WITH NORECOVERY) were available from SSMS 2012 onwards. If you perform a restore as you described in SQL 2012, then both options are selected by default. In SSMS 2014 these options are not selected by default. Apparently MS has decide to turn it back on by default for SSMS 2016.
    – John K. N.
    Commented Aug 24, 2017 at 11:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.