Sql-server – Database restore using SSMS leaves source database in restoring state

restoresql serversql-server-2016ssms

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.

Best Answer

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.

Related Question