Home > Initialize a Transactional Subscription from Backup, SQL Server Replication > How to: Initialize a Transactional Subscription from Backup (Transactional Replication)

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-DF271\DELL”.’,

                  @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 =’\\SharedDrive\j$\BPromptSubscriberDB\BPrompt_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.

  1. SKJ
    October 22, 2010 at 4:58 pm

    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…

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: