create procedure sys.sp_change_log_shipping_primary_database
(
    @database sysname   -- cannot be NULL
    ,@backup_directory nvarchar(500) = NULL
    ,@backup_share nvarchar(500) = NULL
    ,@backup_retention_period int = NULL
    ,@monitor_server_security_mode bit = NULL
    ,@monitor_server_login sysname = NULL
    ,@monitor_server_password sysname = NULL
    ,@backup_threshold int = NULL
    ,@threshold_alert int = NULL
    ,@threshold_alert_enabled bit = NULL
    ,@history_retention_period int = NULL
    ,@ignoreremotemonitor bit = 0
    ,@backup_compression tinyint = NULL --0 disabled, 1 enabled, 2 server default
)
as
begin
    set nocount on
    declare @retcode int
                ,@primary_id uniqueidentifier
                ,@monitor_server sysname
                ,@existing_security_mode int
    
    -- security check
    
    exec @retcode = sys.sp_MSlogshippingsysadmincheck
    if (@retcode != 0 or @@error != 0)
        return 1
    
    -- must be invoked from master db
    
    if (db_name() != N'master')
    begin
        raiserror(5001, 16,-1)
        return 1
    end

    
    -- Parameter Check: @backup_compression
    
    if @backup_compression is not null and @backup_compression not in (0, 1, 2)
    begin
        raiserror(21055, 16, -1, '@backup_compression','sp_change_log_shipping_primary_database')
        return 1
    end

    
    -- Does it exist
    
    select @primary_id = primary_id
            ,@monitor_server = monitor_server
            ,@existing_security_mode = monitor_server_security_mode
    from msdb.dbo.log_shipping_primary_databases
    where primary_database = @database
    if (@primary_id is null)
    begin
        raiserror(32010, 16, 2, @database)
        return 1
    end
    
    -- validate @threshold_alert
    
    if (@threshold_alert is not null and @threshold_alert != 14420)
    begin
        if not exists (select *
            from master.dbo.sysmessages where error = @threshold_alert)
        begin
            raiserror(32028, 16, 2, @threshold_alert)
            return 1
        end
    end
    
    -- refresh monitor link if the monitor credentials are have been specified
    
    if (upper(@monitor_server) != upper(@@servername)
        and (@monitor_server_security_mode is not null
                or @monitor_server_login is not null
                or @monitor_server_password is not null))
    begin
        
        -- check if only login credentials have changed
        
        if (@monitor_server_security_mode is not null)
            select @existing_security_mode = @monitor_server_security_mode
        
        -- create link for monitor server
        
        exec @retcode = msdb.sys.sp_MSprocesslogshipmonitorlink @mode = 1
                                        ,@monitor_server = @monitor_server
                                        ,@monitor_server_security_mode = @existing_security_mode
                                        ,@monitor_server_login = @monitor_server_login
                                        ,@monitor_server_password = @monitor_server_password
        if (@retcode != 0 or @@error != 0)
            return 1
    end
    
    -- start transaction
    
    begin tran sp_change_ls_pd
    save tran sp_change_ls_pd
    
    -- update log_shipping_primary_databases
    
    update msdb.dbo.log_shipping_primary_databases
    set backup_directory = case when (@backup_directory is null) then backup_directory else @backup_directory end
         ,backup_share = case when (@backup_share is null) then backup_share else @backup_share end
         ,backup_retention_period = case when (@backup_retention_period is null) then backup_retention_period else @backup_retention_period end
         ,monitor_server_security_mode = case when (@monitor_server_security_mode is null) then monitor_server_security_mode else @monitor_server_security_mode end
         ,backup_compression = case when (@backup_compression is null) then backup_compression else @backup_compression end
    where primary_id = @primary_id
    if (@@error != 0)
        goto UNDO
    
    -- commit
    
    commit tran
    
    -- Add a monitor metadata
    
    exec @retcode = sp_MSprocesslogshippingmonitorprimary @mode = 3
                ,@primary_id = @primary_id
                ,@primary_server = @@servername
                ,@monitor_server = @monitor_server
                ,@monitor_server_security_mode = @monitor_server_security_mode
                ,@backup_threshold = @backup_threshold
                ,@threshold_alert = @threshold_alert
                ,@threshold_alert_enabled = @threshold_alert_enabled
                ,@history_retention_period = @history_retention_period
                ,@ignoreremotemonitor = @ignoreremotemonitor
    if (@retcode != 0 or @@error != 0)
        return 1
    
    -- all done
    
    return 0

UNDO:
    rollback tran sp_change_ls_pd
    commit tran
    return 1
end