How to cleanup Replication Bits

For SQL 2005 and SQL 2008 the sp_removedbreplication stored procedure works great for removing the SQL Replication bits from a database.  SQL 2000 has an older version of this SP which doesn’t always cleanup the database.  You can use the following steps in SQL 2000 to remove the leftover Replication bits.

Problem Description – Manually Removed Replication components following a database restore

Normally, after a database is restored the sp_removedbreplication can be execute to removed Replication settings.  However, this command did not remove all setting and we had to manually execute SQL commands below to clean-up the left over SQL replication settings.  SQL Server 2005 and 2008 have greatly enhanced this stored procedure.

– Following a database restore use

sp_removedbreplication ‘DB_PROD’
go

– From link – http://msdn.microsoft.com/en-us/library/aa239336(SQL.80).aspx

sp_removedbreplication – Removes all replication objects from a database without updating data at the Distributor. This stored procedure is executed at the Publisher on the publication database or at the Subscriber, on the subscription database.

sp_removedbreplication is useful when restoring a replicated database that has no replication objects needing to be restored.

SQL 2000 Challenge

Unfortunately this command isn’t always effective in the older SQL Server 2000 version.  In which case we need to use manual cleanup steps such as ones published in this SQL forum posting.

Restoring a db that was involved in replication

http://www.dbforums.com/microsoft-sql-server/990370-restoring-db-involved-replication.html

Steps to Remove SQL Replication Settings

Ran command to get the output for the objects that have constraints for msrepl_tran_version column

select ‘ALTER TABLE [‘ + OBJECT_NAME(a.id) + ‘] drop constraint [‘ + OBJECT_NAME(a.constid) + ‘]’ + CHAR(13) +
‘alter table [‘ + OBJECT_NAME(a.id) + ‘] drop column [‘+ b.name + ‘]’ + CHAR(13) + ‘go’
from sysconstraints a
inner join syscolumns b on a.id = b.id and a.colid = b.colid
where OBJECT_NAME(a.constid) like ‘%msrepl_tran%’
select name, object_name(id) from syscolumns where name = ‘msrepl_tran_version’

Found that around 74 names were starting with syncobj_ which are views related the replication. Also there were some user tables where we need to drop the constraints & columns. We needed to drop these views since these are also related to replication and also we know that we do not see these VIEW objects related to replication with msrepl_tran_version column in them.  Ran command – for generating the query to drop the views with msrepl_tran_version column in them:

select ‘DROP TABLE [‘ + OBJECT_NAME(a.id) + ‘]’ + ‘go’
from syscolumns a where name = ‘msrepl_tran_version’

Ran command to delete the views starting with Syncobj_ & we are not touching the user tables at the moment.

Example: DROP VIEW [syncobj_0x4432323484413246]

We again checked if there are any objects left with msrepl_tran_version in them using command –

select name, object_name(id) from syscolumns where name = ‘msrepl_tran_version’

Ran command – to get the output for the objects that have constraints for msrepl_tran_version column

select ‘ALTER TABLE [‘ + OBJECT_NAME(a.id) + ‘] drop constraint [‘ + OBJECT_NAME(a.constid) + ‘]’ + CHAR(13) +
‘alter table [‘ + OBJECT_NAME(a.id) + ‘] drop column [‘+ b.name + ‘]’ + CHAR(13)
from sysconstraints a
inner join syscolumns b on a.id = b.id and a.colid = b.colid
where OBJECT_NAME(a.constid) like ‘%msrep%’

Example: ALTER TABLE [OrdersItems] drop column [msrepl_tran_version]

output:

Server: Msg 4932, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN failed because ‘msrepl_tran_version’ is currently replicated.

To remove replication for the database & then remove the msrepl_tran_version columns, we need to first remove the database options. We simply execute sp_dboption for published, merge publish, or subscribed, and set it to false. So we ran command

exec sp_dboption ‘DB_PROD’,’published’, FALSE

The replication option ‘publish’ of database ‘DB_PROD’ has been set to 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. Ran below command that seems to remove all replication info

sp_removedbreplication ‘DB_PROD’
go

exec sp_configure ‘allow updates’,1
RECONFIGURE WITH OVERRIDE
go

update sysobjects set replinfo = 0 where replinfo > 0 — 755 rows affected
UPDATE syscolumns SET colstat = colstat & ~4096 WHERE colstat & 4096 <>0  — 3932 rows affected.
go

exec sp_configure ‘allow updates’,0
RECONFIGURE WITH OVERRIDE

Now re-ran the command to remove the constraints & column for the user table which has column name – msrepl_tran_version in them .

Example: ALTER TABLE [OrderItems] drop column [msrepl_tran_version]

We again checked if there are any objects left with msrepl_tran_version in them, found four them

select name, object_name(id) from syscolumns where name = ‘msrepl_tran_version’

Ran below commands to remove the column with msrepl_tran_version.

Example: alter table [InvoiceOrderLines] drop column [msrepl_tran_version]

At this point all of the replication settings had been removed from the database.