Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

1 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

share|improve this answer
    
thanks I've looked at both of those....so after I remove the msrepl_tran_version columns....do i need to backup the database and restore on subscribers or should i create the publication and the snapshot and then perform this step. –  Masa Rumi Jul 16 '13 at 18:48
    
The reason I am asking this is because I re-created a replication (same type as mentioned here). But now when columns are inserted the msrepl_tran_version is different on both the publisher and the subscriber. –  Masa Rumi Jul 16 '13 at 18:50
    
Use the script and drop the columns on publisher and then restore the databases on subscribers. This will make sure that everything is on same version –  Kin Jul 16 '13 at 18:52
    
Yes will do that. thank you for the help! –  Masa Rumi Jul 16 '13 at 19:28
    
If you feel that the answer is useful then up-vote and mark as answer to help future visitors as well. –  Kin Jul 16 '13 at 19:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.