I have a transactional replication(with initialize from backup) setup in my server.

So i setup replication as below: Used full backup(publisher database full back is scheduled every night) from the previous day of the publisher database. Did restore of the full backup in the subscriber and then created publisher and subscriber. While creating subscriber,i took a differential backup and used it to synch.

And everything works fine. After two days,i noticed that i missed to add a table in the publisher/subscriber.

  1. Now is it possible to add that table into the publisher/subscriber.?
  2. If i create a separate publisher for that table,when i create subscriber for the new publisher ,what backup i should use?Already two nightly scheduled full backup and several transactional backup are generated,so how can i synch from the full backup i used to restore in the subscriber.
  • Had a long time with no use of replication, but maybe you should take a look to sp_addarticle. This should add the article to the publication. I think you will need to run the snapshot agent again to generate the scripts and add the objects to the subscription database. Since I mentioned there's a long time I had not use replication, probably you should take a test on this. Hope this helps. – dbamex Jul 6 '19 at 2:17
  • i am not using snapshot.I use backup to initialize subscription. – user9516827 Jul 6 '19 at 4:49
  • 1
    If you want to add just the single table to the existing publication, you won't be able to initialize from backup, unless you reinitialize everything. Is there a reason you can't initialize this forgotten table using a snapshot? Is there a reason you can't reinitialize every table using the backup? – AMtwo Jul 6 '19 at 15:52
  • Cannot use snapshot as it will take long time to create snapshot .I am replicating my prod server.I don't want any impact of prod server.I can reinitialize everything using backup,but it will take long time again. – user9516827 Jul 7 '19 at 17:31
  • @AMtwo : Can i create another publication for that table alone and create a subscription.? – user9516827 Jul 7 '19 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.