Transactional Replication - SQL Server

salilsingh-9961 346 Reputation points
2021-04-07T07:22:57.137+00:00

Hi Team,

I am having an on-premises sql server db which acts as a publisher to a subscriber (in transactional replication).
After i restore this db through a .bak file (this file has no information about transactional replication setting), my replication settings on server where on-premises sql server db is restore are gone. Under Replication folder, both publication and subscription get deleted, i need to manually set publication/deletion again.

Could you please let me know after restoring the .bak file to on-premises sql server db, how can i retain the transactional replication settings so that it works as earlier without having to do any change on the server.

A working example would be really helpful here.

Thanks,
Salil

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,607 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,546 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Cris Zhan-MSFT 6,601 Reputation points
    2021-04-08T06:02:07.137+00:00

    Hi @salilsingh-9961 ,

    The transaction replication technology is set at the SQL Server instance level (The component Publisher, Distributor, and Subscribers all acted by the database instances). Only backup/restore publication database (the database used when creating the publication) cannot migrate transactional replication related settings.

    If you need to migrate the publication database involved in transactional replication to another instance of SQL Server, then you need to reconfigure transactional replication on the new instance.

    1 person found this answer helpful.
  2. Cris Zhan-MSFT 6,601 Reputation points
    2021-04-08T07:06:47.32+00:00

    Hi @salilsingh-9961 ,

    >... Just wanted to sure I am able to convey the issue correctly to you.....

    Thank you for your patient explanation. Yes, I understand your question now. Again, this is by design, and you cannot restore backups of other databases to the publisher database (publication database) that is already in a replication topology. In this case, you need to reconfigure publication and subscription.

    Replication only supports restoring replicated databases to the same server and database from which the backup was created.
    https://learn.microsoft.com/en-us/sql/relational-databases/replication/administration/back-up-and-restore-replicated-databases?view=sql-server-ver15

    Best Regards,
    Cris

    1 person found this answer helpful.
  3. Erland Sommarskog 100.8K Reputation points MVP
    2021-04-08T21:26:29.74+00:00

    I just like to add one thing to the answers given by Cris and Anurag: Assume for a moment that replication settings were retained, what would you expect to happen in this case? The idea with replication is that subscribers are kept in sync asynchronously with the publisher. Now, you wipe out that publisher and restore a different database, which has different data. And it doesn't stop there: the schema could also be different, rending the replication articles useless.

    So it would not be meaningful to retain the replication settings, but the only reasonable is to throw them away.

    1 person found this answer helpful.
    0 comments
  4. Anurag Sharma 17,566 Reputation points
    2021-04-08T06:53:32.597+00:00

    Hi @salilsingh-9961 , as rightly pointed by @Cris Zhan-MSFT , we need to reconfigure the replication. You can read more on below article:

    Restoring Databases Involved in Replication
    "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 re-create all publications and subscriptions after backups are restored."

    This article also talks about strategies if we are restoring the database in case of replication.