Remote Replication - Initialize from Backup

• lilywhites

  SSCrazy

  Points: 2732

  More actions

  February 6, 2015 at 4:17 am

  #314393

  Hello,

  We are testing a replication setup that has publications on the production database server in our hosted environment. These offload the changes to a remote distribution database in hosting also. We then connect over VPN from our office and create the subscriptions down here.

  Replication monitor seems reasonably happy....apart from 1 issue....i cannot initialize the subscription from a backup. The method i am using is (

  On primary database server (644015-SQL1):

  - exec sp_replicationdboption

  @dbname = N'IM_Reports',

  @optname = N'publish',

  @value = N'true'

  - exec sp_addlogreader_agent

  @job_login = <<domain account>>,

  @job_password = 'xxxxxxxxxxxxxxxxx',

  @publisher_security_mode = 1

  -exec sp_addpublication

  @publication = N'IM_Reports',

  @description = N'Transactional publication of database ''IM_Reports'' from Publisher ''644015-SQL1''.',

  @sync_method = N'concurrent',

  @retention = 0,

  @allow_push = N'false',

  @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'true',

  @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_grant_publication_access for relevant accounts

  - exec sp_addarticle

  then backup the database

  - exec sp_addsubscription

  @publication = N'IM_Reports',

  @subscriber = N'VORTEX',

  @destination_db = N'IM_Reports',

  @subscription_type = N'Pull',

  @sync_type = N'initialize with backup',

  @backupdevicetype = N'disk',

  @backupdevicename = 'F:\MSSQL\BACKUP\IM_Reports.bak',

  @article = N'all',

  @update_mode = N'read only',

  @subscriber_type = 0

  On secondary database server:

  - exec sp_addpullsubscription

  @publisher = N'644015-SQL1',

  @publication = N'IM_Reports',

  @publisher_db = N'IM_Reports',

  @independent_agent = N'True',

  @subscription_type = N'pull',

  @description = N'',

  @update_mode = N'read only',

  @immediate_sync = 1

  - exec sp_addpullsubscription_agent

  @publisher = N'644015-SQL1',

  @publisher_db = N'IM_Reports',

  @publication = N'IM_Reports',

  @distributor = N'643951-REPORTS1',

  @distributor_security_mode = 1,

  @distributor_login = 'SQL_REPL', (local sql login)

  @distributor_password = 'xxxxxxxxxxxx',

  @enabled_for_syncmgr = N'False',

  @frequency_type = 4,

  @frequency_interval = 1,

  @frequency_relative_interval = 1,

  @frequency_recurrence_factor = 0,

  @frequency_subday = 4,

  @frequency_subday_interval = 10,

  @active_start_time_of_day = 0,

  @active_end_time_of_day = 235959,

  @active_start_date = 0,

  @active_end_date = 0,

  @alt_snapshot_folder = N'\\643951-reports1\repldata',

  @working_directory = N'\\643951-reports1\repldata',

  @use_ftp = N'False',

  @job_login = 'SQL_REPL', (local sql login)

  @job_password = 'xxxxxxxxxxxx',

  @publication_type = 0

  can anyone see where i might be going wrong?? all the options are correct but may be a permissions/access thing??

  thanks in advance

• lmarkum

  SSCertifiable

  Points: 7989

  More actions

  February 8, 2015 at 10:30 pm

  #1776292

  So what type of error are you getting? What is the indication that there is a problem?