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

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

1 Answer 1

8

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.

0

Your Answer

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

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