You can check SQL Server error log to know the cause behind the database stuck in RECOVERY mode issue. Common causes behind the issue includes:<\/p>
- \n
- You might be restoring the database from full, differential, and log backups with the NORECOVERY option enabled, but RECOVERY is not specified during the last restore.<\/li>\n\n\n\n
- As per MSDN, transaction log can fill when a database is in recovery. SQL Server stops all the running transactions until the log storage is freed, slowing down the database recovery. Several Virtual Log Files (VLFs) in a transaction log can also slow down the recovery process.<\/li>\n\n\n\n
- The recovery process might stop if your database is damaged or have some sort of corruption.<\/li>\n<\/ul>
Troubleshooting the ‘SQL Server Database in Recovery Mode’ Issue<\/strong><\/h2>
Following are some simple troubleshooting tips to resolve the SQL Server database in recovery mode issue:<\/p>
Tip 1 – Restore Database with RECOVERY<\/strong><\/h3>
\n
Note:<\/strong> Running ‘RESTORE with Recovery’ will make the database go through the same recovery steps again. Avoid this option if you have a large database.<\/em><\/p>\n<\/blockquote>
Execute the following query to bring your database back online:<\/p>
RESTORE DATABASE db_name WITH RECOVERY;<\/code><\/pre>The query will rollback any uncommitted transactions and bring the database out of recovery mode.<\/p>
Tip 2 – Apply Microsoft Fixes<\/strong><\/h3>
If you’ve too many VLFs inside the transaction log, resulting in slow database recovery, applying Microsoft fixes<\/a> may help.<\/p>
Tip 3 – Run DBCC CHECKDB to Determine Database Corruption<\/strong><\/h3>
Run DBCC CHECKDB<\/a> on the problematic database. If it reports consistency errors, you must repair the database using the minimum level of repair option. For more information, read this: Read More<\/a><\/p>
If these troubleshooting tips fail, try resolving the issue using the solutions discussed in the next section.<\/p>
Solutions to Fix the SQL Server Database in Recovery Mode Issue<\/strong><\/h2>
Following are the two solutions you can use to fix the issue:<\/p>
Solution 1 – Restore Database from Most Recent Backup<\/strong><\/h3>
\n
Note:<\/strong> Skip to the next solution if the backup is obsolete or corrupt.<\/em><\/p>\n<\/blockquote>
If database recovery process seems stuck for ages, try restoring the database from an updated backup copy. To do so, follow these steps:<\/p>
- \n
- Open SQL Server Management Studio (SSMS), right-click on Databases<\/strong>, and click Restore Database<\/strong>.<\/li>\n<\/ul>\n
![\"restore-sql-db\"](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2021\/09\/restore-sql-db-img-1.png\")
<\/figure>\n<\/div>- \n
- Under the Source section, select the Device <\/strong>option and then click the button next to it.<\/li>\n<\/ul>\n
![\"select-source-to-restore-db-from\"](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2021\/09\/select-source-to-restore-db-from-img-2.png\")
<\/figure>\n<\/div>- \n
- In the dialog box that opens, press Add<\/strong>.<\/li>\n<\/ul>\n
![\"select-backup-media-for-db-restore\"](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2021\/09\/select-backup-media-for-db-restore-img-3.png\")
<\/figure>\n<\/div>- \n
- Locate and select the backup file (.bak) you want to restore, then press OK<\/strong>.<\/li>\n<\/ul>\n
![\"select-db-backup-img\"](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2021\/09\/select-db-backup-img-4.png\")
<\/figure>\n<\/div>- \n
- Click OK<\/strong> again.<\/li>\n<\/ul>\n
![\"proceed-with-db-restore\"](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2021\/09\/proceed-with-db-restore-img-5.png\")
<\/figure>\n<\/div>- \n
- In the Restore Database<\/strong> screen, enter name of the database you want to restore and click OK<\/strong>.<\/li>\n<\/ul>\n
![\"specify-db-name\"](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2021\/09\/specify-db-name-img-6.png\")
<\/figure>\n<\/div>- \n
- The database will be restored.<\/li>\n<\/ul>\n
![\"database-restored-message\"](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2021\/09\/database-restored-message-img-7.png\")
<\/figure>\n<\/div>\n
Note:<\/strong> Restoring a large database containing multiple tables can take a lot of time.<\/em><\/p>\n<\/blockquote>
Solution 2 – Use a Professional SQL Database Recovery Tool<\/strong><\/h3>
If backup is not available or you want to quickly restore the database, using a SQL recovery tool<\/a>, such as Stellar Repair for MS SQL may help. The software repairs the .mdf\/.ndf files and restores the database to its original form.<\/p>
Check out this video to understand how the software works:<\/p>
- The database will be restored.<\/li>\n<\/ul>
- In the Restore Database<\/strong> screen, enter name of the database you want to restore and click OK<\/strong>.<\/li>\n<\/ul>
- Click OK<\/strong> again.<\/li>\n<\/ul>
- Locate and select the backup file (.bak) you want to restore, then press OK<\/strong>.<\/li>\n<\/ul>
- In the dialog box that opens, press Add<\/strong>.<\/li>\n<\/ul>
- Under the Source section, select the Device <\/strong>option and then click the button next to it.<\/li>\n<\/ul>
- Open SQL Server Management Studio (SSMS), right-click on Databases<\/strong>, and click Restore Database<\/strong>.<\/li>\n<\/ul>