SQL Server: Database stuck in "Restoring" state

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

You need to use the WITH RECOVERY option, with your database RESTORE command, to bring your database online as part of the restore process.

This is of course only if you do not intend to restore any transaction log backups, i.e. you only wish to restore a database backup and then be able to access the database.

Your command should look like this,

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

You may have more sucess using the restore database wizard in SQL Server Management Studio. This way you can select the specific file locations, the overwrite option, and the WITH Recovery option.

Here's how you do it:

1. Stop the service (MSSQLSERVER);
2. Rename or delete the Database and Log files (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data...) or wherever you have the files;
3. Start the service (MSSQLSERVER);
4. Delete the database with problem;
5. Restore the database again.

I had a similar incident with stopping a log shipping secondary server. After the command to remove the server from log shipping and stopped the log shipping from primary server the database on secondary server got stuck in restoring status after the command

RESTORE DATABASE <database name> WITH RECOVERY

The database messages:

RESTORE DATABASE successfully processed 0 pages in 18.530 seconds (0.000 MB/sec).

The database was usable again after those 18 seconds.