SQL Server database stuck in RESTORING: find out why, then recover it without breaking log shipping or an AG

#Symptom

In SSMS the database shows (Restoring...) next to its name. Applications fail with:

Database 'Sales' cannot be opened. It is in the middle of a restore.

RESTORING is not damage. It means SQL Server is waiting for one more restore, or for someone to tell it the restore sequence is finished. The fix is usually one line, but run the wrong line on the wrong database and you break a log shipping chain or an availability group secondary. Find out why it is restoring first.

#Check 1: what state is it really in

SELECT name, state_desc, user_access_desc, is_in_standby,
       recovery_model_desc, create_date
FROM sys.databases
WHERE state_desc <> 'ONLINE' OR is_in_standby = 1;

RESTORING with is_in_standby = 0 is the case this post covers. If you see RECOVERING, RECOVERY_PENDING or SUSPECT, that is a different problem; don't use the commands below.

#Check 2: is a restore still running

A large restore can sit in RESTORING for hours. Don't interrupt it.

SELECT r.session_id, r.command, r.percent_complete,
       r.start_time,
       DATEADD(ms, r.estimated_completion_time, GETDATE()) AS est_finish,
       r.wait_type, s.login_name, s.host_name, s.program_name
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
WHERE r.command IN ('RESTORE DATABASE', 'RESTORE LOG', 'RESTORE HEADERONLY', 'DB STARTUP');

If a row comes back, the restore is in progress. percent_complete covers the data copy; after 100% the redo and undo phases still run. Follow those in the error log:

EXEC sys.xp_readerrorlog 0, 1, N'Sales';

If nothing is running, move on and find out who left it restoring.

#Check 3: who left it restoring

#Recent restore history

SELECT TOP (20) rh.destination_database_name, rh.restore_date,
       rh.restore_type,          -- D = full, I = differential, L = log
       rh.[recovery],            -- 1 = WITH RECOVERY, 0 = NORECOVERY
       rh.user_name, bs.backup_finish_date, bmf.physical_device_name
FROM msdb.dbo.restorehistory AS rh
JOIN msdb.dbo.backupset AS bs ON bs.backup_set_id = rh.backup_set_id
JOIN msdb.dbo.backupmediafamily AS bmf ON bmf.media_set_id = bs.media_set_id
WHERE rh.destination_database_name = N'Sales'
ORDER BY rh.restore_date DESC;

If the last row has recovery = 0, someone ran a restore WITH NORECOVERY and never finished the sequence.

#A tail-log backup on the source database

If the database is a source, not a restore target, check for a log backup taken WITH NORECOVERY. It happens when someone ticks "Take tail-log backup before restore" in the SSMS restore dialog, or runs:

BACKUP LOG Sales TO DISK = N'X:\tail.trn' WITH NORECOVERY;

That deliberately leaves the source in RESTORING. You can confirm it in msdb.dbo.backupset (look for a type L backup at the time the database went offline).

#Is it part of HA or DR

-- Availability group replica?
SELECT DB_NAME(database_id) AS db, is_local, is_primary_replica,
       synchronization_state_desc, database_state_desc
FROM sys.dm_hadr_database_replica_states
WHERE DB_NAME(database_id) = N'Sales';

-- Log shipping secondary?
SELECT secondary_database, restore_mode, last_restored_file, last_restored_date
FROM msdb.dbo.log_shipping_secondary_databases
WHERE secondary_database = N'Sales';

-- Database mirroring partner?
SELECT DB_NAME(database_id) AS db, mirroring_role_desc, mirroring_state_desc
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;

#Cause and fix at a glance

#Fix: finish the restore sequence

If you still have log backups that should be applied, apply them in order, all with NORECOVERY, and recover once at the end:

RESTORE LOG Sales FROM DISK = N'X:\Backups\Sales_0200.trn' WITH NORECOVERY, STATS = 10;
RESTORE LOG Sales FROM DISK = N'X:\Backups\Sales_0215.trn' WITH NORECOVERY, STATS = 10;
RESTORE DATABASE Sales WITH RECOVERY;

If there is nothing else to restore, recovery alone brings it online:

RESTORE DATABASE Sales WITH RECOVERY;

This command reads no backup file. It runs the undo phase and opens the database. On a large or busy database that can take a while.

Warning

WITH RECOVERY is one-way. After it runs you cannot restore any further log or differential backups into that database. If you are not sure whether more logs are coming, check with the person running the restore first, or use WITH STANDBY to look at the data read-only while keeping the sequence open.

To read the data without closing the sequence:

RESTORE LOG Sales FROM DISK = N'X:\Backups\Sales_0230.trn'
WITH STANDBY = N'X:\Backups\Sales_undo.dat';

If you get an error that a log backup is too recent to apply (error 4305), a log file is missing from the chain. Find it with RESTORE HEADERONLY and compare FirstLSN and LastLSN across files:

RESTORE HEADERONLY FROM DISK = N'X:\Backups\Sales_0215.trn';

#Fix: a log shipping secondary

A log shipping secondary in RESTORING is normal. Only recover it if you are failing over to it.

Caution

Recovering a log shipping secondary ends log shipping for that database. To return to the old setup you must re-seed the secondary from a new full backup.

For a planned role change, disable the copy and restore jobs first, apply the last logs, then recover:

EXEC msdb.dbo.sp_update_job @job_name = N'LSRestore_PRIMARY01_Sales', @enabled = 0;
EXEC msdb.dbo.sp_update_job @job_name = N'LSCopy_PRIMARY01_Sales',    @enabled = 0;
RESTORE DATABASE Sales WITH RECOVERY;

Job names vary by setup; check msdb.dbo.sysjobs for the LSCopy_ and LSRestore_ jobs on your server.

#Fix: an AG secondary

If the database is on a secondary replica and shows RESTORING, it was usually restored there but never joined to the group, or it fell out of the group. Join it rather than recovering it:

-- Run on the secondary replica
ALTER DATABASE Sales SET HADR AVAILABILITY GROUP = [AG_Sales];

If the join fails because the log chain has moved on, restore a newer log backup WITH NORECOVERY and try again. Only if you really want a standalone copy, remove it from the AG first with ALTER DATABASE Sales SET HADR OFF; and then run RESTORE DATABASE Sales WITH RECOVERY;. Don't do that on the primary replica.

#Fix: a mirroring partner

-- On the mirror server, only if you are ending mirroring
ALTER DATABASE Sales SET PARTNER OFF;
RESTORE DATABASE Sales WITH RECOVERY;

#Check many databases at once

On a server with dozens of databases, list every restoring database with its last restore in one pass, from PowerShell:

$query = @"
SELECT d.name, d.state_desc,
       (SELECT TOP (1) rh.restore_date FROM msdb.dbo.restorehistory rh
        WHERE rh.destination_database_name = d.name ORDER BY rh.restore_date DESC) AS last_restore
FROM sys.databases d WHERE d.state_desc = 'RESTORING';
"@
foreach ($server in 'SQL01','SQL02') {
    Invoke-Sqlcmd -ServerInstance $server -Query $query -TrustServerCertificate |
        Select-Object @{n='Server';e={$server}}, name, state_desc, last_restore
}

Invoke-Sqlcmd comes from the SqlServer PowerShell module; -TrustServerCertificate exists in version 22 and later.

#Prevent it next time

• Always end a restore script with an explicit RESTORE DATABASE ... WITH RECOVERY or a comment saying the database is meant to stay open for more logs.
• In the SSMS restore dialog, check the Options page before clicking OK. Untick the tail-log backup option unless you really mean to take the source offline.
• Name restore targets clearly (for example Sales_restore_20261103) so a half-finished copy is never confused with production.
• Add a daily check that alerts on any database in RESTORING that is not a known log shipping, AG or mirroring secondary. The first query in this post is enough.
• Log who ran the restore: restorehistory.user_name already holds it, so keep msdb history long enough to answer the question.