2

I have copied a database vn_Budget_2021_BofDOct29 to the database vn_Budget_2021_Rig2. I did this through Tasks->Restore Database shown in the second image.

I saw this similar issue except the database I've restored isn't the one hanging. Its my source DB that doesn't require any restoring in iteslf. SQL server databases stuck in restoring state

How do I get it stop hanging? I don't need to restore this database to the same name. The last backup is a few days old and theres nothing wrong with the data.

Restore to Rig2 successful

Method of restoring. No logs were taken which may be the issue? restored through Tasks

  • Was the database in a "restoring" state before you did this ? – Dominique Boucher Nov 20 at 18:41
  • Hi Dominique, no it was not! – KickingAustin Nov 20 at 19:31
6

This can happen when you don't uncheck in the GUI to take a tail-log backup.

It is a nasty behavior by the GUI. You type a new database name in the restore GUI, the one to be created by the RESTORE command. But the GUI still does a tail-log backup of the source database, and it does it by default using the NORECOVERY option (which s precicely what puts the source db in restoring state).

The image you posted doesn't reflect this, so I can deduce that it wasn't the actual screen from your restore. You would have a note in the upper left of the dialog.

So, in the future, go to the Options page and uncheck the tail log backup when you restore into a new database name.

To handle the immediate situation, you just execute below (obviously substituting the database name):

RESTORE DATABASE dbname WITH RECOVERY

No backup files needed.

| improve this answer | |
  • You're a lifesaver. Thank you @TiborKarasz. It worked immediately! I'll be sure to check off that option in options next time! – KickingAustin Nov 20 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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