Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

SQL Server database stuck in 'Restoring...': finish the restore or recover it safely

Technical article
Backup & Recovery

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

A database in the RESTORING state is unavailable by design. A common cause is a restore that ran with NORECOVERY where nobody ran the final step, but the same state is also normal for a log shipping or availability group copy that should not be touched. Before you run RESTORE DATABASE ... WITH RECOVERY, find out whether a restore is still running, whether more backups are meant to be applied, and whether this database is a standby copy. Recovery cannot be undone, and after it no more log backups can be applied.

What the error means

Microsoft's database states page (metadata date 2025-10-20) defines RESTORING as: "One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable." The state is visible in state_desc of sys.databases. The sys.databases page (metadata date 2026-04-27) lists state value 1 as RESTORING. It also notes that for Always On databases you should query database_state in sys.dm_hadr_database_replica_states rather than rely on sys.databases.

Recovery is the step that ends a restore sequence. The restore and recovery overview (metadata date 2024-12-04) says the recovery process's undo phase rolls back incomplete transactions, "After rollback, the database goes online, and no more transaction log backups can be applied to the database." The RESTORE arguments page (metadata date 2022-05-10) states that RECOVERY rolls back uncommitted transactions and makes the database ready for use. NORECOVERY does not roll back, and "the database is not usable". Microsoft's best practice is to use NORECOVERY on all steps of a multi-step restore until the desired point is reached, and then to run a separate RESTORE ... WITH RECOVERY.

STANDBY is a third mode. It leaves the database available read-only between log restores, and keeps the undo actions in a standby file so the recovery effects can be reverted. Per the same page, if that standby file is deleted manually before the next restore, the entire database must be re-restored.

Two states are easy to confuse. RECOVERING is "a transient state": the database goes online if recovery succeeds. RESTORING does not clear itself. It waits for your next restore.

Likely causes

Cause What you see How to confirm
A restore is still running RESTORING, and a request with command RESTORE DATABASE and a rising percent_complete sys.dm_exec_requests (first check)
Last step was RESTORE ... WITH NORECOVERY and more backups were meant to follow RESTORING; msdb shows recovery = 0 on the last restore row msdb.dbo.restorehistory
Ticking "Take tail-log backup before restore" in SSMS while restoring under a new name The existing database, not the new one, is RESTORING SSMS page, Example C: leaving it ticked "will result in the existing database ... to change to the restoring state"
Tail-log backup taken with NORECOVERY Source database RESTORING right after a log backup The BACKUP page: NORECOVERY "backs up the tail of the log and leaves the database in the RESTORING state"
Log shipping secondary RESTORING (or standby) on the secondary, with log shipping jobs present Log shipping jobs; the log shipping page
Availability group secondary database replica_id is not NULL in sys.databases sys.databases and sys.dm_hadr_database_replica_states
Interrupted restore No running request, restorehistory shows partial or missing final step restorehistory, job and error logs

Prove the cause before changing anything

These queries use column names from the Microsoft pages cited. They have not been run or lab-tested and are read-only.

  1. Is a restore actually running? A database in RESTORING during a live restore is normal. The sys.dm_exec_requests page (metadata date 2026-11-02) says percent_complete is reported for RESTORE DATABASE and RECOVERY, among other commands. On SQL Server 2019 and earlier you need VIEW SERVER STATE to see other sessions, and on 2022 and later VIEW SERVER PERFORMANCE STATE.
SELECT session_id, command, status, percent_complete, start_time
FROM sys.dm_exec_requests
WHERE command LIKE N'RESTORE%' OR command LIKE N'%RECOVERY%';

If a row appears, wait for it to finish.

  1. What state and mode is the database in?
SELECT name, state_desc, user_access_desc, is_in_standby,
       recovery_model_desc, replica_id
FROM sys.databases
WHERE name = N'Sales';

is_in_standby means "database is read-only for restore log". A non-NULL replica_id means the database is part of an availability group, per the page.

  1. What was the last restore? The restorehistory table (metadata date 2017-03-03) has one row per restore operation in msdb:
SELECT TOP (10) restore_history_id, restore_date, destination_database_name,
       user_name, restore_type, recovery, restart, stop_at
FROM msdb.dbo.restorehistory
WHERE destination_database_name = N'Sales'
ORDER BY restore_history_id DESC;

restore_type is D (database), F (file), G (filegroup), I (differential), L (log) or V (verify only). recovery is 1 for RECOVERY and 0 for NORECOVERY. The history exists only on the instance that ran the restore, and rows are removed by sp_delete_backuphistory. An empty result does not prove nothing happened.

  1. Which backups do you still hold, and in what order? Use RESTORE HEADERONLY (metadata date 2023-05-10) on each file. Compare BackupType, FirstLSN, LastLSN and DatabaseBackupLSN, and BackupFinishDate. You need to know whether log backups newer than the last restored step exist before you recover.

  2. Is this a deliberate standby? Check for log shipping jobs. The log shipping page (metadata date 2026-02-23) says a secondary database "might be in either the RECOVERING state or the STANDBY state", and that log shipping does not fail over automatically. The states page above defines RECOVERING as transient, so the two pages describe the secondary differently. Judge by the jobs and by state_desc.

Fix

Choose by what you found. Do not run recovery just to make the warning go away.

A restore is running. Do nothing until percent_complete reaches 100 and the request ends.

The sequence is unfinished and you hold the remaining backups. Continue the chain. The RESTORE page shape, using a disk device, is:

RESTORE LOG Sales
FROM DISK = N'\\FS01\SQLBackups\Sales_log_1300.trn'
WITH NORECOVERY;
-- repeat for each remaining log backup, in order
RESTORE DATABASE Sales WITH RECOVERY;

Use STOPAT on the last log restore if you need a point in time. The final line is the recovery-only restore described in Microsoft's recovery-only page (metadata date 2019-12-17): no FROM clause, because no backup is needed. Recovering the database "when restoring the last backup in a restore sequence" is its stated use. If the backup came from an older version of SQL Server, the upgrade runs at this step and cannot be undone (RESTORE arguments page).

No more backups exist, and the database state is what you want. Run only the recovery-only restore:

RESTORE DATABASE Sales WITH RECOVERY;

This is irreversible for the chain. After it, no further log backups can be applied. If you recover too early, for example before the last log backups were restored, you cannot add them on top. The remedy is to restore again from the full backup, which can take as long as the first restore. Consider whether any log backups, or a tail-log backup of the source, still exist before you commit.

The database was left in RESTORING by ticking the tail-log option in SSMS. The SSMS restore page shows this mistake on an existing database. A recovery-only restore brings the database back, but check first that no other restore is planned for it and that nothing was overwritten.

It is a log shipping or availability group database. Leave it alone unless you intend to fail over. Microsoft says a log shipping secondary can be brought online manually if the primary becomes unavailable. Doing that on a healthy secondary breaks the configuration and is a role change, not a repair. For availability groups, work through the availability group's own tooling and the replica states; availability group procedures are outside this article.

A standby file was deleted. The RESTORE arguments page says that if the standby file is removed manually before the next restore, the entire database must be re-restored. Do not delete standby files as housekeeping.

Example (illustrative). On SQL01, Sales was restored from a full backup WITH NORECOVERY on Friday. The weekend job that was meant to apply the logs never ran. On Monday, sys.databases shows RESTORING, no restore request is running, and restorehistory shows one row with restore_type D and recovery 0. The administrator finds six log backups through Sunday 23:00 on the file share, applies them with NORECOVERY, and finishes with RESTORE DATABASE Sales WITH RECOVERY.

Confirm the fix

  • sys.databases shows state_desc ONLINE and user_access_desc MULTI_USER.
  • restorehistory shows a final row with recovery = 1 and, if you used it, the expected stop_at.
  • The application owner checks the newest data, not only that the database opens. The last transaction should match the recovery point you chose.
  • If logins and users were involved, check them on the new server, as that is a separate follow-up.
  • Keep every backup file until the application owner signs off. Running recovery does not change the backup files.

What to watch afterwards

  • Alert on any database in RESTORING that is not on a known list of log shipping or availability group copies. The state does not raise its own error.
  • End every restore script with an explicit RESTORE ... WITH RECOVERY step, or a clear comment that the database is meant to stay in restoring state.
  • Keep a list of intended standbys, so nobody recovers one by mistake.
  • If msdb history is pruned, record restores elsewhere, because sp_delete_backuphistory removes the restorehistory rows.

Risks and limits

  • These commands have not been run or lab-tested. The syntax shapes follow Microsoft's pages.
  • Recovery is not reversible for the log chain. The repair for a premature recovery is a repeat of the whole restore.
  • The log shipping page uses "RECOVERING" for a secondary state, while the states page defines RECOVERING as transient. Microsoft does not reconcile the two, so judge by the jobs and state_desc.
  • Availability group statements are limited to what the sys.databases page says about replica_id and the replica states view. Microsoft does not document the behaviour of an availability group secondary in RESTORING on those pages, so confirm it before acting.
  • percent_complete for a restore describes work done, not a guarantee the restore will succeed.

Sources