I need the best way for the scenario because of which I got irritated.
I do have 3 Production database Servers Srv1, Srv2 and Srv3.
I configure transactional replication among these servers.
Srv1 is Publisher and Srv2 and Srv3 are subscribers.
On production server everything running fine without any problem.
I have QA environment same as production replication configured.
On request of QA team, I need to refresh QA with production databases weekly (almost), which cause to fail replication in QA environment.
Because I have 1000 tables dropping and re-creating publications takes too much time on Srv1.
If I refresh database without dropping publication and recreate publication using backup script I get The process could not execute ‘sp_repldone/sp_replcounters’
Thanks
Best Answer
What @Kin said is correct but may be time-consuming (if you have really big tables involved in replication) when you need to run Snapshot.
Here are the steps that you can omit the snapshot
Script out QA replication and change @sync_type parameter value (highlighed below)
exec sp_addsubscription @sync_type = 'Replication Support Only', ... (other paramaters should remain the same)
Remove replication (you can write a script to do this, note, first remove subscriptions then publisher, but do NOT remove distributor)
Restore PROD db on QA publisher
Restore PROD db on QA subscribers.
Run the replication setup script on QA publisher
Test your replication by inserting/updating a few test records and see whether the replication works as expected.
This link talks about another ways that can avoid snapshot and may be of help (but I never tried myself)
https://blogs.msdn.microsoft.com/repltalk/2010/03/16/deep-dive-on-initialize-from-backup-for-transactional-replication/
1st step of scripting out QA replication is one time work and you can actually automate all these steps (after step 1 modification is done) via t-sql scripts, and using sqlcmd.exe or powershell to drive the t-sql scripts. I used this way frequently before.