SQL Server Drop Database: Here’s Your Fix

Another part of tech blogging is that, sadly, there are people blogging about the wrong ways to resolve certain issues. Your parents told you not to believe everything you read on the internet – are they liars?  Or is that “expert” telling you the best way to do backups is with maintenance plans the one serving up the bull?

SQL Server Drop Database

I had been asked to detach a database. I ran the script to detach and got the error message 3724 – Cannot drop the database because it is being used for replication.

Well, isn’t that special.

Was there replication? Yes. Were there any publications associated with this database? There used to be but not any more (at least not visible at the publisher server in SSMS). There were some other issues that needed resolving but those had nothing to do with this situation. I pop over to my other screen and start looking for the right scripted solution (I figure I will have to do this again so better that I save it off to a script with some notes).
First, I want to show that the database is enabled for replication – sp_helpreplicationdb is going to give me this information.

Ok.  Now what??

Based on the results from sp_helpreplicationdb , I now have confirmation that my database is at least enabled for replication. The next thing I need to do is turn off replication for this database with sp_removedbreplication.

Fixed – WITH ONE LINE OF CODE!!!
After ran this I was able to detach the database. And all was right with the world. On Saturday night. Did I mention this was on Saturday night?
I’m a party animal.

Anyways, back to what inspired me to write this in the first place…after my search I had opened a few of the results in new tabs in my browser. One of them was here.
After a little more searching based on this link I had what I needed with the right syntax. I resolved the issue but when I was done I still had to go back and close the new tabs I had just opened. It was then that I saw a completely different solution. While this other solution may have allowed you to drop the database, it required far more steps than the one I presented above. Also, in my case, I did not want to drop the database, just detach it. Restoring the database from a backup that contains no data wipes out my database, and was not part of the requirement.

So essentially, to be able to detach the database I would have to run sp_helpreplicationdb and sp_removedbreplication OR I would have to take a backup, validate that all the data that was needed was in the backup, create the other database, backup that database, copy the backup file, restore the backup with replace over the database I am trying to drop, and then detach or drop the database. In this case, the desired end result was to migrate the database files to a new server and attach them. This would be changed to restoring from the backup of the original database. If you have a case where you are having to do this for multiple databases, this would be multiplied.

Lesson learned, your parents were right.