1

I have a transactional replication which was initially synced from backup. Now I need to add a new table which is really big so we have decided to backup and restore a fresh copy of the db to subscriber to re-intializing it.

My question is, in this scenario should I be dropping the subscription, backup restore and then re-add the subscription? is that the correct way or is there any other way of going about it?

Thanks

  • Yes, drop the subscription. Disable backups on publisher, Backup the DB on pub, Restore at sub and re-add the subscription using that backup and finally re-enable backups on pub. – SQLPRODDBA Mar 21 '17 at 6:42
0

So I had a similar situation. I had a HUGE publication that took up to 36 hours to initialize with a snapshot. When I had to move the subscriber to new hardware this just wasn't ideal. So I used a backup to initialize the sub. Of course I knew that we'd have to add articles eventually and I did what this article said to do in Option 1 (option 2 not available as there was no way I was going to be able to stop all activity on the subscriber short of a disaster).

Add Articles to Subscription initialized with backup

So I added a new publication to the same publisher database and then added the subscription to the subscriber. Since the initial snapshot is very small (only 4 tables) it sych'd up very quickly. Subsequent table adds to the publication are done on the new publication and snapshots generally are only of new tables, not entire publication. Is working very well so far .

| improve this answer | |

Your Answer

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

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