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 only takes a minute to sign up.
Sign up to join this community
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
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 .
