April 18, 2012

sp_delete_log_shipping_primary_database (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_delete_log_shipping_primary_database(nvarchar @database
, bit @ignoreremotemonitor)

MetaData:

 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

No comments:

Post a Comment

Total Pageviews