Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Create SQL Server replication pull jobs
USE [msdb];
GO
DECLARE @iPublisher AS sysname;
DECLARE @iDistributor AS sysname;
DECLARE @iSubscriber AS sysname;
DECLARE @iPublication AS sysname;
DECLARE @iPublicationDB AS sysname;
DECLARE @iSubscriptionDB AS sysname;
DECLARE @job_name NVARCHAR(400);
DECLARE @cmd NVARCHAR(4000);
DECLARE @alt_snap_path NVARCHAR(512);
DECLARE @jobId BINARY(16);
DECLARE @programPath NVARCHAR(512);
SET @iPublisher = N'PublisherName';
SET @iPublication = N'PublicationName';
SET @iPublicationDB = N'PublisherDatabase';
SET @iSubscriptionDB = 'SubscriptionDatabase';
SET @iSubscriber = @@SERVERNAME;
SET @iDistributor = 'DistributorName';
SET @programPath = 'C:\Program Files\Microsoft SQL Server\120\COM\DISTRIB.EXE' --This will differ depending on version and installation preference.
SET @alt_snap_path = ''; --\\unc\snapshot\
SET @cmd
= N'"' + @programPath + '" -Subscriber [' + @iSubscriber + '] -SubscriberDB ['
+ @iSubscriptionDB + '] -SubscriberSecurityMode 1 -Publication [' + @iPublication + '] -Publisher ['
+ @iPublisher + '] -PublisherDB [' + @iPublicationDB + '] -Distributor [' + @iDistributor
+ '] -DistributorSecurityMode 1 -Continuous -SubscriptionType 1 ' + @alt_snap_path + '';
SET @job_name = N'Replication: ' + @iPublisher + '-' + @iPublicationDB + '-' + @iPublication;
IF EXISTS(SELECT 1 FROM msdb.dbo.sysjobs WHERE name = @job_name)
BEGIN
EXEC msdb.dbo.sp_delete_job @job_name = @job_name;
END
BEGIN TRANSACTION;
DECLARE @ReturnCode INT;
SELECT @ReturnCode = 0;
IF NOT EXISTS
(
SELECT name
FROM msdb.dbo.syscategories
WHERE name = N'REPL-Distribution'
AND category_class = 1
)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB',
@type = N'LOCAL',
@name = N'REPL-Distribution';
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback;
END;
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = @job_name,
@enabled = 0,
@notify_level_eventlog = 0,
@notify_level_email = 0,
@notify_level_netsend = 0,
@notify_level_page = 0,
@delete_level = 0,
@description = N'Manually created job for syncing replication. Has to be manual because SQL Server doesn''t create the jobs for SQL Web servers.',
@category_name = N'REPL-Distribution',
@owner_login_name = N'sa',
@job_id = @jobId OUTPUT;
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId,
@step_name = N'Run Agent',
@step_id = 1,
@cmdexec_success_code = 0,
@on_success_action = 1,
@on_success_step_id = 0,
@on_fail_action = 2,
@on_fail_step_id = 0,
@retry_attempts = 2147483647,
@retry_interval = 1,
@os_run_priority = 0,
@subsystem = N'CmdExec',
@command = @cmd,
@flags = 0;
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId,
@start_step_id = 1;
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId,
@name = N'Replication agent schedule.',
@enabled = 1,
@freq_type = 64,
@freq_interval = 0,
@freq_subday_type = 0,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_start_date = 20150114,
@active_end_date = 99991231,
@active_start_time = 30000,
@active_end_time = 235959,
@schedule_uid = N'9f053472-701e-4de3-ba90-f620e2970def';
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback;
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId,
@server_name = N'(local)';
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback;
COMMIT TRANSACTION;
GOTO EndSave;
QuitWithRollback:
IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION;
EndSave:
--Enable and start the jobs
EXEC msdb.dbo.sp_update_job
@job_name = @job_name,
@enabled = 1 ;
EXEC msdb.dbo.sp_start_job @job_name = @job_name;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment