This blog demonstrates the detailed steps needed to set up transactional replication using a backup to initialize the subscriptions without taking the snapshot for all the articles, as we know the snapshot option is time-consuming for large databases.
It’s a huge task to set up a transactional replication for a very large SQL Server database. Generally, you will set up transactional replication on any database by generating the snapshot without considering how long a snapshot will take and the amount of disk for the snapshot folder. To avoid a very long time on creating an initial snapshot and reinitializing the Subscribers, in this blog, you will explore an easy way to initialize the subscriber from an SQL database backup.
To set up a transactional replication - First, you need to configure the Distributor. In this lab, I have already configured the Distributor, and following is the reference link on how to configure the Distributor.
• Create Publication.
• Modify Publication Properties.
• Backup the Publisher database.
• Restore the backup on the subscriber database.
• Create Subscription through T-SQL as the GUI doesn’t support initializing from a database backup.
Publisher Database: ABC_Pub
Select Articles to replicate:
Created publication- ABC_Pub_Bkp
Form T-SQL :
From GUI : On Publication Properties, select the Subscription Options and set “Allow initialization from backup files“ to “true” and Click Ok to save the change.
It is a must to disable the job before taking the backup as a next step. If not, you may experience the following error.
We can see that the performance shows excellent state and there is no Snapshot agent created in the entire process as we have used the backup file to initialize.
Initialization from database backup files resolves numerous problems when we try to synchronize large databases but creates a few of its own. These problems can be solved with some planning and hopefully, this article helps to simplify the process.
Use the Feedback tab to make any comments or ask questions. You can also start a conversation with us.