create procedure sys.sp_change_log_shipping_secondary_database
(
@secondary_database sysname -- cannot be NULL
,@restore_delay int = NULL
,@restore_all bit = NULL
,@restore_mode bit = NULL
,@disconnect_users bit = NULL
,@block_size int = NULL
,@buffer_count int = NULL
,@max_transfer_size int = NULL
,@restore_threshold int = NULL
,@threshold_alert int = NULL
,@threshold_alert_enabled bit = NULL
,@history_retention_period int = NULL
,@ignoreremotemonitor bit = 0
)
as
begin
set nocount on
declare @retcode int
,@secondary_id uniqueidentifier
,@primary_server sysname
,@primary_database sysname
,@monitor_server sysname
,@monitor_server_security_mode bit
-- 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
-- Does it exist
select @secondary_id = sd.secondary_id
,@primary_server = s.primary_server
,@primary_database = s.primary_database
,@monitor_server = s.monitor_server
,@monitor_server_security_mode = s.monitor_server_security_mode
from msdb.dbo.log_shipping_secondary_databases as sd join msdb.dbo.log_shipping_secondary as s
on sd.secondary_id = s.secondary_id
where sd.secondary_database = @secondary_database
if (@secondary_id is null)
begin
raiserror(32014, 16, 1, @secondary_database)
return 1
end
-- validate block_size
if (@block_size is not null and (@block_size < -1 or @block_size > 65536))
begin
raiserror(21055, 16, -1, N'@block_size', N'sp_change_log_shipping_secondary_database')
return 1
end
-- validate buffer_count
if (@buffer_count is not null and @buffer_count < -1)
begin
raiserror(21055, 16, -1, N'@buffer_count', N'sp_change_log_shipping_secondary_database')
return 1
end
-- validate buffer_count
if (@max_transfer_size is not null and @max_transfer_size < -1)
begin
raiserror(21055, 16, -1, N'@max_transfer_size', N'sp_change_log_shipping_secondary_database')
return 1
end
-- validate @threshold_alert
if (@threshold_alert is not null and @threshold_alert != 14421)
begin
if not exists (select *
from master.dbo.sysmessages where error = @threshold_alert)
begin
raiserror(32028, 16, 4, @threshold_alert)
return 1
end
end
-- start transaction
begin tran sp_change_ls_sd
save tran sp_change_ls_sd
-- update log_shipping_secondary_databases
update msdb.dbo.log_shipping_secondary_databases
set restore_delay = case when (@restore_delay is null) then restore_delay else @restore_delay end
,restore_all = case when (@restore_all is null) then restore_all else @restore_all end
,restore_mode = case when (@restore_mode is null) then restore_mode else @restore_mode end
,disconnect_users = case when (@disconnect_users is null) then disconnect_users else @disconnect_users end
,block_size = case when (@block_size is null) then block_size else @block_size end
,buffer_count = case when (@buffer_count is null) then buffer_count else @buffer_count end
,max_transfer_size = case when (@max_transfer_size is null) then max_transfer_size else @max_transfer_size end
where secondary_id = @secondary_id
and secondary_database = @secondary_database
if (@@error != 0)
goto UNDO
-- commit
commit tran
-- Add a monitor metadata
exec @retcode = sp_MSprocesslogshippingmonitorsecondary @mode = 3
,@secondary_server = @@servername
,@secondary_database = @secondary_database
,@secondary_id = @secondary_id
,@primary_server = @primary_server
,@monitor_server = @monitor_server
,@monitor_server_security_mode = @monitor_server_security_mode
,@primary_database = @primary_database
,@restore_threshold = @restore_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_sd
commit tran
return 1
end