Article From:https://www.cnblogs.com/Amaranthus/p/9686084.html
Reference resources:
http://www.sqlservercentral.com/articles/Replication/117265/
Precondition:
    Prepare a computer with the same name as the previous distribution database. And install SQL Server
 
Recovery steps:
On the new distribution server
1.Configure distribution to increase the previous publisher
2.Using backup to restore distribution database
3.The creation of job, mainly snapshots agent and logreadagent, can be copied directly from the old distribution.
3.Modify publisher properties
  EXEC sp_changedistpublisher  'TESTSYNCDBSERV', 'active', 'true' 
4.Modify publisher_id in [MSpublisher_databases] as server_id of publisher server in sys.servers
UPDATE t  set t. publisher_id  = 2  from dbo.[MSpublisher_databases]  t WHERE id=2   
5.Modify publisher_id in [MSpublications] as server_id of publisher server in sys.servers
UPDATE t set t. publisher_id  = 2 from dbo.MSpublications  t WHERE publication_id=2
6.Modify the publisher_id in [MSdistribution_agents] to be the server_id of the publisher server in sys. servers and the subscriber_id to be the subscriber server in sys.Server_id in servers can be created directly without subscription.
 EXEC master.dbo.sp_addlinkedserver @server = N'TESTSYNCRPTSERV', @srvproduct=N'SQL Server'
 EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TESTSYNCRPTSERV',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
 update dbo.MSdistribution_agents SET publisher_id=2,subscriber_id=3 WHERE id = 4
7.Modify the publisher_id in [MS subscriptions] to be the server_id of the publisher server in sys. servers and the subscriber_id to be the subscriber server in sys. server.Server_id in s can be created directly without subscription.
update dbo.MSsubscriptions  SET publisher_id =2,subscriber_id=3 WHERE publication_id=2
8.Modify the publish_id of logreadagent, snapshotagent and the corresponding job information.
 update [MSlogreader_agents]  SET publisher_id =2  WHERE id = 2
 update [MSsnapshot_agents]  SET publisher_id =2  WHERE id = 2
 update t
  set t.job_id = sj.job_id, t.job_step_uid = sjs.step_uid
 from dbo.MSlogreader_agents t
  left join dbo.MSreplication_monitordata   sm on sm.agent_name = t.name
  left join msdb.dbo.sysjobs sj  on sj.name = t.name
  left join msdb.dbo.sysjobsteps sjs on sj.job_id = sjs.job_id and sjs.step_id = 2
 update t
  set t.job_id = sj.job_id, t.job_step_uid = sjs.step_uid
 from dbo.[MSsnapshot_agents] t
  left join dbo.MSreplication_monitordata   sm on sm.agent_name = t.name
  left join msdb.dbo.sysjobs sj  on sj.name = t.name
  left join msdb.dbo.sysjobsteps sjs on sj.job_id = sjs.job_id and sjs.step_id = 2
 
Run on publication
Modify the ID of job in syspublications and syssubscriptions in the publish database
update [dbo].[syspublications] set snapshot_jobid=0xD2E257AFD287304CA2FC14F3D7C1AECF
update [dbo].[syssubscriptions] set distribution_jobid=0x0E79D3A2A36F73409DB3F4261723F37A

 

 
 

Leave a Reply

Your email address will not be published. Required fields are marked *