none
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)

    Monday, April 07, 2008 9:36 PM

Answers

  • 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

     

     

     

    Wednesday, April 09, 2008 12:48 AM

All replies

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

     

     

     

    Monday, April 07, 2008 11:50 PM
  • 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.

    Tuesday, April 08, 2008 1:40 PM
  • 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

    Tuesday, April 08, 2008 3:46 PM
  • 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

     

     

     

    Wednesday, April 09, 2008 12:48 AM
  • The dbcc detachdb worked!!!!  It displayed an error, but dropped the database.

    Thanks for you help.
    Wednesday, April 09, 2008 10:42 PM
  • 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 :-(.
    • Edited by SAinCA Thursday, January 12, 2012 1:45 AM
    Thursday, January 12, 2012 1:44 AM
  • 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.

    

    


    Wednesday, April 18, 2012 6:47 AM
  • 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.
    Thursday, March 14, 2013 4:57 AM
  • That worked..

    Gave error, then took remote of DB Server and checked, the DB Was detached, deleted the files and restored from  the recent backup.

    :)

    Wednesday, November 06, 2013 11:31 AM
  • DBCC DEATCHDB ('DB_NAME') worked like magic ..Thanks a bunch
    Tuesday, February 11, 2014 6:17 PM