How to: Initialize a Transactional Subscription from Backup (Transactional Replication)

How to: Initialize a Transactional Subscription from a Backup (Replication Transact-SQL Programming)

It’s really a gigantic task to setup a transactional replication on database of size 1.2 TB.

As a new bee we will just go and setup transactional replication on any database and generate the snapshot without putting a thought that , how much time snapshot will take and amount of disk for snapshot folder .On the top of this DBA don’t put much brain in thinking what will happen to the network.

The stress-free way of setting up T-Replication for Gigantic Database is to initialize the subscription with Backup.

Here I will demonstrate the steps how we can setup T-Replication and initialize the Subscription with Backup.

1.Configure the distribution on the server as per you subscription. In my case I setup the distribution on the same server where I have my production Database.

2.Right Click the Replication Folder in Object Explorer and Select Publisher Properties. Select the Database from the list and check the Type of Replication to be used for replication.


3.Let’s setup the publication for the database. One way is to setup the replication using GUI with default settings. Add the articles which need to be added.

Below is the sample default script which gets generated using GUI Option.

Use [Bprompt_Partition_Prod]

exec sp_replicationdboption

       @dbname = N’BPrompt_Partition_Prod’,

       @optname = N’publish’,

       @value = N’true’


use [BPrompt_Partition_Prod]

exec [BPrompt_Partition_Prod].sys.sp_addlogreader_agent

      @job_login = N’Provide a Domain user’,

      @job_password = ‘@Jon_Login Password’,

      @publisher_security_mode = 0,

      @publisher_login = N’SQLLogin or Impersonating the Process Account’,

      @publisher_password = N’ ‘,

      @job_name = null


— Adding the transactional publication

use [BPrompt_Partition_Prod]

exec sp_addpublication

                  @publication = N’BPrompt_Prod_Pub’,

                  @description = N’Transactional publication of database ”BPrompt_Partition_Prod” from Publisher ”NL03-DF271DELL”.’,

                  @sync_method = N’concurrent’,

                  @retention = 0, @allow_push = N’true’,

                  @allow_pull = N’true’,

                  @allow_anonymous = N’false’,

                  @enabled_for_internet = N’false’,

                  @snapshot_in_defaultfolder = N’true’,

                  @compress_snapshot = N’false’,

                  @ftp_port = 21,

                  @ftp_login = N’anonymous’,

                  @allow_subscription_copy = N’false’,

                  @add_to_active_directory = N’false’,

                  @repl_freq = N’continuous’,

                  @status = N’active’,

                  @independent_agent = N’true’,

                  @immediate_sync = N’false’,

                  @allow_sync_tran = N’false’,

                  @autogen_sync_procs = N’false’,

                  @allow_queued_tran = N’false’,

                  @allow_dts = N’false’,

                  @replicate_ddl = 1,

                  @allow_initialize_from_backup = N’false’,

                  @enabled_for_p2p = N’false’,

                  @enabled_for_het_sub = N’false’


use [BPrompt_Partition_Prod]

exec sp_addarticle

       @publication = N’BPrompt_Prod_Pub’,

       @article = N’xyz’,

       @source_owner = N’dbo’,

       @source_object = N’xyz’,

       @type = N’logbased’,

       @description = null,

       @creation_script = null,

       @pre_creation_cmd = N’drop’,

       @schema_option = 0x000000000803509F,

       @identityrangemanagementoption = N’manual’,

       @destination_table = N’xyz’,

       @destination_owner = N’dbo’,

       @vertical_partition = N’false’,

       @ins_cmd = N’CALL sp_MSins_dboxyz’,

       @del_cmd = N’CALL sp_MSdel_dboxyz’,

       @upd_cmd = N’SCALL sp_MSupd_dboxyz’



4.For an existing publication, ensure that the publication supports the ability to initialize from backup by executing sp_helppublication (Transact-SQL) at the Publisher on the publication database. Note the value of allow_initialize_from_backup in the result set.

use [BPrompt_Partition_Prod]


EXEC sp_helppublication @publication = BPrompt_Prod_Pub
By default the value of allow_initialize_from_backup is set to 0.

To set the publication to support the ability to initialize from backup

EXEC sp_changepublication     


     @property = N’allow_initialize_from_backup’,   

     @value = True

This can also be achieved at the time of configuring the publication using the script.

  1. Now Backup the Publisher Database
  2. Restore the Database Backup at the Subscriber Server.
  3. Now Create the subscription using @sync_type = N’initialize with backup’
use [BPrompt_Partition_Prod]

exec sp_addsubscription

                   @publication = N’BPrompt_Prod_Pub’,

                   @subscriber = N’Subscriber Server’,

                   @destination_db = N’BPrompt_Partition_Prod_Rep’,

                   @subscription_type = N’Push’,

                   @sync_type = N’initialize with backup’,

                   @article = N’all’,

                   @update_mode = N’read only’,

                   @subscriber_type = 0,

                   @backupdevicetype =’disk’,

                   @backupdevicename =’\SharedDrivej$BPromptSubscriberDBBPrompt_Partition_Prod.BAK’

–Location of Backup file–              

exec sp_addpushsubscription_agent

                  @publication = N’BPrompt_Prod_Pub’,

                  @subscriber = N’SubscriberServer’,  —Name of the Subscribing Server

                  @subscriber_db = N’BPrompt_Partition_Prod_Rep’,

                  @job_login = N’Provide a Domain user’,

                  @job_password = Domain User Password’,

                  @subscriber_security_mode = 0,

                  @subscriber_login = N’SQLLogin or Impersonating the Process Account’,

                  @subscriber_password = ‘Password’,

                  @frequency_type = 64,

                  @frequency_interval = 0,

                  @frequency_relative_interval = 0,

                  @frequency_recurrence_factor = 0,

                  @frequency_subday = 0,

                  @frequency_subday_interval = 0,

                  @active_start_time_of_day = 0,

                  @active_end_time_of_day = 235959,

                  @active_start_date = 20100903,

                  @active_end_date = 99991231,

                  @enabled_for_syncmgr = N’False’,

                  @dts_package_location = N’Distributor’



Once Subscription is configured, Open the Replication Monitor and double click the Running Subscription.

You will see the Distributor agent will start doing the check sum validation for all the included article.

One thought on “How to: Initialize a Transactional Subscription from Backup (Transactional Replication)

  1. i am trying to use a back for settnig up T-Replication but i get following error in replication monitor and noting is getting replicated.

    Explicit value must be specified for identity column in table ‘AuditLog’ either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

    Thanks for writing such a detailed blog…

Leave a Reply

Your email address will not be published.