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