Archive for November 27, 2015

SQL DBA – Database restored successfully, but stuck in (Restoring…) state

November 27, 2015 Leave a comment

Today after restoring a database on my DEV machine from a PROD backup, I observed that the database was still showing as Restoring… in Object Explorer, and cannot be used. I again checked in SSMS results pane and it was showing as a successful restore with below message:

100 percent processed.
Processed 21713736 pages for database ‘dbName’, file ‘dbName_Data’ on file 1.
Processed 398 pages for database ‘dbName’, file ‘dbName_log’ on file 1.
RESTORE DATABASE successfully processed 21714134 pages in 506.682 seconds (334.808 MB/sec).

I checked online and found that I was using WITH NO RECOVERY option with my RESTORE DATABASE statement, like:


… and by using above option, you make the Database to be unused, and allow more Roll Forward actions to continue with the next RESTORE DATABASE statements in sequence.

As this was not my intention, so I can simply mark by Database to stop accepting more Transaction Logs, by forcing the database out of Restoring state, by issuing following statement:


Converting database ‘dbName’ from version 706 to the current version 852.
Database ‘dbName’ running the upgrade step from version 706 to version 770.

Database ‘dbName’ running the upgrade step from version 851 to version 852.
RESTORE DATABASE successfully processed 0 pages in 1.716 seconds (0.000 MB/sec).

… and my database was out from Restoring… state, and I could use it now !!!

Categories: DBA Stuff, SQL Errors Tags: