create procedure sys.sp_MSproxylogshippingmonitorrefresh
(
    @agent_id uniqueidentifier     -- primary/secondary ID
    ,@agent_type tinyint              -- 0 = Backup, 1 = Copy, 2 = Restore
    ,@database sysname = NULL  -- primary/secondary database
    ,@mode tinyint                      -- 1 = refresh, 2 = delete
    ,@monitor_server sysname
)
with execute as 'dbo'
as
begin
    set nocount on
    declare @retcode int
                ,@linkcmd nvarchar(4000)
    
    -- prepare query
    
    select @retcode = 0
    
    -- cleanup metadata first
    
    if (@agent_type = 0)
    begin
        
        -- delete Primary monitor data
        
        select @linkcmd = quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.msdb.sys.sp_processlogshippingmonitorprimary'
        begin try
            exec @retcode = @linkcmd @mode = 2
                                    ,@primary_id = @agent_id
                                    ,@primary_server = @@servername
                                    ,@monitor_server = @monitor_server
                                    ,@monitor_server_security_mode = 0
        end try
        begin catch
            select @retcode = 1
        end catch
    end
    else
    begin
        
        -- delete Secondary monitor data
        
        select @linkcmd = quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.msdb.sys.sp_processlogshippingmonitorsecondary'
        begin try
            exec @retcode = @linkcmd @mode = 2
                ,@secondary_server = @@servername
                ,@secondary_database = @database
                ,@secondary_id = @agent_id
                ,@monitor_server = @monitor_server
                ,@monitor_server_security_mode = 0
        end try
        begin catch
            select @retcode = 1
        end catch
    end
    if (@retcode != 0 or @@error != 0)
        return 1
    
    -- If we deleting - then we are done
    
    if (@mode = 2)
        return @retcode
    
    -- refresh now
    
    if (@agent_type = 0)
    begin
        
        -- refresh primary data
        
        select @linkcmd = N'insert into ' + quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.msdb.dbo.log_shipping_monitor_primary '
            + N'(primary_id,primary_server,primary_database,backup_threshold,threshold_alert,threshold_alert_enabled,last_backup_file,last_backup_date,last_backup_date_utc,history_retention_period) '
            + N'select primary_id,primary_server,primary_database,backup_threshold,threshold_alert,threshold_alert_enabled,last_backup_file,last_backup_date,last_backup_date_utc,history_retention_period '
            + N'from msdb.dbo.log_shipping_monitor_primary where primary_id = N''' + CAST(@agent_id as nvarchar(50)) +  ''' '
        begin try
            EXEC(@linkcmd)
        end try
        begin catch
            select @retcode = 1
        end catch
    end
    else
    begin
        
        -- refresh secondary data
        
        select @linkcmd = N'insert into ' + quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.msdb.dbo.log_shipping_monitor_secondary '
            + N'(secondary_server,secondary_database,secondary_id,primary_server,primary_database,restore_threshold,threshold_alert,threshold_alert_enabled'
            + N',last_copied_file,last_copied_date, last_copied_date_utc, last_restored_file,last_restored_date, last_restored_date_utc, last_restored_latency, history_retention_period) '
            + N'select secondary_server,secondary_database,secondary_id,primary_server,primary_database,restore_threshold,threshold_alert,threshold_alert_enabled'
            + N',last_copied_file,last_copied_date, last_copied_date_utc, last_restored_file,last_restored_date, last_restored_date_utc, last_restored_latency, history_retention_period '
            + N'from msdb.dbo.log_shipping_monitor_secondary where secondary_server = @@servername and secondary_id = N''' + CAST(@agent_id as nvarchar(50)) +  ''' '
            if (@database is not null)
            BEGIN
                SELECT @linkcmd = @linkcmd + N' and secondary_database = ''' + sys.fn_replreplacesinglequote(CAST(@database as nvarchar(128))) + ''' '
            END
        begin try
            EXEC(@linkcmd)
        end try
        begin catch
            select @retcode = 1
        end catch
    end
    if @@error != 0 OR @retcode != 0
        return 1
    
    -- refresh history
    
    select @linkcmd = N'insert into ' + quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.msdb.dbo.log_shipping_monitor_history_detail '
        + N'(agent_id,agent_type,session_id,database_name,session_status,log_time,log_time_utc,message) '
        + N'select agent_id,agent_type,session_id,database_name,session_status,log_time,log_time_utc,message '
        + N'from msdb.dbo.log_shipping_monitor_history_detail where agent_id = N''' + CAST(@agent_id as nvarchar(50)) +  ''' '
    begin try
        EXEC(@linkcmd)
    end try
    begin catch
        select @retcode = 1
    end catch
    if @@error != 0 OR @retcode != 0
        return 1
    
    -- refresh error
    
    select @linkcmd = N'insert into ' + quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.msdb.dbo.log_shipping_monitor_error_detail '
        + N'(agent_id,agent_type,session_id,database_name,sequence_number,log_time,log_time_utc,message,source,help_url) '
        + N'select agent_id,agent_type,session_id,database_name,sequence_number,log_time,log_time_utc,message,source,help_url '
        + N'from msdb.dbo.log_shipping_monitor_error_detail where agent_id = N''' + CAST(@agent_id as nvarchar(50)) +  ''' '
    begin try
        EXEC(@linkcmd)
    end try
    begin catch
        select @retcode = 1
    end catch
    return @retcode
end