SQL Server: Database stuck in "Restoring" state

Question

i backed up a data:

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

Hi,

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 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.

Cheers, John

Answer 2

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

Answer 3

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.

Answer 4

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.

Good luck!

Answer 5

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

Answer 6

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

Answer 7

There can also be problem deleting a stuck database if snapshot is enabled. For me this worked:

1. First I followed Tipu Delacablu steps (read a few posts up)
2. run command: drop database [your database], which will give you an error telling you the name of the snapshot database
3. run command: drop database [snapshot database], and then run the command in step 2 again.

Answer 8

I have the same issue but I am unable to connect to the SqlServer so I can not get into the Query, any suggestions?

Answer 9

Thanks Tipu - I had to delete the file in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup as well.

Answer 10

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.

Answer 11

I want to know how to restore database after it has been overwriten by backing up restore.My case is as follows I have restored my database from old backup by I restored this database by mistake on last data base updated so it overwritten this updated database.so please tell me how to restore my original database before it's overwritten.