can't detach/delete migrated DB mistakenly marked for replication

Question

I was given the MDF and LDF files for a database to be migrated from our soon-to-be-retired SQL 2000 server to attach to our new SQL 2008 server.  These weren't the result of a correct detachment process, but rather taken from the server when it was shut down.

I wasn't aware that the database was marked for replication on the SQL 2000 server.  We don't (yet) have replication enabled on the SQL 2008 server, but the system DBs indicate this migrated database is marked for replication, even though there are no local publications nor subscriptions.

Now, I need to drop this database, but I can't because it's marked for replication.  I tried taking it offline, deleting it, detaching it, shutting down the service and moving the files, but nothing will allow me to get rid of it.  Every operation I try gives me a 3724 error.  Without being able to change system tables, there's no way to un-mark it for replication.

Is it possible for me to delete this database?  If so, how?  Thanks in advance.

Answer 1

Can you try the following

1) sp_replicationdboption 'DatabaseName','publish','false'
2) sp_replicationdboption 'DatabaseName','merge publish','false'
3) sp_removedbreplication 'databasename'

If these fail you will need to try this in the problem database:

sp_dropsubscription @publication = 'PublicationName',@article = N'all',@subscriber = 'All',@ignore_distributor=1
GO
sp_dropmergesubscription @publication = 'PublicationName',@article = N'all',@subscriber = 'All',@ignore_distributor=1
GO
sp_droppublication 'PublicationName',@ignore_distributor=1
GO
sp_dropmergepublication 'PublicationName',@ignore_distributor=1
GO

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

Steps 1-3 worked, and I was able to drop the database.  Fantastic!  Thank you very much for you assistance, as a project for which I came in this weekend would be incomplete without it.