Add a table to a replication


  • I have two MS SQL servers running.  The one is a local publication and the other is a subscriber.  SQL Server version 10.50.1600.

    A while back I added a table to one of the publications and noticed today that the subscriber has bunch of records and the publisher has none.  Digging into the list of articles, I see that table is not listed.

    It is my understanding that to fix this I need to reinitialize all subscription from the publication server.

    Is there anything else I need to know, I really don't want to lose the data in the table.



    John J. Hughes II

    Monday, June 11, 2018 8:02 PM

All replies

  • Try doing a sp_refreshsubscriptions 'PublicationName'

    This might work depending on your settings for immediate_sync and allow anonyous.

    If it does not work use

    sp_changepublication to set these to false and then run

    sp_refreshsubscriptions 'PublicationName'


    Monday, June 11, 2018 8:28 PM
  • You said the table on publisher side has no records but the subscriber does have records. I don't see a need to re-initialize the entire subscription. 

    1. Whatever writes to that table on subscriber needs to be paused. Ask for downtime so there are no new records written to that table being worked upon.

    2. use import/export to copy that table and data from subscriber to publisher. Make sure it carries over the exact same table structure.

    3. At this point, you would have the same table copy on both i.e. subscriber and publisher.

    4. On publisher, Add that table to the publication.

    5. run a new snapshot

    6. run the distribution or merge agent (depending on your setup). By default, it will drop and recreate the table on the subscriber.  

    7. make sure the records match. 

    Make sure the application or whatever writes to that table does that on the publisher (if transactional) unless you have bi-directional merge replication.

    Hope this helps.

    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Monday, June 11, 2018 11:29 PM