SQL Server Database Stuck in Restoring State: How to Fix It

A database restore operation that hangs or fails to complete will leave your database in a restoring state and completely inaccessible to users and applications. To recover it, run RESTORE DATABASE MyDatabase WITH RECOVERY to bring the database online. If that fails due to log issues, you have two additional recovery paths depending on whether the log file is intact or missing.

This is one of those problems that tends to surface at the worst possible time. A restore runs overnight, something goes wrong, and the team arrives in the morning to find a critical database sitting in a restoring state with applications throwing errors. Knowing exactly what commands to run, and in what order, can cut recovery time from hours to minutes.

Why Does a Database Get Stuck in the Restoring State?

SQL Server uses the restoring state to signal that a database is mid-recovery and not yet ready for use. This is normal behaviour during a planned restore sequence, particularly when applying multiple transaction log backups in order. The problem occurs when the restore process is interrupted, fails partway through, or when the final WITH RECOVERY step is never executed.

Common causes include:

  • A restore job timing out or being killed before completion
  • Network interruption during a restore from a network share
  • Applying a differential or log backup that fails validation
  • Initiating a restore without specifying WITH RECOVERY, leaving the database in standby or norecovery mode
  • A log backup chain that cannot be completed because a file is missing or corrupt

The database will remain inaccessible until you explicitly tell SQL Server to finalise the recovery process or perform a forced restore.

Step 1: Attempt Recovery With RESTORE WITH RECOVERY

The first thing to try is completing the recovery process. If the restore itself finished but SQL Server is waiting for additional log backups that aren't coming, this command will finalise recovery and bring the database online:

RESTORE DATABASE MyDatabase
WITH RECOVERY

This tells SQL Server to roll back any uncommitted transactions and mark the database as online. It's a clean, safe operation when the data files are intact and the restore was simply left in an incomplete state.

Run this first. In many cases, it resolves the issue immediately.

What If You Get "The Database Cannot Be Recovered Because the Log Was Not Restored"?

This error means SQL Server cannot complete recovery because it expects additional log backups that haven't been applied. The restore sequence is incomplete from SQL Server's perspective.

If you don't have the remaining log backups, or if you simply need to get the database back online from the last full backup, you can force a complete restore using the WITH REPLACE option:

RESTORE DATABASE MyDatabase
FROM DISK = 'MyDatabase.bak'
WITH REPLACE, RECOVERY

A few important points about this command:

  • WITH REPLACE instructs SQL Server to overwrite the existing database, including any files currently associated with it. Use this deliberately, not as a first instinct.
  • WITH RECOVERY finalises the restore and brings the database online in a single step.
  • This will restore the database to the point in time of the backup file specified. Any transactions that occurred after that backup was taken will be lost.
  • Make sure the path in FROM DISK points to a valid, accessible backup file. If the backup sits on a network share, verify the SQL Server service account has read access to that path.

This approach is appropriate when you're recovering from a failed restore and you're willing to accept data loss back to the backup point. It's not appropriate if you have log backups available that you still want to apply.

What If the Log File Is Missing or Corrupt?

A separate but related scenario is when a database has been detached and the transaction log file (.ldf) has been damaged, deleted, or is otherwise unavailable. In this situation, you can't simply attach the database normally because SQL Server expects both the data file (.mdf) and the log file to be present.

The stored procedure sp_attach_single_file_db handles this case. It attaches a database using only the primary data file and instructs SQL Server to create a new, empty transaction log file:

EXEC sp_attach_single_file_db
    @dbname = 'TEST',
    @physname = 'D:\Data\TEST1_DATA.MDF'

This is useful in a pinch, but there are several things to be aware of before using it:

  • This procedure is deprecated in modern versions of SQL Server. Microsoft's recommended alternative is CREATE DATABASE ... FOR ATTACH_REBUILD_LOG, which achieves the same result in a supported way.
  • A new log file will be created in the same directory as the data file, unless SQL Server cannot write there, in which case it will fall back to the SQL Server default data directory.
  • The database will come online, but you should run DBCC CHECKDB immediately afterwards to verify data integrity. Losing a log file means any uncommitted transactions at the time of the failure were not cleanly rolled back.
  • This approach is for recovery only. It is not a substitute for having proper backups.

The supported equivalent using CREATE DATABASE syntax looks like this:

CREATE DATABASE TEST
ON (FILENAME = 'D:\Data\TEST1_DATA.MDF')
FOR ATTACH_REBUILD_LOG

Both achieve the same outcome. The CREATE DATABASE version is preferred for SQL Server 2012 and later.

When you find a database stuck in the restoring state, work through these steps in order:

  1. Run RESTORE DATABASE MyDatabase WITH RECOVERY and check whether the database comes online.
  2. If that fails with a log-related error and you have the missing log backups, apply them in sequence, then run WITH RECOVERY again.
  3. If you don't have the log backups and need to restore from the last full backup, run RESTORE DATABASE MyDatabase FROM DISK = 'MyDatabase.bak' WITH REPLACE, RECOVERY.
  4. If the database was detached and the log file is missing, use CREATE DATABASE ... FOR ATTACH_REBUILD_LOG to rebuild the log and bring the database online, then run DBCC CHECKDB to verify integrity.

Don't skip steps or jump straight to the more aggressive options. The WITH RECOVERY command is non-destructive. The WITH REPLACE option is not.

Key Takeaways

  • A database stuck in the restoring state can be recovered by running RESTORE DATABASE MyDatabase WITH RECOVERY, which finalises the recovery process without overwriting data.
  • If SQL Server reports the log was not restored, use WITH REPLACE, RECOVERY to force a full restore from a backup file, accepting that data after the backup point will be lost.
  • When a log file is missing after a detach, use CREATE DATABASE ... FOR ATTACH_REBUILD_LOG rather than the deprecated sp_attach_single_file_db procedure.
  • Always run DBCC CHECKDB after any forced recovery operation to confirm database integrity before returning it to production use.
  • Prevention is straightforward: use WITH NORECOVERY only when you intend to apply additional backups, and always confirm your restore jobs complete successfully with a final WITH RECOVERY step.

If your team is regularly dealing with failed restores, stuck databases, or backup and recovery issues, it's worth having a proper review done. DBA Services provides SQL Server health checks and managed database support that cover backup validation, restore testing, and recovery procedure documentation. Getting your recovery process right before an incident happens is considerably easier than fixing it under pressure at 2am.