Hi all

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?

Matt

Still you require to use sp_removedbreplication which removes the replication settings on this database.

There is another way to ignore the setting but I prefer you to use system supplied SPs, if this ain't working then take back route.

That didnt seem to do anything. Bummer.

Anything else I should try?

Then adopt the following with prior testing:
use Userdatabase

updates sysobjects set replinfo = 0 where name ='table_name'

sp_configure 'allow updates', 1
go
reconfigure with overide
go

BOOM!!!!

I checked sysobjects (before running) and replinfo was already 0

I ran the whole script and still no change. This is one stubborn db.

How about setting up repl for that Db (create Publication) and then disabling it again ?

What kind of replication was set on production database?

Even if the GUI tool runs perfectly to remove the Replication components, it will still leave elements behind.
The most common items that are left behind are:

1) database options
2) replication objects
3) sysobject settings
4) rowguid column
5) conflict tables
6) distribution database

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.


Good Luck.

Thanks for the response, I learnt some more about replication

I still am unable to drop any columns on my tables though. Its worth noting that the replinfo column value is 0 for all rows in the sysobject database before I run any "remove replication" scripts.

There dont seem to be any rowguid columns or conflict objects in the db.

I've passed all these details onto our DBA in case it helps resolve the problem

Thanks
Matt

Just in case someone is vaguely interested, our DBA has produced a script that seems to have nicely nuked all replication info

sp_removedbreplication 'mydb'
go

exec sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
go

update sysobjects set replinfo = 0 where replinfo > 0
UPDATE syscolumns SET colstat = colstat & ~4096 WHERE colstat & 4096 <>0
go

exec sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE

Thanks for the enhancement to replace the issue, it helps.