I wanted to physically move the files associated with a database in SQLServer 2005 Express. I backed up the database, detached the database, copied the files to a different drive, and then reattached.
When I reattached I changed the filename at the top half of the screen, but didn't notice that the filename used in the bottom half of the screen (for the individual files) had the old filename.
Since then whenever I've started up the server and the SQLServer service I get a message saying that it is recovering, even an hour later.
I would like to detach the database, but I can't do that. I can stop SQLServer and rename the files, but then it just gives an error message about not finding the files.
There are messages in the application log
Process 51:0:0 (0xe94) Worker 0x03A800E8 appears to be non-yielding on Scheduler 0. Thread creation time: 12897060171040. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 0%%. System Idle 98%%. Interval: 1750921 ms.
How do I get rid of my database entry in the management studio so I can either reattach it properly, or restore from backup?
Best Answer
Paul Randal has an excellent blog post on recovering a database by setting it into Emergency Mode and using CHECKDB with the REPAIR_ALLOW_DATA_LOSS option. After you recover the database hopefully you can drop and restore it from the backup correctly.
Hope this helps