0

Recently I backed up to disk a Sql Server Database through SSMS. Then I restored this .bak file to a new Database with a different name on the same Server. The restored completed and the new Database with the new name is available and populated with data. Everything is OK so far. After re-opening SSMS, the old database that i had backed up is stuck on: (Restoring...) when the new Database is accessible. Is there a way to check what's the problem and if there is a way to "unfreeze" the old database without having to drop it and restore it from the backup ?

Thank you very much in advance

George F Kyritsis
  • 1
  • 1
  • 2
    Same questions, hundreds of upvotes (and an answer!): http://stackoverflow.com/questions/520967/sql-server-database-stuck-in-restoring-state – jleach Nov 17 '16 at 12:17
  • Thank you very much, but the answer says to restore it using the .bak file. As i said, i would like to troubleshoot the problem, and possibly unfreeze it without having to restore it from the back up. I'm looking in the transaction log for answers :P . – George F Kyritsis Nov 17 '16 at 13:08
  • You don't actually have to restore anything... look at the second answer listed (the one not accepted)... you can run just this line: `RESTORE DATABASE WITH RECOVERY` and it's enough to kick SSMS back into place (without actually restoring anything - I've done it a few times by now myself) – jleach Nov 17 '16 at 13:24
  • You were absolutely right, that worked great! The database seems fine. I still wonder thought, what was the problem in the first place. I used the SSMS restore database wizard, and back up wizard for the whole process. – George F Kyritsis Nov 17 '16 at 13:42
  • I'm not sure... happens every once in a blue moon for reasons that I never pinpointed (or have forgotten). I was concerned about residual/hidden effects, but I've done it a few times over the years now and have never had further issue. I *almost* attribute it to some SSMS bug, but I actually do think there's a real reason for it - I just don't recall what it is. – jleach Nov 17 '16 at 14:04

0 Answers0