SQL with Manoj

 
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:

RESTORE DATABASE db_name WITH NO RECOVERY

… 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:

RESTORE DATABASE dbName WITH RECOVERY

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 !!!