Here are a few SQL commands that you can use if you simply want to delete a database that is in “Recovering” or “Suspect” mode. You may find yourself self needing to delete the database when rebuilding log-shipping on the primary server and the secondary database failed applying the log files, which then leaves the database in a recovering state. I sometimes will just remove the log-shipping for that database on the primary, which cleans up all the agent jobs on both the primary and secondary servers but leaves the database in this state. Here are the commands:
-- attempts to recover the database. This rarely works but is the safest. Once recovered, you can delete or drop it and rebuild on the primary.
RESTORE DATABASE myDatabase WITH RECOVERY
-- this attempts to detach the database. You can then just delete it from windows explorer and rebuild log-shipping on the primary.
EXEC sp_detach_db myDatabase
-- this is the sledge hammer that will drop the database regardless of database state. Use this last
DROP DATABASE myDatabase
Views – 2605