Database Stuck in Restoring: Fixing the Issues

Problem

My Microsoft SQL Server database is in a restoring state. How does this happen and how can I access my SQL Server database?

Solution

In this article, we will show reasons why a SQL Server database is in a restoring state and how you can get access to a database in a restoring state. It is not a very common problem, but when it happens it can be a big headache. In this article, we will see different reasons and possible solutions to solve this. These steps will work for any version of SQL Server.

Key Takeaways

A SQL Server database can be stuck in restoring due to various reasons, including incomplete restorations and issues with log shipping.
To access a database in restoring state, you often need to finalize the restoration with appropriate backup commands.
Using PowerShell’s Restore-SqlDatabase cmdlet can also help manage databases in restoring state effectively.
In case a database remains stuck after a server restart, check the Error Log and switch to single user mode for recovery commands.
Refer to sys.database_recovery_status to monitor the recovery process of a SQL Server database stuck in restoring.

Microsoft SQL Server database in RESTORING state after a restore

Usually, the restoring state happens when you are restoring a database. Here we will walk through an example of this.

I will create a full backup file (*.bak file) and transaction log backup file (*.bak file) by running this T-SQL code in SQL Server Management Studio (SSMS).

BACKUP DATABASE [earnings] TO DISK = N'c:\sql\earnings.bak' 
WITH NOFORMAT, NOINIT, NAME = N'earnings-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [earnings] TO DISK = N'C:\sql\earnings_LogBackup_2018-06-02_12-42-07.bak' 
WITH NOFORMAT, NOINIT, NAME = N'earnings_LogBackup_2018-06-02_12-42-07', SKIP, NOREWIND, NOUNLOAD, STATS = 10

Once we have the SQL Server backups, we will start the restoring process.

In order to restore the full and log backup we need to use the NORECOVERY option for the full restore. So, if we just restore the full backup as follows:

RESTORE DATABASE [earnings] 
FROM DISK = N'c:\sql\earnings.bak' WITH NORECOVERY, NOUNLOAD, STATS = 10

The database will now be in a restoring state. If we forget to restore additional backups, the database will be stuck in this mode.

To finalize the restore and access the database we need to issue the restore command for the log backup as follows:

RESTORE LOG [earnings]
FROM DISK = N'c:\sql\earnings_LogBackup_2018-06-02_12-42-07.bak'

SQL Server database in RESTORING state after doing backup log with NORECOVERY

Another reason your database can be in restoring state is when you backup the tail of the log using the NORECOVERY option as shown below.

BACKUP DATABASE [earnings] TO DISK = N'c:\sql\earnings.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'earnings-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [earnings] TO DISK = N'C:\sql\earnings_LogBackup_2018-06-02_12-42-07.bak' 
WITH NOFORMAT, NOINIT, NAME = N'earnings_LogBackup_2018-06-02_12-42-07', SKIP, NOREWIND, NOUNLOAD, NORECOVERY, STATS = 10

This will cause the database to change to a restoring state.

To fix this you can restore the database backups as shown above.

Make a SQL Server database in RESTORING state accessible without restoring backups

If the database is stuck in the restoring state and you don’t have additional backups to restore, you can recover the database using the following command:

RESTORE DATABASE [earnings] WITH RECOVERY

Once you issue this command, the database will be useable, but you won’t be able to restore any additional backups for this database without starting all over again with the full backup.

For more details about restoring a database in a restoring state, refer to this article Recovering a database that is in the restoring state.

SQL Server database in RESTORING state for Database Mirroring

Another reason your database is in a restoring state is that it is part of SQL Server Database Mirroring. Database Mirroring is a solution that allows you to have high availability for your database. If there is a database failure on the primary database, the secondary replica database on a different server will take over the database operations. The main database is the Principal Server, the secondary is the Mirror Server and optionally you can have another Mirror Server.

Here is an example. We can see on the left that the Principal server is where the database is accessible. On the right we can see the Mirror that is in a Restoring state.

For more information about Database Mirroring in SQL Server, refer to this link: Configure SQL Server Database Mirroring Using SSMS.

In Database Mirroring, the Mirror Server is in Restoring state until a Failover is done. To access a SQL Server database that is in a restoring state when it is part of Database Mirroring, you can do a manual or automatic failover from the Principal to the Mirror.

For an automatic failover, refer to the following link: Role Switching During a Database Mirroring Session (SQL Server).

To break the mirror, you will need to select the database and go to the mirroring page and select the remove mirroring button. Once removed, the mirroring database will return to the normal state and you can backup and restore the database as a normal database.

SQL Server database in RESTORING state for Log Shipping

SQL Server Log Shipping allows to you to backup the transaction logs and send and restore the backups on a different server in order to have replicas of the database in case the primary server fails.

Log Shipping puts the database in a Standby state with recovery or with no recovery. The no recovery mode will show the Log Shipping database in a Restoring state as shown below.

log shipping status in SSMS in restoring state

Here is a link to change the state to avoid the restoring state: Change the restore mode of a secondary SQL Server database in Log Shipping with SSMS.

SQL Server database stuck in RESTORING state after restarting the machine

Sometimes the database is in a restoring state after restarting the machine or for some other reason. It usually happens with big databases when a long transaction is in progress and an unexpected server shutdown or restart occurs.

If you have this problem, try this first:

RESTORE DATABASE [databasename] WITH RECOVERY

If you receive an error that the database is in use, try to set the user to single user mode:

USE master;
GO
 
ALTER DATABASE Database_name
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

Then try the restore with recovery command again.

Once restored, you can set to multiple user mode using the following T-SQL command:

USE master;
GO
 
ALTER DATABASE Database_name
SET MULTI_USER;
GO

Also, you will should review the Error Log and the Windows Event Viewer to check for errors. Refer to these links:

PowerShell to have the SQL Server in status

SQL Server can be run in PowerShell scripts. You can restore your database in PowerShell using the Restore-SqlDatabase cmdlet. By default, the SQL Server module is not installed by default. In order to install the SQL Server module, you may need to run the following command:

Install-Module -Name SqlServer

Once installed the module, you can run the restore-sqldatabase cmdlet to restore a transaction log with the NORECOVERY option as follows:

Restore-SqlDatabase -ServerInstance "myComputerInstance" -Database "Adventureworks2019" 
-BackupFile "c:\backup\adwlog.trn" -RestoreAction Log -NoRecovery

The command line will restore the database in a NoRecovery mode. Then the backup will be in restoring status.

When the SQL Server recovery will finish?

In a big database, restoring a database could take a long time. Is there a way to estimate the time that the restoration will take?

Fortunately yes. You can check this information in the error log. We created an exclusive tip for you related to this scenario:

Script to find out when SQL Server recovery will finish

Where can I see the recovery status?

There is a system view used to see the recovery status. The name is sys.database_recovery_status. The view will show information about the database id, the last log backup lsn (log sequence number) and the fork point lsn which is related to the current recovery fork.

Conclusion

In this article, we saw different reasons why a database could be in a Restoring state. Hopefully this will be helpful the next time you are troubleshooting this issue.

Frequently Asked Questions

How can I check the restore progress in SQL Server?

You can use the DMV sys.dm_exec_requests to check the status of the database. For more information about restoring percentages and progress, refer to this link:
Monitor Backup Percentage Complete in SQL Server

How to check if the status of the database is in a restoring state using T-SQL?

The following query can help you:

SELECT name, state_desc, recovery_model_desc
FROM sys.databases
WHERE name = ‘YourDatabase’
GO

How can I check if the SQL Server restore command is actively running?

The following query will help you to detect that:

SELECT r.session_id, r.command, r.percent_complete, r.estimated_completion_time
FROM sys.dm_exec_requests r
WHERE r.command IN (‘RESTORE DATABASE’, ‘RESTORE LOG’, ‘BACKUP DATABASE’);

How can I detect who ran a SQL Server restore command?

The following query can help you to detect who ran the restore command:

DECLARE @TraceFile nvarchar(260);

SELECT @TraceFile = CONVERT(nvarchar(260), value)
FROM sys.fn_trace_getinfo(DEFAULT)
WHERE property = 2;

SELECT TOP (100)
    StartTime,
    LoginName,
    HostName,
    ApplicationName,
    DatabaseName,
    TextData
FROM sys.fn_trace_gettable(@TraceFile, DEFAULT)
WHERE EventClass = 115
AND DatabaseName = ‘yourdatabase’
ORDER BY StartTime DESC;

How can I get the RESTORE information from the SQL Server Error Log?

If you want to monitor the backup information from the SQL Server error log, you can use the following extended stored procedure:

EXEC xp_readerrorlog 0, 1, N’RESTORE’;

If you want to query this information, you will need to store the extended procedure results in a temporary table. The following example shows how to store the results of the xp_readerrrorlog into a table named #ErrorLog.

CREATE TABLE #ErrorLog
(
    LogDate     DATETIME,
    ProcessInfo NVARCHAR(50),
    [Text]      NVARCHAR(MAX)
);

INSERT INTO #ErrorLog
EXEC xp_readerrorlog
    0,
    1,
    N’RESTORE’;

SELECT * FROM #ErrorLog;

I use Veeam/Commvault/Redgate, why do my SQL Server databases get stuck in a restoring state?

These backup programs use a Virtual Device Interface. If the application crashes or if you lose the network connection, the backup will fail abruptly. To fix this problem, check if the restore command is still running using this query:

SELECT
session_id,
command,
percent_complete,
estimated_completion_time / 60000.0 AS EstimatedMinutesRemaining,
status
FROM sys.dm_exec_requests
WHERE command IN (‘RESTORE DATABASE’, ‘ROLLBACK’, ‘RECOVERY’);

If you see that the command is still running, you can find and kill the process.

SELECT
spid,
program_name,
status,
cmd
FROM sys.sysprocesses
WHERE dbid = DB_ID(‘YourDatabase’)
OR program_name LIKE ‘%Veeam%’
OR program_name LIKE ‘%Commvault%’
GO

And kill the process:

KILL spid

If you cannot restore your database manually, drop it and try to restore it again.

ALTER DATABASE [YourDatabase]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [YourDatabase]
GO

I tried to drop my SQL Server database in restoring mode, but it fails what can I do?

To kill other sessions using the database set your database to single user mode with this code:

ALTER DATABASE [YourDatabase]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Then drop the database with this code:

DROP DATABASE [YourDatabase]
GO

Next Steps

If you have more questions, feel free to ask in the comments section below.

For more information refer to these links:

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.

MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018

I usually don’t comment, but this time I have to:
first of all, how can you tell if the database is still trying to recover or not? https://www.mssqltips.com/sqlservertip/2343/how-to-monitor-backup-and-restore-progress-in-sql-server/.
Once you are past that, please check your SQL Server Edition: Enterprise edition skips the rollback step (roll forward, roll back, bring online), which means the database will be online much faster as compared to Standard Edition (in my case it was around 2 hours for one database).
Lastly, we hit a nasty bug (twice already) with database mirroring, in which it would not come online (WITH RECOVERY) for the love of it. Microsoft has not yet been able to tell us why.
Hope this helps.

10 Comments

Richard Peninger
January 22, 2025 / 12:32 pm Reply
One thing I don’t understand is why are you talking about backing up the data file and the log file separately. When you run a SQL Server Full Backup (Not copy_only) the active log records are updated to the data file. That’s why copy_only was designed, to provide space saving for backups used in test server, and maybe other reasons I don’t know about.
Raj
October 25, 2024 / 2:33 pm Reply
thanks. RESTORE DATABASE cmd worked
mario osorio
July 26, 2024 / 3:50 pm Reply
You’re simply the best
rashmi ashokan
May 23, 2023 / 1:58 pm Reply
We see database in restoring state while restore triggered from commvault.There is no specific error from commvault , do we have any cummulative updates for 2019 servers
Isidro
January 23, 2023 / 10:37 am Reply
Muchas Gracias
jy
December 20, 2022 / 3:48 am Reply
Thank you so much
Ashok Rao
September 24, 2021 / 6:33 am Reply
Is there an option to “Restore sql transaction log backups using the Powershell script”?
Calin
August 2, 2021 / 4:12 pm Reply
I usually don’t comment, but this time I have to:
first of all, how can you tell if the database is still trying to recover or not? https://www.mssqltips.com/sqlservertip/2343/how-to-monitor-backup-and-restore-progress-in-sql-server/.
Once you are past that, please check your SQL Server Edition: Enterprise edition skips the rollback step (roll forward, roll back, bring online), which means the database will be online much faster as compared to Standard Edition (in my case it was around 2 hours for one database).
Lastly, we hit a nasty bug (twice already) with database mirroring, in which it would not come online (WITH RECOVERY) for the love of it. Microsoft has not yet been able to tell us why.
Hope this helps.
Koos Van Niekerk
May 24, 2021 / 9:36 am Reply
It is all good and well when you get to 100% and it is stuck there, however, what do you do if it stops at 90% time after time you try and restore it?
vac
May 19, 2021 / 6:55 am Reply
For me it is like this. During the test database restoring, there was not enough space and it entered the restoring mode. None of the commands help. I get the message “ALTER DATABASE is not permitted while a database is in the Restoring state. Msg 5069, Level 16, State 1, Line 6 ALTER DATABASE statement failed.” The log file has 48GB and there is still not enough space. How to stop restoring without switching off the server?

SQL Server Database Stuck in Restoring State