SQL Server Restore: Fixing Databases Stuck in RESTORING Mode

The Problem: Database Stuck in RESTORING Mode

You run a RESTORE command, and the database won't go online:

SELECT name, state_desc FROM sys.databases WHERE name = 'MyDatabase';

-- Output:
-- name          state_desc
-- MyDatabase    RESTORING

You can't query it, you can't modify it, you can't even see what's wrong. The database is frozen in an intermediate state, waiting for something.

This happens because SQL Server's restore process has strict rules: if you interrupt the sequence or forget a step, the database locks itself until you complete it.

Understanding the Restore Sequence

Full Backup → Transaction Log Backups

A proper restore chain looks like this:

Backup Timeline:
Full backup at 00:00
Log backup at 06:00
Log backup at 12:00
Log backup at 18:00
Log backup at 23:59 (database crashes)

To restore the database to 18:30 (before the crash), you must:

Restore the full backup (RESTORE DATABASE ... FROM DISK = ...)
Restore each log backup in order (RESTORE LOG ... FROM DISK = ...)
Restore the final log with RECOVERY (or RESTORE LOG ... WITH RECOVERY)

If you skip a step or restore logs out of order, SQL Server leaves the database in RESTORING mode waiting for the next log file.

Why Databases Get Stuck

Reason 1: Missing Transaction Log Backups

You restore the full backup but forget one of the intermediate log backups:

-- Restore full backup
RESTORE DATABASE MyDatabase
FROM DISK = 'C:\Backups\MyDatabase_Full_20240115.bak'
WITH NORECOVERY;  -- ← Important: NORECOVERY

-- Restore log from 06:00
RESTORE LOG MyDatabase
FROM DISK = 'C:\Backups\MyDatabase_Log_20240115_06.trn'
WITH NORECOVERY;

-- Restore log from 12:00 - SKIPPED BY MISTAKE

-- Restore log from 18:00
RESTORE LOG MyDatabase
FROM DISK = 'C:\Backups\MyDatabase_Log_20240115_18.trn'
WITH NORECOVERY;
-- Error: "The backup file contains a log backup from a time after the last full or differential backup was restored"
-- Database is now RESTORING

SQL Server detects the gap and refuses to proceed. Database is stuck waiting for the missing log backup.

Reason 2: Forgetting RECOVERY on the Final Log

You restore all backups but forget RECOVERY on the last one:

RESTORE LOG MyDatabase FROM DISK = '...' WITH NORECOVERY;
RESTORE LOG MyDatabase FROM DISK = '...' WITH NORECOVERY;
RESTORE LOG MyDatabase FROM DISK = '...' WITH NORECOVERY;
-- Oops! Forgot WITH RECOVERY on the final restore
-- Database is stuck in RESTORING mode

The database is in RESTORING state indefinitely because SQL Server thinks more log backups are coming.

Reason 3: Wrong Restore Sequence

You restore log backups out of order:

RESTORE DATABASE MyDatabase FROM DISK = 'Full.bak' WITH NORECOVERY;
RESTORE LOG MyDatabase FROM DISK = 'Log_20240115_18.trn' WITH NORECOVERY;  -- ← Wrong! This is 18:00
RESTORE LOG MyDatabase FROM DISK = 'Log_20240115_06.trn' WITH NORECOVERY;  -- ← This is 06:00 (earlier!)
-- Error: Log backup is before current point in time
-- Database is RESTORING

Reason 4: Differential Backup After Full, Before Logs

You restore the full backup, then a differential, but forget the logs between them:

RESTORE DATABASE MyDatabase FROM DISK = 'Full.bak' WITH NORECOVERY;
RESTORE DATABASE MyDatabase FROM DISK = 'Diff.bak' WITH NORECOVERY;
-- Now you need to restore the transaction logs AFTER the differential
-- If you skip any, database is stuck

Reason 5: Restore WITH STANDBY Instead of NORECOVERY

You use WITH STANDBY (read-only mode) on the final log restore instead of WITH RECOVERY:

RESTORE LOG MyDatabase FROM DISK = '...' WITH STANDBY = 'C:\Backups\Undo.trn';
-- Database is online but in READ-ONLY mode, still in RESTORING state

Diagnosing the Problem

Check Database State

SELECT name, state_desc, recovery_model_desc FROM sys.databases WHERE name = 'MyDatabase';

States you might see:

RESTORING – Waiting for more backups
RECOVERY_PENDING – Recovery in progress
ONLINE – Good, database is usable
OFFLINE – Database is offline (intentionally or by error)

Check SQL Server Error Log

-- Read error log
EXEC sp_readerrorlog 0, 1;

-- Look for:
-- "The backup file contains a log backup from a time after..."
-- "This log backup cannot be restored..."
-- "Incomplete restore sequence"

Check Which Backups Are Expected

SQL Server stores metadata about pending backups:

-- List all backup sets and their details
SELECT
    database_name,
    backup_start_date,
    backup_finish_date,
    backup_type,
    first_lsn,
    last_lsn
FROM msdb.dbo.backupset
WHERE database_name = 'MyDatabase'
ORDER BY backup_start_date DESC;

Look at the LSN (Log Sequence Number) ranges. Each log backup's first_lsn should match the previous backup's last_lsn. If there's a gap, you're missing a backup.

Fixing the Problem

Option 1: Complete the Restore Chain (Correct Path)

If you have the missing backups, restore them in order:

-- Find all log backups since the full backup
SELECT backup_start_date, backup_finish_date, physical_device_name
FROM msdb.dbo.backupset
JOIN msdb.dbo.backupmediafamily ON backupset.media_set_id = backupmediafamily.media_set_id
WHERE database_name = 'MyDatabase'
AND backup_type = 2  -- Transaction log backups
ORDER BY backup_start_date;

-- Restore them in order with NORECOVERY
RESTORE LOG MyDatabase FROM DISK = 'Log_06.trn' WITH NORECOVERY;
RESTORE LOG MyDatabase FROM DISK = 'Log_12.trn' WITH NORECOVERY;
RESTORE LOG MyDatabase FROM DISK = 'Log_18.trn' WITH NORECOVERY;
RESTORE LOG MyDatabase FROM DISK = 'Log_23.trn' WITH RECOVERY;  -- Final one with RECOVERY

Option 2: Stop Waiting for More Backups (RECOVERY)

If you don't want to restore any more logs, tell SQL Server to stop waiting:

-- Bring database online with current state
RESTORE DATABASE MyDatabase WITH RECOVERY;
-- Database is now ONLINE (but may be missing recent data)

Risk: Any transaction logs you haven't restored will be lost. Use this only if you accept data loss.

Option 3: Restore to a Point in Time (STOPAT)

If you have all the logs but want to stop at a specific time:

-- Restore full backup
RESTORE DATABASE MyDatabase FROM DISK = 'Full.bak' WITH NORECOVERY;

-- Restore all logs up to 18:30 (even if log backup is 06:00, 12:00, 18:00, etc.)
RESTORE LOG MyDatabase FROM DISK = 'Log_06.trn' WITH NORECOVERY;
RESTORE LOG MyDatabase FROM DISK = 'Log_12.trn' WITH NORECOVERY;
RESTORE LOG MyDatabase FROM DISK = 'Log_18.trn' WITH NORECOVERY;

-- Now restore the final log but STOP at 18:30
RESTORE LOG MyDatabase FROM DISK = 'Log_23.trn'
WITH STOPAT = '2024-01-15 18:30:00', RECOVERY;
-- Database is now ONLINE at 18:30 state (12 hours before crash)

Option 4: Abandon Restore and Start Over

If you're too deep in a broken restore chain:

-- Drop the stuck database
DROP DATABASE MyDatabase;

-- Restore from the full backup again
RESTORE DATABASE MyDatabase
FROM DISK = 'C:\Backups\MyDatabase_Full_20240115.bak'
WITH RECOVERY;  -- Go straight to ONLINE
-- You'll lose data since the full backup, but database is usable

Prevention: Getting the Restore Right the First Time

Build a Restore Script Template

-- Template for restore sequence
-- 1. Restore FULL backup
RESTORE DATABASE [DATABASE_NAME]
FROM DISK = N'FULL_BACKUP_FILE'
WITH MOVE 'DATA_FILE' TO N'PATH\DATA.mdf',
     MOVE 'LOG_FILE' TO N'PATH\LOG.ldf',
     NORECOVERY;
GO

-- 2. Restore all differential backups (if any) with NORECOVERY
RESTORE DATABASE [DATABASE_NAME]
FROM DISK = N'DIFF_BACKUP_FILE'
WITH NORECOVERY;
GO

-- 3. Restore all transaction log backups
-- Important: IN CHRONOLOGICAL ORDER
RESTORE LOG [DATABASE_NAME] FROM DISK = N'LOG_20240115_0600.trn' WITH NORECOVERY;
RESTORE LOG [DATABASE_NAME] FROM DISK = N'LOG_20240115_1200.trn' WITH NORECOVERY;
RESTORE LOG [DATABASE_NAME] FROM DISK = N'LOG_20240115_1800.trn' WITH NORECOVERY;

-- 4. Final restore with RECOVERY to bring online
RESTORE LOG [DATABASE_NAME] FROM DISK = N'LOG_20240115_2359.trn' WITH RECOVERY;
GO

-- Verify
SELECT name, state_desc FROM sys.databases WHERE name = '[DATABASE_NAME]';

Validate Backup Chain

Before restoring, verify all backups are present and in order:

-- Check backup files exist
RESTORE FILELISTONLY FROM DISK = 'Full.bak';
RESTORE FILELISTONLY FROM DISK = 'Log_06.trn';
RESTORE FILELISTONLY FROM DISK = 'Log_12.trn';
-- etc.

-- If any are missing or corrupted, RESTORE will fail with an error message

Test Restores Regularly

Don't wait for a disaster to test your restore procedure. Test monthly:

Pick a nonproduction server
Restore the full backup
Restore each log backup in sequence
Verify the database is online and data is correct
Document any issues

The Restore Checklist

[ ] Database is in RESTORING mode (check with SELECT state_desc FROM sys.databases)
[ ] Found the full backup file (or most recent full)
[ ] Located all transaction log backups since full backup
[ ] Sorted log backups in chronological order
[ ] Verified all backup files exist and are not corrupted
[ ] Generated the restore script (using template above)
[ ] Restored full backup with NORECOVERY
[ ] Restored each log backup in order with NORECOVERY
[ ] Restored final log with RECOVERY
[ ] Verified database is ONLINE and queryable
[ ] Ran integrity checks (DBCC CHECKDB)
[ ] Validated data is correct

Quick Reference: Common Mistakes

Mistake	What Happens	Fix
Forget NORECOVERY on full backup	Can't restore logs	Start over with NORECOVERY
Skip a log backup	Error on next restore, DB stuck	Find missing backup or use RECOVERY to stop
Restore logs out of order	LSN error, DB stuck	Drop DB, start restore over
Forget RECOVERY on final log	DB stays RESTORING	RESTORE DATABASE ... WITH RECOVERY
Use STANDBY instead of RECOVERY	DB is read-only, still RESTORING	RESTORE DATABASE ... WITH RECOVERY
Wrong backup file format	Restore fails immediately	Verify backups from correct full backup

When All Else Fails: Recovery from Corruption

If backups are missing or corrupted beyond recovery:

-- Bring database online as-is (may have inconsistencies)
RESTORE DATABASE MyDatabase WITH RECOVERY, ALLOW_INCOMPLETE_LOG_SCAN;

-- Run integrity checks
DBCC CHECKDB (MyDatabase) WITH REPAIR_ALLOW_DATA_LOSS;

-- Export critical data if possible
-- Then decide: restore from older backup, or rebuild from logs

Warning: ALLOW_INCOMPLETE_LOG_SCAN and REPAIR_ALLOW_DATA_LOSS can cause data loss. Use only as a last resort.

← Back to Blog