Ms SQL database stuck in restoring

This post is also available in: Slovenian




Recently my MS SQL database stucked in restoring state and it’s no easy way to move to a normal state. The only thing that helped me was restore db with recovery .

It can be brought to a restoring state because in backup was used NORECOVERY when full recovery was checked. In that case the Microsoft SQL database stands in restoring mode. To resolve stucking restoring mode just enter:

 

RESTORE DATABASE Mydatabase
FROM DISK = ‘ Mydatabase.bak ‘
WITH REPLACE , RECOVERY

 

But first , of course, I recommend to make a backup of the database . Why is that happening ? The most common cause is backup setting , where it is necessary to verify that in the full recovery mode is not marked as follows: ” The backup tail of the log and leave the database in the restoring state . ” It is necessary to tick this option off .

If restore MS SQL database with the above command does not help, then it is necessary to apply a command to drop the DB and then create it again and connect it .

If this does not help, then shut down a server , delete the database and logs, restarts the server and then start restoring database .

 

Microsoft SQL Server advertisement in Shanghai
Microsoft SQL Server advertisement in Shanghai (Photo credit: Wikipedia)

 

 

Saša

Works as system engineer in Slovenian Enterprise in Microsoft environment focusing on security, deployments, SharePoint, SCCM and CheckPoint firewall. Author of successful blog about IT security, Microsoft tips & tricks, social media, internet trends.

Leave a Reply