Post reply

Replication fails after a restore

  • 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 !!!

  • January 8, 2014 at 6:40 am

    #1678401

    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.

  • January 8, 2014 at 7:37 am

    #1678417

    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.

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

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