Initializing replication for a large database

by Rackspace Technology Staff

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. 

Setting up a transactional replication for an extensive SQL Server database is a huge task. 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 to create an initial snapshot and reinitialize the Subscribers, in this blog, you will explore an easy way to initialize the subscriber from an SQL database backup.


Let’s get into the detailed steps of the setup:

To set up a transactional replication, you must first configure the Distributor. 
In this lab, I have already configured the Distributor, and the following is the reference link on how to configure the Distributor.

Process Steps Overview

  • Create Publication. 
  • Modify Publication Properties.  
  • Back up the Publisher database.
  • Restore the backup on the subscriber database.  
  • Create a Subscription through T-SQL as the GUI doesn’t support initializing from a database backup.

In this scenario, Transactional Replication is going to be configured with the following details:

  • Publisher + Distributor: Node1
  • Subscriber: Node2
  • Publisher Database: ABC_Pub
  • Subscriber Database: ABC_Sub
  • Publication: ABC_Pub_Bkp
  • Subscription: ABC_Sub_Bkp


Step 1: Create the publication by choosing the database for replication. 
Publisher Database: ABC_Pub

DB Replication

Step 2: Select the type of publication and the tables to participate in replication. 

DB Replication Picture 2

Select Articles to replicate: 

DB Replication Picture 3

Step 3: In our case, we are using a database backup file instead of a snapshot so we will leave them blank and click on next, as shown in the following snapshot. 

DB Replication Picture 4

 Created publication- ABC_Pub_Bkp 

DB replication Picture 5

Step 4: Set ‘Allow initialization from backup files’ to true. 

From T-SQL :

DB replication Picture 7

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.

DB replication Picture 7

  Disable the Distribution cleanup SQL Server job (right-click on the job name and click Disable):

DB replication part 8

It is a must to disable the job before taking the backup as a next step. If not, you may experience the following error 

DB replication Picture 9
DB replication Picture 10

Execute sp_addsubscription on the Publication database by passing the mentioned parameters in the following snapshot. 

DB Replication Picture 11

After all the other steps are completed, enable the Distribution cleanup job.

DB replication Picture 12

Now check the replication status using SSMS under replication → Replication Monitor.


We can see that the performance shows an excellent state and there is no Snapshot agent created in the entire process as we have used the backup file to initialize.  

DB replication Picture 12
DB replication Picture 13


Learn more about Rackspace Database Services