Database in SQL Server in recovery mode

There are many reasons to send your database in Recovery Mode. The most common cause is that to restart SQL Services forcefully due to long running data queries. Another common cause is that the database was restored with the NORECOVERY option from full, differential, and log backups but RECOVERY was not specified on the last restore.

Another common cause is that the transaction log filled due to a large data modification operation and SQL Server is rolling the transactions back to recover the database, which may take quite a bit of time. The error log will include recovery progress messages.

The best way to recover your database by using the below steps –

1. Stop SQL Server instance – just right on the SQL Server instance and click on Stop option

2. Move database files to another location/drive or rename database files including mdf and ldf files.

2. Start SQL Server Services – just go to the services and right click on the SQL Server Services and click on Restart.

3. Database again appears in recovery mode

4. Bring the database in offline mode - Once all connections have been removed you can then set the database to offline, using the following command (or right click on the database in SSMS and select ‘Take Offline’).

5. Delete the database – just right click on the database and click on delete option

6. Place the database files or rename database files back where it was

7. Attach the database – right click on the database and choose attach as given below –

8. Database is recovered and available. 

9. Checking in SQL error logs shows messages on dbcc checks & how many transactions were rolled back or forward during recovery.