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.

OK, I have similar problem and exactly as it was in case of Pauk, it was caused by the server running out of disk space while restoring and so caused a permanent restoring state. How to end this state without stopping SQL Server services?

I have found a solution :)

Drop database *dbname*

Right Click database go to Tasks --> Restore --> Transaction logs In the transactions files if you see a file checked, then SQL server is trying to restore from this file. Uncheck the file, and click OK. Database is back .....

this solved the issue for me, hope this helps someone.

I figured out why.

If the client who issued the RESTORE DATABASE command disconnects during the restore, the restore will be stuck.

It's odd that the server, when told to restore a database by a client connection, will not finish the restore unless the client stays connected the entire time.

In my case, it was sufficient to drop the database which was hanging in state "Restoring..." with the SQL command

 drop database <dbname> 

in a query window.

Then I right-clicked on Databases and selected Refresh which removed the entry in the SQL Server Management Studio (SSMS). Afterwards I did a new restore which worked fine (note that bringing it offline did not work, a restart of the SQL service did not work, a server reboot did not work as well).

Use the following command to solve this issue

RESTORE DATABASE [DatabaseName] WITH RECOVERY

I have had this problem when I also recieved a TCP error in the event log...

Drop the DB with sql or right click on it in manager "delete" And restore again.

I have actually started doing this by default. Script the DB drop, recreate and then restore.

Have you tried running a VERIFY ONLY? Just to make sure it's a sound backup.

http://msdn.microsoft.com/en-us/library/ms188902.aspx

What fixed it for me was

1. stopping the instance
2. creating a backup of the .mdf and .ldf files in the data folder
3. Restart the instance
4. delete the database stuck restoring
5. put the .mdf and.ldf files back into the data folder
6. Attach the instance to the .mdf and .ldf files

Ran into a similar issue while restoring the database using SQL server management studio and it got stuck into restoring mode. After several hours of issue tracking, the following query worked for me. The following query restores the database from an existing backup to a previous state. I believe, the catch is the to have the .mdf and .log file in the same directory.

RESTORE DATABASE aqua_lc_availability
FROM DISK = 'path to .bak file'
WITH RECOVERY

RESTORE DATABASE {DatabaseName}
   FROM DISK = '{databasename}.bak'
   WITH REPLACE, RECOVERY

If you want to restore an SQL Server database from a backup file, you can use the following script:

RESTORE DATABASE [MyDatabase] -- which database to restore
FROM DISK = N'X:\MyDatabase.bak' -- location of the database backup
WITH 
    FILE = 1, -- restore from a backup file
    -- declare where the file groups should be located (can be more than two)
    MOVE N'MyDatabase_Data' TO N'D:\SSDPATH\MyDatabase.mdf',
    MOVE N'MyDatabase_Log' TO N'E:\HDDPATH\MyDatabase.ldf',
    -- Tape option; only relevant if you backup from magnetic tape
    NOUNLOAD,
    -- brings the database online after the database got restored
    -- use this option when you don't want to restore incremental backups
    -- use NORECOVERY when you want to restore differential and incremental backup files
    RECOVERY,
    -- replace existing database with the backup 
    -- deletes the existing database
    REPLACE, 
    -- print log message for every 1 percent of restore
    STATS = 1;

This is an old issue that keeps coming up all the time with SQL Server, even the latest 2019 version! I don't know why Microsoft have left this pain take hold for so long and allowed their MSSQL engine to continue behaving this way. Never the less, I propose another possible solution for those that tried the RESTORE DATABASE WITH RECOVERY option and it still didn't work.

Log into the server itself and fire up the default SSMS program on the actual database server. Next go to the "recovery" database and DELETE it. Done. Problem gone. If you needed to keep it, copy the MDF file and rename it and attach it as a new database. Worked for me on SQL Server 2008 R2.