{"id":227,"date":"2016-01-27T13:07:50","date_gmt":"2016-01-27T19:07:50","guid":{"rendered":"http:\/\/www.sqlkitten.com\/?p=227"},"modified":"2016-01-27T13:07:50","modified_gmt":"2016-01-27T19:07:50","slug":"cannot-drop-the-database-because-it-is-being-used-for-replication-the-right-fix-and-the-long-way","status":"publish","type":"post","link":"http:\/\/www.sqlkitten.com\/?p=227","title":{"rendered":"Cannot drop the database because it is being used for replication – the Right Fix and the Long Way"},"content":{"rendered":"

One of the things I am still trying to get past when it comes to writing about tech is the fact that I can’t always be completely original. I can write about different experiences and scripts but in many cases someone will have already written about the same thing. Part of tech blogging is realizing that even if someone else has already written about something you want to write about WRITE ABOUT IT ANY WAY. Maybe you had a task that was slightly different from what others have written about or maybe you can embellish on something they left out. Bottom line, you can be creative and reach people.<\/p>\n

Another part of tech blogging is the fact that sadly there are people blogging about the wrong<\/span> other 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?<\/p>\n

I had been asked to detach a database. The person doing it couldn’t and tossed it to me. Cool. No big deal. Then I ran the script to detach and got the error message\u00a03724 – Cannot drop the database because it is being used for replication.<\/p>\n

\"Well,<\/a>

Well, isn’t that special.<\/p><\/div>\n

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).<\/p>\n

First, I want to show that the database is enabled for replication – sp_helpreplicationdb<\/a> is going to give me this information.<\/p>\n

\"help\"<\/a>

Ok. Now what?<\/p><\/div>\n

Based on the results from sp_helpreplicationdb\u00a0, 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<\/a>.<\/p>\n

\"Fixed<\/a>

Fixed – WITH ONE LINE OF CODE!!!<\/p><\/div>\n

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.<\/p>\n

Anyways, back to the tale of the long way to do this…after my search I had opened a few of the results in new tabs in my browser. One of them was here<\/a>. 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\u00a0I 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.<\/p>\n

Frustrated after finding this, I took to twitter.<\/p>\n

\n

Always interesting to find a blog post with a bassakwards, heavy handed answer to a SQL problem with the right solution in the comments.<\/p>\n

\u2014 Amy (@texasamy) January 24, 2016<\/a><\/p><\/blockquote>\n