39

I have a development database which has entered recovery mode on restart, and has been recovering for the last hour or so.

I need to either stop the recovery, or kill it in some way.

I don't care one bit about the database or any of the data, I have deployment scripts.

Any ideas?

Improve this question

2 Answers 2

Reset to default
52
  • Stop SQL Server
  • Delete MDF + LDF
  • Start SQL Server
  • Restore (may need to drop first, comes up suspect)

If the MDF and LDF are present on service start up, it will go into rollforward/rollback recovery. You have to drop them to prevent this.

Improve this answer
6
  • 2
    What if you can't stop sql server because of other databases that shouldn't go down? There is no other way?
    – Erik Dekker
    Jan 31, 2014 at 13:11
  • 1
    @ErikDekker Probably too late to be of help, but you can also just detach the database
    – Ben Aaronson
    Sep 30, 2015 at 9:01
  • 1
    This was perfect advice for my situation. Dummy/dev-testing instance, database restore was interrupted midway, had to completely kill it and remove it before attempting a fresh restore. The bit "need to drop first" was the trick -- after you stop SQL service, delete the files, and start SQL service, the DB will still be listed in the object-explorer, but simply right-click-"Delete" (aka Drop) will finish it off. Thanks!
    – NateJ
    Feb 26, 2016 at 1:16
  • 1
    It should be noted that you cannot detach a database stuck in the restoring state. Nor can you detach it.
    – Ian Boyd
    Sep 22, 2016 at 20:30
  • For me, with a database in "Recovery Pending" and a server low on disk space (the likely cause of the "pending"), stopping SQL Server, deleting the mdf and ldf, then a restart was the only way forward. Using Detach Database would not work for me with a database in recovery pending state.
    – Jeff Mergler
    May 10, 2018 at 18:42
8
  1. Right click on the database in SQL Server Management Studio, and hit Detach.
  2. Select the Drop Connections checkbox only, and hit ok.
  3. Then the database will disappear.
  4. Right click on the Databases folder, and click Attach...
  5. Click Add and find the .mdf file for the database you deleted. You will have to find it in the file system.
  6. Click Ok, and it will be back online.
Improve this answer