Wow it's been some time since I blogged. Mainly because of being very busy at work and three weeks of holidays. Next week I'll be offline again because Pascal, Alex and me will be preparing for the .NET 3.5 Summer Class in September. If you still don't know what it is, have a look at it and be sure to come. We'll teach you almost everything that's .NET 3.5 and you'll go home knowing what a great addition it is and how to build projects using it. This won't be a standard training, you will know how to use this stuff in your next projects.
Anyway, I was having some problems with dropping a database. It was the mirror of another one, mirrored by transaction log shipping. For some reason during my holidays and after some tweaking done by my client on more then one database, it failed. After having a look, some properties changed and not for the good. I decided to set it up again from scratch.
After removing transaction log shipping from the root database, I wanted to drop the database on the mirror server. Unfortunately I got the following error:
Cannot drop the database 'xxx' because it is being used for replication. (Microsoft SQL Server, Error: 3724)
The jobs were gone, the root database wasn't attached anymore, but SQL Server 2005 still thought it was being used for replication. I even tried hacking the system tables using the dedicated administrator connection but it just wouldn't drop, delete, detach or anything.
SolutionThe final solution was to create a new database on another server with the same name and create a backup. After copying the backup to the server with problems, I chose to restore the backup. You can't do this by right-clicking on the database, because it'll only allow to restore transaction logs. Right-click the server itself and choose to restore it onto the database with problems and select to "overwrite" in the options screen.
It overwrote the database and I could delete it!
Pingback from University Update-Microsoft SQL Server-Cannot drop database because of replication
Thanks
it was very helpful. since few days i was facing this issue.
Worked a treat with SQL2000, I backed-up secondary PUBS to disk then restored, changing the name.
just wasted 3 hours and you helped me with a 2 second fix. Thanks
I faced the problem using SQL2000. Then I took the database offline without problems and I could delete the offline database. The data and log files had to be deleted manualy.
This works.. Thanks for your help!
I have used different named database, of the same database having equal db structure worked fine.Hope this helps. You need not have to create a new database!
The simple solution for me was to take offline and delete the files. Thanks for the tip.