Replication fails after a restore

Question

Replication fails after a restore

PearlJammer1

SSCrazy Eights

Points: 8480
More actions
January 7, 2014 at 9:27 am

#302912

We use transactional replication in our dev environment. Any time I need to restore the databases involved in the replication (for purposes of keeping the dev environment in sync with live) then the publications disappear and I need to re-create them from the scripts that I created before the restore.
Is there a way of keeping the replication 'intact' so that after a restore it will continue to work.
Or is the alternative to place the scripts to create the replication objects into some kind of automated job ?
Any ideas as this is currently a proper pain in the backside to keep having to re-create the publications after each and every restore !!!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

LightVader Hall of Fame Points: 3810 More actions · Answer 1

PearlJammer1 (1/7/2014)
We use transactional replication in our dev environment. Any time I need to restore the databases involved in the replication (for purposes of keeping the dev environment in sync with live) then the publications disappear and I need to re-create them from the scripts that I created before the restore.
Is there a way of keeping the replication 'intact' so that after a restore it will continue to work.
Or is the alternative to place the scripts to create the replication objects into some kind of automated job ?
Any ideas as this is currently a proper pain in the backside to keep having to re-create the publications after each and every restore !!!

I haven't used it, but are you doing the restore with the "WITH KEEP_REPLICATION" option?

The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 2

PearlJammer1 (1/7/2014)
(for purposes of keeping the dev environment in sync with live)

If you are restoring the db from production, using KEEP_REPLICATION will fail as the distributor and subscribers will be different. You'll get errors.

Its much cleaner to restore it without replication and have create an automate process to the script and recreate the publications. I can recommend SQLPSX for powershell. It works well although there are a few "flaky" areas which you may have to correct or change you methods.