locked
Migrating Replicated Databases RRS feed

  • Question

  • Hello,

    Old server is SQL2k12 ent edition with a few databases having transactional replication. One Publisher (ServerA) and another server is both a distributor and a subscriber (ServerB). Migrating to another SQL2k16 server. Same setup ServerC will be the publisher and ServerD will be both distributor and subscriber. Here is my final migration plan:

    1. Stop replication jobs inside ServerB (since it has both distribution and subscription).

    2. Do a full backup of the replicated database inside ServerA.

    3. Stop replication jobs inside ServerD.

    4. Do a overwrite restore inside ServerC.

    5. Once the restore is complete, generate a new snapshot and re-synch the replicated database from ServerC to ServerD.

    6. Start all the replication jobs inside ServerD.

    Of course, there are other steps such as checking database access etc. I just want to confirm whether steps mentioned above relating to backup and restore from the old server to the new server is correct or not or needs changes.

    Will greatly appreciate your quick response.

    Thanks.

    Victor 


    Victor

    Friday, July 26, 2019 9:21 PM

Answers

  • Hi vr123,

    Replication supports restoring replicated databases to the same server and database from which the backup was created. If you restore a backup of a replicated database to another server or database, replication settings cannot be preserved. In this case, you must recreate all publications and subscriptions after backups are restored.

    Replicated databases and their associated system databases should be backed up regularly. Back up the following databases:
    • The publication database at the Publisher
    • The distribution database at the Distributor
    • The subscription database at each Subscriber
    • The master and msdb system databases at the Publisher, Distributor and all Subscribers. These databases should be backed up at the same time as each other and the relevant replication database. For example, back up the master and msdbdatabases at the Publisher at the same time you back up the publication database. If the publication database is restored, ensure that the master and msdb database are consistent with the publication database in terms of replication configuration and settings.

    Please refer to Back Up and Restore Replicated Databases and Upgrade or patch replicated databases to get more information.

    Hope this could help you.

    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by vr123 Monday, July 29, 2019 5:04 PM
    Monday, July 29, 2019 6:45 AM

All replies

  • Hi vr123,

    Replication supports restoring replicated databases to the same server and database from which the backup was created. If you restore a backup of a replicated database to another server or database, replication settings cannot be preserved. In this case, you must recreate all publications and subscriptions after backups are restored.

    Replicated databases and their associated system databases should be backed up regularly. Back up the following databases:
    • The publication database at the Publisher
    • The distribution database at the Distributor
    • The subscription database at each Subscriber
    • The master and msdb system databases at the Publisher, Distributor and all Subscribers. These databases should be backed up at the same time as each other and the relevant replication database. For example, back up the master and msdbdatabases at the Publisher at the same time you back up the publication database. If the publication database is restored, ensure that the master and msdb database are consistent with the publication database in terms of replication configuration and settings.

    Please refer to Back Up and Restore Replicated Databases and Upgrade or patch replicated databases to get more information.

    Hope this could help you.

    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by vr123 Monday, July 29, 2019 5:04 PM
    Monday, July 29, 2019 6:45 AM
  • Thanks Cathy.

    Victor


    Victor

    Monday, July 29, 2019 5:05 PM