create procedure sys.sp_delete_log_shipping_primary_database
(
@database sysname -- cannot be NULL
,@ignoreremotemonitor bit = 0
)
as
begin
set nocount on
declare @retcode int
,@primary_id uniqueidentifier
,@backup_job_id uniqueidentifier
,@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 @primary_id = primary_id
,@backup_job_id = backup_job_id
,@monitor_server = monitor_server
,@monitor_server_security_mode = monitor_server_security_mode
from msdb.dbo.log_shipping_primary_databases
where primary_database = @database
if (@primary_id is null)
begin
-- Does not exist - return no error
return 0
end
-- there should not be any secondaries for this primary
if exists (select * from msdb.dbo.log_shipping_primary_secondaries
where primary_id = @primary_id)
begin
raiserror(32011, 16, 1, @database)
return 1
end
-- delete backup job
exec sys.sp_MSprocesslogshippingjob @type = 1
,@mode = 2
,@jobid = @backup_job_id
-- remove monitor metadata for the primary and remote monitor
exec sys.sp_MSprocesslogshippingmonitorprimary @mode = 2
,@primary_id = @primary_id
,@primary_server = @@servername
,@monitor_server = @monitor_server
,@monitor_server_security_mode = @monitor_server_security_mode
,@primary_database = @database
,@ignoreremotemonitor = @ignoreremotemonitor
-- Remove remote monitor link if needed
if (upper(@monitor_server) != upper(@@servername))
begin
-- check if any other primary or secondary is using this monitor
if not exists (select * from msdb.dbo.log_shipping_primary_databases
where monitor_server = @monitor_server
and primary_database != @database)
and not exists (select * from msdb.dbo.log_shipping_secondary
where monitor_server = @monitor_server)
begin
exec msdb.sys.sp_MSprocesslogshipmonitorlink @mode = 2
,@monitor_server = @monitor_server
,@monitor_server_security_mode = @monitor_server_security_mode
if (@retcode != 0 or @@error != 0)
return 1
end
end
-- delete entry from log_shipping_primary_databases
delete msdb.dbo.log_shipping_primary_databases
where primary_database = @database
-- all done
return 0
end