SQL Server Replication Initialize from backup

This article details the steps needed to setup transactional replication using a backup to initialize the subscriptions. The steps will take you from start to finish and will provide a general overview of the process. Note: Some other steps may be needed to make this work in all environments.

The scripts used below can be generated by using the New Publication Wizard in SSMS. Instead of creating the publication the last screen on the wizard gives you an option to Script it out. Once the script is generated you can tweak the SQL as needed to fit your requirements. The steps below will detail how to modify the script generated by the Wizard.

Highlevel Steps

  1. Setup the Publication using the GUI or TSQL Scripts, making sure you enable the initailize from backup option on the subscription options.
  2. Take a backup of the Publisher database after the publication is setup
  3. Restore the backup to the subscriber
  4. Add the subscription to the publisher using the ‘initialize with backup’ sync_type and specifiying the backupdevicetype and backupdevice name
  5. Add the Pull subscriptions to the subscriber database
Detailed Steps
Prep Steps
  1. In order to prepare for a synchronization from a backup you will need to restore a recent full backup on the Subscriber database
  2. Roll forward DIFF backups
  3. Apply necessary transaction logs. Leave the Subscriber database in NORECOVERY mode and keep applying the LOG Backups
Once you are ready to restore the Subscription database you will follow the steps below. Do NOT stop your transaction log restore job until you are ready to flip the switch.

Enable the Replication Database

This step may or may not be needed. This step assumes that you are setting up the publication from scratch. You must first enable replication on the publisher server.

  1. Execute the sp_replicationdboption stored procedure to setup a replication database for the target publisher.
    exec sp_replicationdboption @dbname = N'DBname', @optname = N'publish', @value = N'true'
    • @dbname = name of the database you are setting up
    • @optname = option you are enabling
    • @value = turn this option on
  2. Use the sp_addlogreader_agent stored procedure to add the Log Reader Agent
    exec [DBName].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1
    • @job_login = specifies what account to run the agent as
    • @job_password = specifies the password for the account
    • @publisher_security_mode = 0 indicates SQL Server authentication, 1 indicates Windows Authentication
  3. Use the sp_addqreader_agent stored procedure to add the Queue Reader Agent
exec [DBName].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1
    • @job_login = specifies what account to run the agent as
    • @job_password = specifies the password for the account
    • @frompublisher = 1 indicates that the procedure is being executed from the Publisher on the publication database

Adding the Transactional Publication

  1. Execute the sp_addpublication and sp_addpublication_snapshot stored procedures to add the Transactional Publication. Below are the crucial variables that need to be set. Please see BOL for more information.
    • Execute this in the publisher database.
exec sp_addpublication @publication = N'PublicationName', @description = N'Transactional publication of database ''DB Name'' from Publisher ''Server Name''.', @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'True', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'

exec sp_addpublication_snapshot @publication = N'Trash_TR', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1</pre>
  • @publication = Name of the publication
  • @description = description for your publication
  • @sync_method = syncrhonization mode for the publication. concurrent = produces native-mode bulk copy program output of all tables but does not lock tables during the snapshot. See BOL for other modes.
  • @retention = The retention period in hours for subscription activity. If a subscription is not active within the retention period it expires and is removed. If 0 the subscription will not expire and will not be removed
  • @allow_push = Determines if publication will allow push subscriptions
  • @allow_pull = Determines if the publication will allow pull subscriptions
  • @allow_anonymous = Determines if anonymous publications are allowed
  • @repl_freq = continuous provides output of all log-based transactions while snapshot produces only scheduled events
  • @status = specifies if publication data is available (active or inactive)
  • @allow_initialize_from_backup = Indicates if Subscribers can initialize a subscription to this publication from backup rather than an initial snapshot.
  • See BOL for other options
Grant access to the publication using the sp_grant_publication_access stored procedure
DECLARE @pub NVARCHAR(128) = N'Publication Name';
exec sp_grant_publication_access @publication = @pub, @login = N'sa'
exec sp_grant_publication_access @publication = @pub, @login = N'NT AUTHORITY\SYSTEM'
exec sp_grant_publication_access @publication = @pub, @login = N'DOMAIN\serviceaccount'
exec sp_grant_publication_access @publication = @pub, @login = N'NT SERVICE\SQLSERVERAGENT'
exec sp_grant_publication_access @publication = @pub, @login = N'NT SERVICE\MSSQLSERVER'
    • @pub = The name of your publication
    • @login = account you are granting access to

Adding the Transactional Articles

  1.  Use the sp_addarticle stored procedure to add each article.The following script shows the addition of a single article. You will have a script per article you are adding so you may have several here. If you setup the publication using the GUI these scripts will be executed automatically for you.
    exec sp_addarticle @publication = N'Publication_Name', @article = N'ArticleName', @source_owner = N'dbo', @source_object = N'ObjectName', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'DestinationObjectName', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboAddenda]', @del_cmd = N'CALL [sp_MSdel_dboAddenda]', @upd_cmd = N'SCALL [sp_MSupd_dboAddenda]'
    • @publication = Name of the publication the article belongs to
    • @article = Name of the Article
    • @source_owner = Owner of the source object
    • @source_object = Source Object name
    • @type = logbased – the default, indicates this is a log-based article. See BOL for other options.
    • @destination_table – name of the destination table
    • @status – 24 includes the column name in INSERT statement and uses parameterized statements. See BOL for other options
    • @xxx_cmd – ins, upd, and del command. Determines the command type to use when replicating the various DML statements.

Adding the Transactional Subscriptions

  1. At this point you are ready to flip the switch. Your subscriber database should be in NORECOVERY mode and you should have been applying transactions logs to keep the Subscriber database up to date.
  2. Disable the “Distribution clean up: distribution” job to make sure that no commands are deleted from MSRepl_commands
  3. Stop your transaction log restoring job at the subscriber if necessary (assumes you have been rolling Transaction Log backups to keep Subscriber database up to date)
  4. Cut one last backup on your Publisher Database (after you have setup the publication using the above steps). Note the name of the backup file. You will need it for the next step.
    • This backup can be a FULL, DIFF or Transaction Log backup
    • The restore procedure of course will differ based on the type of backup you are restoring
    • The key is to make sure you backup the publisher database AFTER you setup the replication publication using the steps above.
  5. Restore the Last Backup to the Subscriber database WITH RECOVERY
  6. Add the subscription to the publisher by executing the sp_addsubscription stored procedure on the Publisher. Modify your @backupdevicename to be the full path of your last transaction log backup.

    exec sp_addsubscription @publication = N'Publication Name', @subscriber = N'Subscriber Server', @destination_db = N'DBName',
    @subscription_type = N'Pull', @sync_type = N'initialize with backup', @article = N'all', @update_mode = N'read only', @subscriber_type = 0,@backupdevicetype='Disk', @backupdevicename='X:\Full Path to Backup file'
    • @publication = Set this to match your publication name from above
    • @subscriber = This will be the name of your Subscriber server
    • @destination_db = Set this to the destination Database
    • @sync_type=’initialize with backup’
    • @backupdevicetype = ‘Disk’
    • @backupdevicename = this will be the full path to the name of last transaction log backup or full backup you took of the publisher.
  7. Add the pull subscription to the Subscriber by executing the sp_addpullsubscription stored procedure on the Subscriber
    USE [DB Name] exec sp_addpullsubscription @publisher = N'Publisher Server', @publication = N'Publication Name', @publisher_db = N'Publisher DB', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 0
    exec sp_addpullsubscription_agent @publisher = N'Publisher Server', @publisher_db = N'DBName', @publication = N'Publication Name', @distributor = N'Distributor Server', @distributor_security_mode = 1, @distributor_login = N'', @distributor_password = N'', @enabled_for_syncmgr = N'False', @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 = 0, @active_end_date = 0, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0 GO
  8. Enable the “Distribution clean up: distribution” job
External Links