By Alison / Last Updated December 20, 2021

Case: SQL Server: Database Stuck in "Restoring" State

I backed up a database:

BACKUP DATABASE MyDatabase
TO DISK = 'MyDatabase.bak'
WITH INIT --overwrite existing

And then tried to restore it:

RESTORE DATABASE MyDatabase
   FROM DISK = 'MyDatabase.bak'
   WITH REPLACE --force restore over specified database

And now the database is stuck in the restoring state.

——stackoverflow.com

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

Restore Failed Database in Use

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 Server Database Stuck in Restoring

If you find your SQL database stuck in restoring state and thus not accessible, there are many reasons that could lead to this result.

For example, you didn’t stop restore process correctly, you don’t have enough disk space, others are using this database, or users didn’t close Query window after use, etc. Among them, the most common reason is that you wrongly used No Recovery option to restore database.

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. You need to use With Recovery to finish the restore.

Besides, if you find others are using this database, or left Query window unclosed, you can close all existing connections to destination database.

Here I summarized 5 ways to fix SQL Server database stuck in restoring.

Solution 1. Add WITH RECOVERY to Restore Statements

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.

Restore Database with Recovery

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…

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.

Restore with Recovery

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…

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.

Uncheck Transaction Log

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…

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.

Close Existing Connections

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

Single User Mode

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 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 database without error, I recommend you a professional software AOMEI Centralized Backupper Database. It’s easy to operate, and ensures data security.

Besides, as an enterprise endpoints backup software, it allows one single specialist to create & manage backups for all Windows PCs, workstations and servers within LAN, which can definitely reduce many manual errors while increasing efficiency. 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.

Here’s a 30-day free trial for you:

Download Free Trial Win PCs & Servers
Secure Download

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.

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.

Request Control

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.

SQL Server Backup

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

Add Computers

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

Select Database

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.

Select Path

Once created, tasks will show on Tasks page separately.

Start Backup

How to Safely Restore SQL Database

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

Restore Database

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

Select Database

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

Select Specific Backup

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

Restore to Original Location

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

Restore to New Location

Summary

In this article I introduced 5 proved ways to solve SQL Server database 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.