Unanswered: Restoring a db that was involved in replication
I have restored a db from a live environment to my dev envorinment. This db on live is a replication publisher.
My dev environment has no replication, but when I try to modify the schema in the db I restored I get the following error
"Server: Msg 4932, Level 16, State 1, Line 441
ALTER TABLE DROP COLUMN failed because 'url_web1' is currently replicated."
Does anyone know what I have to do to restore the database on my de box so it 'forgets' its live replication settings?
On my dev server I have no items in the replication folder in Enterprise Manager but it seems replication details are stored somewhere, perhaps in the backup itself?
To remove the database options, you simply execute sp_dboption for published, merge publish, or subscribed, and set it to false.
exec sp_dboption 'pubs','published', FALSE
Removing the sysobject settings is more involved in that you have to turn on allow updates, update the replinfo column and set it to 0 where it is 128, and then turn off allow updates. At this point, you should have a fully functional database that allows you to alter/drop objects and drop databases. You could stop here without having any side effects. But, we might as well take everything out. You get rid of the rowguid column, if it exists, by altering the table and dropping the column. Conflict tables are simply user tables and can be removed with a simple drop command. The same goes for any triggers, views, or procedures that replication created.