Database in "Restoring" state --- HELP

Question

Ok.  I have spent hours on this already.

We were restoring a database from tape.  The tape drive failed in the middle of the restore.  So now we CANNOT restore from tape to try again.

Before I started the restore, I detached and made a copy of the database files.

The database is in "Restoring" state.  All I want to do is DELETE the "restoring" database and attach the copy.

How do I get rid of this thing?????  In 2000 it was an update of the sys.databases.  In 2005, you cannot do that anymore.


SQL 2005 (9.0.2153)

Answer 1

When the database status is in "restoring", it's waiting for the completion of the log restore before recovering the database. You can run the below to force a recover.

 

Code Snippet

restore database <db> with recovery

 

 

 

To force drop/delete of a database - this will delete the physical files (be warned):

 

Code Snippet

drop database <db>

 

 

 

Answer 2

Sorry, I should have mentioned I tried that already. It reports:

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.


Drop database will not drop because the database is in "Restoring" mode.

Msg 927, Level 14, State 2, Line 1
Database 'UserData' cannot be opened. It is in the middle of a restore.



I cannot do ANYTHING to this database.  I need to just force it to drop.

Answer 3

I think you can start Sql Services and delete the database files for that database and then replace the ldf and mdf files from tape and start Sql Server and attach it and see how it goes.

 

- Deepak

Answer 4

Tom,

 

I have no prob dropping a 'restoring' database. Worst case, stop sqlserver service and delete the physical files then drop the database.

 

You can also try this "undoc" route.

 

1. run the below to detach the db from master catalog

 

Code Snippet

dbcc detachdb('UserData')

 

 

 

2. delete the physical data files

 

 

 

Answer 5

The dbcc detachdb worked!!!!  It displayed an error, but dropped the database.

Thanks for you help.

Answer 6

Thanks from me, too.  This is the ONLY technique that worked in SQL2005EE - "WITH RECOVERY,REPLACE" doesn't work if the original restore failed due to the mdf and ldf disk drives going AWOL :-(.

Answer 7

I had a situation where my database showed restoring state and I couldn't run any queries and couldn't connect with our software.

What I did to get out of this situation is:

1. Stop all SQL related services from windows services.

2. I opened the DATA folder where the Ldf and Mdf files resides in the SQL directory, normally its like :
"C:\Program Files\***********\MSSQL\DATA

3. Then I copied both the Ldf and Mdf files of the database:
      [db name].mdf         and        [db name]_log.ldf

I copied both of these files to another folder.

4. Then I started all the SQL related services (in step 1) again from windows services.

5. Started my MS SQL Management studio with normal login.

6. Right click on the culprit database and hit DELETE (to delete the database at all).

7. All the LDF and MDF files related to this database have gone from DATA folder (mentioned in step 2).

8. Created a new database with the same name (same name of the one I deleted in step 6 - the culprit database).

9. Then [database name]->right click -> tasks -> Take Offline.

10. I then Copied both the files (from step 3) back to the DATA folder (step 2).

11. [database name]->right click -> tasks -> Bring Online.

12. AlhamduLLilah , its all done.





Answer 8

That worked pretty good. Database went into this "restoring" state during a backup of tail log files and was still "restoring" a few hours later. I'm pretty good with windows and computers but SQL is something I'm just now learning. I'm not particularly happy since I have no idea why it suddenly decided to do this.