question

salilsingh-9961 avatar image
0 Votes"
salilsingh-9961 asked ErlandSommarskog answered

Transactional Replication - SQL Server

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-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Team,

Could you please reply on above.

Thanks,
Salil

0 Votes 0 ·
Criszhan-msft avatar image
1 Vote"
Criszhan-msft answered salilsingh-9961 commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Criszhan-msft ,

My issue is that on a SQL Server (say name is A) where I have applied transactional replication, if I am restoring a backup (.bak file) to the Publisher database on same server, my replication settings on same server are deleted.
Please note .bak file that I restored to Publisher database on above server is not having any replication setting and is coming from a different server.

As mentioned earlier, how can I retain replication settings on server A, once .bak file is restored to its publisher database.

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

Could you please let me know the answer.

Thanks,
Salil

1 Vote 1 ·
AnuragSharma-MSFT avatar image
0 Votes"
AnuragSharma-MSFT answered salilsingh-9961 commented

Hi @salilsingh-9961, as rightly pointed by @Criszhan-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.




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AnuragSharma-MSFT ,

Looks like I am unable to convey exact issue I am facing here.

My issue is on an SQL Server (say name is A) where replication is already applied, if I am restoring a .bak file to publisher db of same server (A), on server A those already existing replication settings are deleted. Please note .bak file that I restored to Publisher database on above server A is not having any replication setting and is coming from a different server.

I am not trying to implement replication to a new server through restoring a database on that server,
Issue is on a server A where replication is already applied, if i am restoring .bak file to its publisher database, replication setting on server A are deleted, how to retain replication settings on server A if a bak file is restore to its publisher db.

Hope i am able to convey the issue i am facing here.

Thanks,
Salil

0 Votes 0 ·
Criszhan-msft avatar image
1 Vote"
Criszhan-msft answered Criszhan-msft commented

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://docs.microsoft.com/en-us/sql/relational-databases/replication/administration/back-up-and-restore-replicated-databases?view=sql-server-ver15

Best Regards,
Cris

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Criszhan-msft ,

Just to be sure, I able to restore a .bak file to publisher database on server A successfully.
After restoring the file to publisher database, replication setting on server A are deleted.
As you mentioned above, understanding is once a .bak file is restored to a publisher database on a server
where replication is already applied, replication setting on same server will always get deleted, we can not retain that setting after above restore.
We need to configure that replication setting again on same server.

Please let me know incase my above understanding is incorrect.

Thanks,
Salil

0 Votes 0 ·

Just to be sure, I able to restore a .bak file to publisher database on server A successfully.

Because this file is not backed up from the publisher database itself, once it is restored to the publisher database, the replication settings and metadata related to the publisher database will be automatically deleted, so you need to re-create the publication and subscription.

1 Vote 1 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.