5 Proven Ways to Fix SQL Server Database Stuck in Restoring

Case: SQL Server Database Stuck in "Restoring" State

Have you also found your SQL database stuck in restoring? With this annoying “restoring” sign attached behind your database, you cannot operate it anymore, and sometimes receive a “Exclusive access could not be obtained because the database is in use” alert.

Here I will list the reasons that may cause your SQL Server database in restoring state, and provide you 5 proved ways to solve this problem.

Reasons Why SQL Database Stuck in Restoring

Before troubleshooting, some users may wonder "How long does it normally take to recover a database".

This question is not really definitive as it involves many variables such as the size of your database, the hardware of your computer, whether you are recovering from a local disk, an external device or a network location. 

Anyway, when you find your SQL database stuck in restoring state and thus not accessible, you can consider some common causes:

✎For example:

You didn’t stop restore process correctly

Disk space not enough

Others are using this database

Users didn’t close Query window after use

You wrongly used "No Recovery" option to restore database (most common)

5 solutions to fix SQL Server database stuck in restoring

Here I summarized 5 ways to fix SQL Server 2014, 2016, 2019, etc. database stuck in restoring. You can try them one by one or directly turn to an efficient SQL Server backup solution.

*Before trying the following methods, please check whether the stuck database has a Query window that you forgot to close, and if so, close it first. This may solve the problem directly.

Solution 1. Add WITH RECOVERY to Restore Statements

Normally SQL Server database restoring uses With Recovery by default, and the No Recovery option is used when you want to restore more databases or logs together - it’s telling SQL Server to wait for more operations. Therefore, you need to use With Recovery to finish the restore:

1. Launch SSMS and connect to your instance, click New Query on the upper bar. And then input following statements in the blank:

RESTORE DATABASE databasename WITH RECOVERY

Note: if you want to overwrite this database without backing up tail of transaction log first when restoring, you can add REPLACE before RECOVERY. But be careful, it may cause data loss. You can do this when you have a full backup.

2. Then click Execute. When you receive the success result, Refresh the database list and you can see the database is out of restoring.

Solution 2. Select RESTORE WITH RECOVERY in Options

1. Launch SSMS and connect to your instance, right-click the SQL Server database in restoring state, select Tasks > Restore > Database…

2. Turn to Options page in the right tab, select RESTORE WITH RECOVERY in Recovery state section.

3. Then click OK. When it’s successfully restored, right-click Databases to Refresh the list, and you can see the database is out of restoring state.

Solution 3. Uncheck Transaction Log in SSMS Restore

1. Launch SSMS and connect to your instance, right-click the database which stuck in restoring, select Tasks > Restore > Transaction Log…

2. In the prompt window, General page, uncheck all log backups in Select the transaction log backups to restore section, then click OK.

3. Refresh your database list, and you can see that your database is back to normal.

Solution 4. Close Existing Connections to Destination Database

1. Launch SSMS and connect to your instance, right-click the database which stuck in restoring, select Tasks > Restore > Database…

2. Turn to Options page, tick the option Close existing connections of destination database. You will see a notice below telling you that This may leave the destination database in single-user mode.

3. Then click OK, and the database-in-use error won’t happen again.

Solution 5. Switch SQL Server into Single User Mode to Close Connections

1. Launch SSMS and connect to your instance, click New Query on the upper bar, and input following statements:

USE master;
GO
ALTER DATABASE databasename
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE databasename
SET MULTI_USER;
GO

2. Then click Execute, and you will close existing connections before restore.

Note: If you have a full back up, you can also delete the database, then restore database from bak file directly.

One Safer Way to Backup and Restore SQL Server Database

I found that for most times, SQL database stuck in restoring is because users wrongly used No Recovery in script. Except for this problem, incorrectly using scripts could also lead to many other errors, even data loss.

Therefore, if you are not familiar with T-SQL, or you want to backup and restore SQL database with less error, I recommend you a professional enterprise endpoints backup software AOMEI Centralized Backupper Database.

✤Advantages:

It has an easy-to-use GUI that enables you to operate without any script.

It allows one single specialist to create & manage backups for all Windows PCs and servers within LAN, which can reduce manual errors while increasing efficiency.

You can simply back up or restore multiple databases or even instances at once.

On Logs page you can check the details of these operations, on Monitor page you can also check their Network, CPU, Memory, and Disk conditions.

Besides SQL databases, it also supports files, partitions, disk and OS backups.

You can download the 30-day free trial below and get a up-to 60% off discount:

To properly use AOMEI Centralized Backupper Database, please follow these steps:

☛ Launch Centralized Backupper Database, and install Agent remotely or manually by clicking Computers > Install Client Program.

☛ For the computers with client programs installed manually, you still need to Request Control over them for the further operation.

Click Computers > Uncontrolled Computers to find the correct IP, then click Request Control on the upper bar. A prompt window will show on their computers, once all permission received you can start whenever you like.

How to Backup and Restore SQL Database

Click Tasks on the upper bar, click New Task and select SQL Server Backup to create a new backup task. Follow the 3 steps in the prompt window.

Step 1, click Add Computers to find the correct IP among all the controlled computers with SQL databases, click OK.

Step 2, click Add and you can select multiple instances and databases you want to backup.

Step 3, select a location as the destination path, you can click Add Storage in the prompt window to add a Share or NAS Device as a storage end. Then click Start Backup to start the task.

Once created, tasks will show on Tasks page separately.

✦ How to Safely Restore SQL Database

Step 1, click Advanced on the upper right of SQL Server Backup task, and select Restore.

Step 2, select the computer and databases you want to restore in prompt window.

You can also choose one specific backup (full/differential) at the upper right corner. Click Next.

Step 3, choose to Restore to original location or Restore to a new location.

If you select the latter, you can restore the backup to another instance on same or another server within LAN. Then clic Start Restore.

Summary

In this article I introduced 5 proved ways to solve SQL database stuck in restoring state. If you encountered other errors, you can view this article to find a solution: SQL Server Backup Failed | Solutions and Alternative

However, if you want an easier and safer way to backup and restore databases without error, I recommend you to use AOMEI Centralized Backupper Database Edition.

It has a simple and intuitive interface, for you to centrally operate and manage backups and restores of multiple Windows PCs, workstations and servers within LAN. You can also check the logs to see the details of these operations.

Besides, it centralized functions like File Backup, System Backup, and Disk Backup, Partition Backup, Basic Sync, Real-Time Sync, and Mirror Sync, which can save you plenty time and thus promote efficiency.