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