SQL Server: Database stuck in "Restoring" state

Question

I backed up a database:

BACKUP DATABASE MyDatabase TO DISK = 'MyDatabase.bak' WITH INIT --overwrite existing 

And then tried to restore it:

RESTORE DATABASE MyDatabase    FROM DISK = 'MyDatabase.bak'    WITH REPLACE --force restore over specified database 

And now the database is stuck in the restoring state.

Some people have theorized that it's because there was no log file in the backup, and it needed to be rolled forward using:

RESTORE DATABASE MyDatabase WITH RECOVERY  

Except that, of course, fails:

Msg 4333, Level 16, State 1, Line 1 The database cannot be recovered because the log was not restored. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. 

And exactly what you want in a catastrophic situation is a restore that won't work.

---

The backup contains both a data and log file:

RESTORE FILELISTONLY  FROM DISK = 'MyDatabase.bak'  Logical Name    PhysicalName =============   =============== MyDatabase    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDatabase.mdf MyDatabase_log  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDatabase_log.LDF 

Answer 1

I had this situation restoring a database to an SQL Server 2005 Standard Edition instance using Symantec Backup Exec 11d. After the restore job completed the database remained in a "Restoring" state. I had no disk space issues-- the database simply didn't come out of the "Restoring" state.

I ran the following query against the SQL Server instance and found that the database immediately became usable:

RESTORE DATABASE <database name> WITH RECOVERY