Microsoft SQL Server Databases Stuck in Restoring State
Overview
The RESTORE ... WITH RECOVERY option puts the database into a usable state, so users can access a restored database.
This is likely caused by the restore script adding the WITH NO RECOVERY parameter to make the database ready for a transaction log apply after the restore. The database is now waiting for the latest transaction log file.
You can either:
- Apply the latest transaction log (Using
RESTORE LOG... WITH RECOVERY)... or - Restore the database again, but this time using
WITH RECOVERY... or - Force the database out of restoring mode by executing:
RESTORE DATABASE YourDb WITH RECOVERY
Before you do this, please make sure you understand the implications of these options. You may cause data loss if you are not careful.
Run below commands in SQL Query windows
Optional more Recovery commands:-
Restore full backup WITH RECOVERY
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH RECOVERY GO
Recover a database that is in the "restoring" state
RESTORE DATABASE AdventureWorks WITH RECOVERY
GO
Restore multiple backups using WITH RECOVERY for last backup
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
WITH RECOVERY
GO

Great stuff thanks for sharing ! The SQL Server Databases Stuck in Restoring State is beginning to clear up. As i found another helpful post for the same see here: https://madhivanan.wordpress.com/2016/09/06/issue-in-recovering-a-database-that-is-in-the-restoring-state-reference/
ReplyDeleteThanks your feedback and support ...Johnson!
ReplyDeleteOkay wow, this is so helpful. My teacher had been trying to explain me this, but I couldn't understand it, but now everything is within my comprehension. Thank you.
ReplyDelete