How to Troubleshoot and Fix SQL Database Stuck in Restoring State?

You may face a situation, while restoring a MS SQL database (.bak file), where the database gets stuck in the restoring state.

This situation usually occurs when the restoration process hasn’t been completed or if there is some issue with the restore process. Due to this, your database becomes inaccessible. In this article, we will discuss how to troubleshoot the SQL database stuck in the restoring state issue.

What causes your SQL Database to get Stuck in the Restoring State?

It is important to first understand the causes behind the SQL database stuck in the restoring state issue. This issue may arise,

If you’ve selected the NORECOVERY option while restoring a full backup.
If there is corruption in the backup file.
If there are issues with the RESTORE command (when using the T-SQL statements).
If the backup restoration process has failed unexpectedly.

Methods to Troubleshoot and Fix SQL Database Stuck in Restoring State Issue

If your database is stuck in the restoring state, then you can first check and make sure the syntax of RESTORE command that you are using to restore backup (if using T-SQL) is correct. If this is not the case, then follow the below methods.

Method 1: Restore Database by using WITH RECOVERY Option

Your SQL database can get stuck in the restoring state if you've selected the NORECOVERY option when restoring multiple backup files. The NORECOVERY option keeps the state of database in 'recovery' to allow restoring of additional backups and prevent users from accessing the database. You can change the state of database from RESTORING to Online by restoring the database with the ‘WITH RECOVERY’ option. To do this, follow the below steps:

Open SQL Server Management Studio (SSMS) and connect to the server instance.
Go to the Object Explorer and right-click on the database node.
Select Tasks and click Restore > Database.

In the Restore Database window, click on Source to select the Full Backup file that you want to restore and then select the destination. Click OK.

You can see all the details about the selected backup file under the Backup sets to restore section.
Now, click on Options under Select a page.
On the Options page, click RESTORE WITH NORECOVERY in the Recovery state field and then click OK.
Next, follow the same procedure to restore other backups (apart from the final backup file) using the WITH NORECOVERY option.
After all the backups have been restored, use the WITH RECOVERY option to restore the last backup file. To do this, from the Options page, click RESTORE WITH RECOVERY and then click OK.

Method 2: Drop and Restore the SQL Database

If the database is stuck in the restoring state due to incomplete restore operation, you can try to drop the database and then restore it from a valid backup. It is recommended that you perform this operation if you have all the backups. Here are the steps to drop and restore the SQL database:

In the Object Explorer, connect to an instance of the SQL Server Database Engine and then expand it.
Now, right-click on the database which is stuck in restoring state and click the Delete option.
A confirmation window is displayed. Click Yes. This will delete the database.

Now, you can restore the same database from the backup. For this, you can use the below command:

USE [master]

RESTORE DATABASE [bank121] FROM DISK = N'D:\Internal\BackupfileRD.BAK'

WITH FILE = 1, MOVE N'bank121' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\bank121.mdf',

MOVE N'bank121_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\bank121_log.ldf',

NOUNLOAD, STATS = 5

GO

Method 3: Check the Backup File Integrity

If the database is still in the restoring state and is not accessible after the restore, there is a chance that the backup file is corrupted or damaged. You can check the integrity of backup file by using the RESTORE VERIFYONLY statement (see the below example).

RESTORE VERIFYONLY FROM DISK = 'D:\AdventureWorks.bak';

If the backup file is corrupted, then you can repair the .bak file using a professional SQL database recovery tool. Stellar Repair for MS SQL Technician is one such tool that can restore data from corrupt backup file and save it in a new database file. It supports repairing of corrupt backups of all types, including Full, Differential, and Transaction Log. Also, it can repair and recover SQL database files with complete integrity.

Conclusion

The article explains that a SQL database getting stuck in the Restoring state is a common issue that usually occurs due to incomplete restore operations, incorrect use of the NORECOVERY option, corruption in the backup file, or problems with the RESTORE command.