April 18, 2012

sp_delete_log_shipping_secondary_primary (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_secondary_primary(nvarchar @primary_server
, nvarchar @primary_database)

MetaData:

 create procedure sys.sp_delete_log_shipping_secondary_primary   
(
@primary_server sysname -- cannot be NULL
,@primary_database sysname -- cannot be NULL
)
as
begin
set nocount on
declare @retcode int
,@secondary_id uniqueidentifier
,@monitor_server sysname
,@monitor_server_security_mode int
,@copy_job_id uniqueidentifier
,@restore_job_id uniqueidentifier

--
-- 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 = secondary_id
,@monitor_server = monitor_server
,@monitor_server_security_mode = monitor_server_security_mode
,@copy_job_id = copy_job_id
,@restore_job_id = restore_job_id
from msdb.dbo.log_shipping_secondary
where primary_server = upper(@primary_server)
and primary_database = @primary_database
if (@secondary_id is null)
begin
return 0
end
--
-- there should not be any secondary databases for this primary
--
if exists (select * from msdb.dbo.log_shipping_secondary_databases
where secondary_id = @secondary_id)
begin
raiserror(32025, 16, 3, @primary_server, @primary_database)
return 1
end
--
-- delete copy job for this primary
--
exec sys.sp_MSprocesslogshippingjob @type = 2
,@mode = 2
,@jobid = @copy_job_id
--
-- delete restore job for this primary
--
exec sys.sp_MSprocesslogshippingjob @type = 3
,@mode = 2
,@jobid = @restore_job_id
--
-- remove monitor metadata for the secondary and remote monitor
-- The log contain entries where secondary database is null
-- we need to clean this up when dropping secondary primary
--
exec sp_MSprocesslogshippingmonitorsecondary @mode = 2
,@secondary_server = @@servername
,@secondary_id = @secondary_id
,@primary_server = @primary_server
,@monitor_server = @monitor_server
,@monitor_server_security_mode = @monitor_server_security_mode
,@primary_database = @primary_database
--
-- 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 = upper(@monitor_server))
and not exists (select * from msdb.dbo.log_shipping_secondary
where monitor_server = upper(@monitor_server)
and primary_server = upper(@primary_server)
and primary_database = @primary_database)
begin
exec msdb.sys.sp_MSprocesslogshipmonitorlink @mode = 2
,@monitor_server = @monitor_server
,@monitor_server_security_mode = @monitor_server_security_mode
end
end
--
-- remove entry from log_shipping_secondary
--
delete msdb.dbo.log_shipping_secondary
where secondary_id = @secondary_id
--
-- all done
--
return 0
end

No comments:

Post a Comment

Total Pageviews