lunes, 07 de abril de 2008 21:36ModeradorOk. 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)
Todas las respuestas
lunes, 07 de abril de 2008 23:50Moderador
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>
martes, 08 de abril de 2008 13:40ModeradorSorry, 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.
martes, 08 de abril de 2008 15:46Moderador
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.
miércoles, 09 de abril de 2008 0:48Moderador
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 catalogCode Snippetdbcc detachdb('UserData')
2. delete the physical data files
miércoles, 09 de abril de 2008 22:42ModeradorThe dbcc detachdb worked!!!! It displayed an error, but dropped the database.
Thanks for you help.
jueves, 12 de enero de 2012 1:44Thanks 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 :-(.
- Editado SAinCA jueves, 12 de enero de 2012 1:45
miércoles, 18 de abril de 2012 6:47
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 :
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.
- Editado Khalid Mehmood Chitrali miércoles, 18 de abril de 2012 6:47
jueves, 14 de marzo de 2013 4:57That 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.