create procedure sys.sp_MSprocesslogshippingmonitorprimary
(
@mode tinyint -- 1 = add, 2 = delete, 3 = update
,@primary_id uniqueidentifier
,@primary_server sysname = NULL -- needed for add
,@monitor_server sysname
,@monitor_server_security_mode bit = 1
,@primary_database sysname = NULL -- needed for add
,@backup_threshold int = NULL
,@threshold_alert int = NULL
,@threshold_alert_enabled bit = NULL
,@last_backup_file nvarchar(500) = NULL
,@last_backup_date datetime = NULL
,@last_backup_date_utc datetime = NULL
,@history_retention_period int = NULL
,@ignoreremotemonitor bit = 0
)
as
begin
set nocount on
declare @retcode int
,@linkcmd nvarchar(512)
-- validate @mode
if (@mode not in (1,2,3))
return 1
-- When adding - always delete first
if (@mode in (1,2))
begin
-- clean up log_shipping_monitor_error_detail
select @linkcmd = N'delete msdb.dbo.log_shipping_monitor_error_detail '
+ N'where agent_id = N''' + cast(@primary_id as nvarchar(50)) + N''' and agent_type = 0 '
exec (@linkcmd)
-- clean up log_shipping_monitor_history_detail
select @linkcmd = N'delete msdb.dbo.log_shipping_monitor_history_detail '
+ N'where agent_id = N''' + cast(@primary_id as nvarchar(50)) + N''' and agent_type = 0 '
exec (@linkcmd)
-- remove entry from log_shipping_monitor_primary
delete msdb.dbo.log_shipping_monitor_primary
where (@primary_id is not null
and primary_id = @primary_id)
or (@primary_server is not null and @primary_database is not null
and primary_server = upper(@primary_server)
and primary_database = @primary_database)
-- drop alert job if needed
if (upper(@monitor_server) = upper(@@servername))
begin
exec sys.sp_delete_log_shipping_alert_job_internal
end
-- specific processing for add
if (@mode = 1)
begin
-- Add an entry in the log_shipping_monitor_primary
insert into msdb.dbo.log_shipping_monitor_primary (
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)
values (
@primary_id
,upper(@primary_server)
,@primary_database
,@backup_threshold
,@threshold_alert
,@threshold_alert_enabled
,@last_backup_file
,@last_backup_date
,@last_backup_date_utc
,@history_retention_period)
if (@@error != 0)
return 1
-- add alert job if needed
if (upper(@monitor_server) = upper(@@servername))
begin
exec sys.sp_add_log_shipping_alert_job_internal
end
end
end
else if (@mode = 3)
begin
-- update log_shipping_monitor_primary
update msdb.dbo.log_shipping_monitor_primary
set backup_threshold = case when (@backup_threshold is null) then backup_threshold else @backup_threshold end
,threshold_alert = case when (@threshold_alert is null) then threshold_alert else @threshold_alert end
,threshold_alert_enabled = case when (@threshold_alert_enabled is null) then threshold_alert_enabled else @threshold_alert_enabled end
,last_backup_file = case when (@last_backup_file is null) then last_backup_file else @last_backup_file end
,last_backup_date = case when (@last_backup_date is null) then last_backup_date else @last_backup_date end
,last_backup_date_utc = case when (@last_backup_date_utc is null) then last_backup_date_utc else @last_backup_date_utc end
,history_retention_period = case when (@history_retention_period is null) then history_retention_period else @history_retention_period end
where primary_id = @primary_id
if (@@error != 0)
return 1
end
-- Do we need have remote monitor
if (upper(@monitor_server) != upper(@primary_server)
and upper(@monitor_server) != upper(@@servername)
and @ignoreremotemonitor = 0)
begin
if (@monitor_server_security_mode = 0) and (suser_name() != SUSER_SNAME(0x01))
begin
-- Process remote monitor using proxy
exec @retcode = msdb.sys.sp_MSproxylogshippingmonitorprimary
@mode = @mode
,@primary_id = @primary_id
,@primary_server = @primary_server
,@monitor_server = @monitor_server
,@primary_database = @primary_database
,@backup_threshold = @backup_threshold
,@threshold_alert = @threshold_alert
,@threshold_alert_enabled = @threshold_alert_enabled
,@last_backup_file = @last_backup_file
,@last_backup_date = @last_backup_date
,@last_backup_date_utc = @last_backup_date_utc
,@history_retention_period = @history_retention_period
end
else
begin
-- integrated -do not use proxy
select @linkcmd = quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.msdb.sys.sp_processlogshippingmonitorprimary'
begin try
exec @retcode = @linkcmd
@mode = @mode
,@primary_id = @primary_id
,@primary_server = @primary_server
,@monitor_server = @monitor_server
,@monitor_server_security_mode = 1
,@primary_database = @primary_database
,@backup_threshold = @backup_threshold
,@threshold_alert = @threshold_alert
,@threshold_alert_enabled = @threshold_alert_enabled
,@last_backup_file = @last_backup_file
,@last_backup_date = @last_backup_date
,@last_backup_date_utc = @last_backup_date_utc
,@history_retention_period = @history_retention_period
end try
begin catch
select @retcode = 1
end catch
end
if (@retcode != 0 or @@error != 0)
return 1
end
-- all done
return 0
end