Why Your SQL Server Keeps Throwing 'Database in Restoring' State

1. The Backup Chain Is Incomplete (Most Common)

This is the one that trips up almost everyone, including me the first time I saw it. You've just finished restoring a full backup, and the database still says Restoring. You check your restore command and it looks fine. What gives?

The problem is that SQL Server expects a complete backup chain. If you restored a full backup without specifying WITH RECOVERY, and then you didn't follow it with all subsequent differential and log backups, the database stays in Restoring state forever. It's waiting for the next piece of the chain.

Here's the real-world scenario: You're migrating a database from a production server to a staging server. You take a full backup at 2:00 PM, a differential at 3:00 PM, and log backups every 15 minutes. If you restore only the full backup (without RECOVERY) and then try to restore the differential but forget the log backups, the database remains in Restoring.

The fix: Use RESTORE DATABASE ... WITH RECOVERY on the last restore in the chain. This brings the database online immediately. If you've already restored the full backup with NORECOVERY, you can run a simple command to finish:

RESTORE DATABASE YourDatabaseName WITH RECOVERY;

That's it. One line. If you see any error about outstanding backup files, you need to restore all remaining log backups first. In that case, run this for each log backup in order:

RESTORE LOG YourDatabaseName FROM DISK = 'C:\Backups\YourLogBackup.trn' WITH NORECOVERY;
-- Then the final one:
RESTORE LOG YourDatabaseName WITH RECOVERY;

Skip that final WITH RECOVERY and you're back to square one. I've seen this exact mistake in SQL Server 2019, 2017, and even 2012—same behavior, same fix.

2. A Restore Operation Was Interrupted or Left Open

This one happens when you're working in SSMS (SQL Server Management Studio) and you start a restore, then get distracted. Or the restore script times out. Or you accidentally close the connection mid-restore. SQL Server doesn't just abandon the restore—it leaves the database in Restoring state as a safety mechanism.

I've seen this most often during large database restores over a slow network. You kick off a restore of a 200 GB database, go get coffee, come back, and the query window says "Command completed" but the database is still Restoring. The restore actually succeeded, but because you used NORECOVERY (or the default, which is NORECOVERY), the session is still holding a lock.

The fix: First, check if there's an active restore session holding the database. Open a new query window and run:

SELECT session_id, command, percent_complete, estimated_completion_time
FROM sys.dm_exec_requests
WHERE command LIKE '%RESTORE%';

If you see a session, you can kill it (carefully—only if you're sure the restore isn't actually running):

KILL <session_id>;

Then run the recovery command from fix #1:

RESTORE DATABASE YourDatabaseName WITH RECOVERY;

If there's no active session and the database is still stuck, someone else might have started the restore. Check sys.databases for the state:

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

If it says RESTORING and no restore is running, the recovery command above will almost certainly bring it online. This works on SQL Server 2016 through 2022—I've tested it.

3. The Log Chain Is Broken (Missing Log Backups)

This is the sneaky one. You're sure you restored everything correctly. The full backup, all the log backups—you even checked the file list. But the database still won't come online. The culprit? A missing log backup in the middle of the chain.

Imagine you have log backups at 1:00, 1:15, 1:30, and 1:45. You restore the full backup (with NORECOVERY), then restore the 1:00 log (with NORECOVERY), then restore the 1:30 log (skipping 1:15). SQL Server says "I have a gap in the log sequence numbers (LSNs)" and refuses to bring the database online. The database stays in Restoring state because it can't apply the 1:30 log without the 1:15 log—the LSNs don't line up.

The fix: You need to find the missing log backup. Check your backup directory or backup history. Run this query to see the last applied LSN:

SELECT last_log_backup_lsn FROM sys.database_recovery_status WHERE database_id = DB_ID('YourDatabaseName');

Then compare that to the LSNs in your log backup files. If you can't find the missing one, your only option is to restore from the beginning with a full backup and then apply all log backups in the correct order, without gaps. Here's the clean way to do it in SQL Server 2017+:

RESTORE DATABASE YourDatabaseName FROM DISK = 'C:\Backups\Full.bak' WITH NORECOVERY;
RESTORE LOG YourDatabaseName FROM DISK = 'C:\Backups\Log1.trn' WITH NORECOVERY;
RESTORE LOG YourDatabaseName FROM DISK = 'C:\Backups\Log2.trn' WITH NORECOVERY;
RESTORE LOG YourDatabaseName FROM DISK = 'C:\Backups\Log3.trn' WITH NORECOVERY;
-- Continue for all logs, then:
RESTORE DATABASE YourDatabaseName WITH RECOVERY;

Pro tip: If you're restoring to a point in time (like before a corruption event), use STOPAT on the last log restore. But that's a topic for another article—just know it exists.

Quick-Reference Summary Table