create procedure sys.sp_MSprocesslogshippingjob
(
@type tinyint -- 1 = backup, 2 = copy, 3 = restore, 4 = alert
,@mode tinyint -- 1 = create, 2 = drop
,@jobid uniqueidentifier = NULL output -- for create jobs it returns the new job, for drop jobs it specifies the job
,@job_name sysname = NULL -- cannot be NULL when creating
,@description nvarchar(512) = NULL -- cannot be NULL when creating
,@command nvarchar(3200) = NULL -- cannot be NULL when creating
,@overwrite bit = 0
)
as
begin
set nocount on
declare @retcode int
,@category sysname
,@category_id int
,@stepname sysname
,@subsystem sysname
,@enabled tinyint
,@schedule_name sysname
,@schedule_id int
,@sa_login sysname
SELECT @sa_login = SUSER_SNAME(0x01)
-- check to make sure the server has not been dropped
if @@servername is null
begin
raiserror(32053, 16, 1)
return 1
end
-- validate @type and @mode
if (@type not in (1,2,3,4)) or (@mode not in (1,2))
return 1
-- process based on @mode
if (@mode = 1)
begin
-- @mode is create - see if job exists with same name
select @jobid = job_id
,@category_id = category_id
from msdb.dbo.sysjobs_view
where name = @job_name
if (@jobid is not null)
begin
-- Job with same name exists
-- If this is a logshipping job - we will drop it before creating
-- If this is not a logshipping job - return error (if override is not set)
if (@category_id != 6)
begin
if (@overwrite = 0)
begin
raiserror(32022, 16, 1, @job_name)
return 1
end
end
end
end
else
begin
-- @mode is drop - @jobid cannot be null
if (@jobid is null)
return 1
end
-- if @jobid is specified, we need to drop it first
if (@jobid is not null)
begin
-- ignore errors during job deletion - the job may not exist
begin try
exec msdb.dbo.sp_delete_job @job_id = @jobid
end try
begin catch
select @retcode = 0
end catch
select @jobid = null
end
-- Proceed if we are creating
if (@mode = 2)
return
-- initialize based on type
if (@job_name is null or @description is null or @command is null)
return 1
select @category = N'Log Shipping'
,@subsystem = case when (@type in (1,2,3)) then N'CMDEXEC' else N'TSQL' end
,@stepname = case when (@type = 1) then isnull(formatmessage(32004), N'Logshipping backup log job step.')
when (@type = 2) then isnull(formatmessage(32005), N'Logshipping copy job step.')
when (@type = 3) then isnull(formatmessage(32006), N'Logshipping restore job step.')
else isnull(formatmessage(32020), N'Logshipping alert job step.') end
,@enabled = case when (@type in (1,2,3)) then 0 else 1 end
-- Add job
exec @retcode = msdb.dbo.sp_add_job @job_name=@job_name
,@enabled=@enabled
,@notify_level_eventlog=0
,@notify_level_email=0
,@notify_level_netsend=0
,@notify_level_page=0
,@delete_level=0
,@description=@description
,@category_name=@category
,@owner_login_name=@sa_login
,@job_id = @jobid output
if (@@error != 0 OR @retcode != 0)
return 1
-- Add job step
exec @retcode = msdb.dbo.sp_add_jobstep @job_id=@jobid
,@step_name=@stepname
,@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=0
,@retry_interval=0
,@os_run_priority=0
,@subsystem=@subsystem
,@command=@command
,@flags=32 -- avoid output truncation
if (@@error != 0 OR @retcode != 0)
return 1
exec @retcode = msdb.dbo.sp_update_job @job_id = @jobid, @start_step_id = 1
if (@@error != 0 OR @retcode != 0)
return 1
-- Do we need this ?
exec msdb.dbo.sp_add_jobserver @job_id = @jobid
-- special processing for alert job
if (@type = 4)
begin
-- check if schedule exists
select @schedule_name = isnull(formatmessage(32021), N'Log shipping alert job schedule.')
select @schedule_id = schedule_id
from msdb.dbo.sysschedules_localserver_view
where name = @schedule_name
if (@schedule_id is null)
begin
-- add the schedule
exec @retcode = msdb.dbo.sp_add_schedule @schedule_name = @schedule_name
,@freq_type = 4 -- daily
,@freq_interval=1 -- everyday
,@freq_subday_type = 4 -- minutes
,@freq_subday_interval = 2 -- every 2 minutes
,@owner_login_name=@sa_login
,@schedule_id = @schedule_id output
if (@@error != 0 OR @retcode != 0)
return 1
end
-- attach the schedule
exec @retcode = msdb.dbo.sp_attach_schedule @job_id = @jobid, @schedule_id = @schedule_id
if (@@error != 0 OR @retcode != 0)
return 1
end
-- all done
return 0
end