Sql-server – Does existing msrepl_tran_version column need to be removed when re-creating replication

sql-server-2008

What is the process for removing a replication and then re-creating the replication. I know the process to set up replication. However, If i wanted to take a publisher database copy it and restore it and create another publication and replication how would I do that?

Would I need to remove the previous mesrepl_tran_version column from all the tables???

The type of replication that I will be re-creating is Transactional Replication with updateable subscribers.

Additionally, After I restore the database and create the publication do I need to let the snapshot complete and then back up that database and restore on subscribers and create the subscription

or

when I restore the database for the publisher can I also restore it on the subscriber?

Help will be greatly appreciated!

Best Answer

Refer to : Why does replication add a column to replicated tables; will it be removed if the table isn't published?

If a transactional publication supports queued updating subscriptions, replication adds the column msrepl_tran_version to every table. If the table is dropped from the publication, the msrepl_tran_version column is not removed.

you can drop the columns after you restore the database (if you want). Nothing harm in keeping them as well.

-- this script will generate drop statements.
-- copy the generated statements to DROP columns related to T-rep msrepl_tran_version
-- First drop constraints 
SELECT 'IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''' + DC.NAME + ''') and Type = ''D'')
            ALTER TABLE [' + OBJECT_SCHEMA_NAME(SO.ID) + '].[' + SO.NAME + '] DROP CONSTRAINT [' + DC.NAME + ']'
FROM SysObjects SO
INNER JOIN SysColumns SC ON SO.ID = SC.ID
INNER JOIN sys.default_constraints DC ON SO.ID = DC.Parent_object_id
    AND SC.colid = DC.Parent_column_id
WHERE SO.XTYPE = 'U'
    AND SC.NAME = 'msrepl_tran_version'

UNION

SELECT 'IF EXISTS (SELECT * FROM dbo.syscolumns where id = OBJECT_ID(N''' + SO.NAME + ''') and Name = ''msrepl_tran_version'')
           ALTER TABLE [' + OBJECT_SCHEMA_NAME(SO.ID) + '].[' + SO.NAME + '] DROP COLUMN [msrepl_tran_version]  '
FROM SysObjects SO
INNER JOIN SysColumns SC ON SO.ID = SC.ID
INNER JOIN sys.default_constraints DC ON SO.ID = DC.Parent_object_id
    AND SC.colid = DC.Parent_column_id
WHERE SO.XTYPE = 'U'
    AND SC.NAME = 'msrepl_tran_version'
ORDER BY 1

Also, refer to How to cleanup Replication Bits

Related Question