April 13, 2012

sp_add_log_shipping_secondary_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_add_log_shipping_secondary_database(bit @ignoreremotemonitor
, nvarchar @secondary_database
, nvarchar @primary_server
, nvarchar @primary_database
, int @restore_delay
, bit @restore_all
, bit @restore_mode
, bit @disconnect_users
, int @block_size
, int @buffer_count
, int @max_transfer_size
, int @restore_threshold
, int @threshold_alert
, bit @threshold_alert_enabled
, int @history_retention_period
, bit @overwrite)

MetaData:

 create procedure sys.sp_add_log_shipping_secondary_database   
(
@secondary_database sysname -- cannot be NULL
,@primary_server sysname -- cannot be NULL
,@primary_database sysname -- cannot be NULL
,@restore_delay int = 0
,@restore_all bit = 1
,@restore_mode bit = 0
,@disconnect_users bit = 0
,@block_size int = -1
,@buffer_count int = -1
,@max_transfer_size int = -1
,@restore_threshold int = 45
,@threshold_alert int = 14421
,@threshold_alert_enabled bit = 0
,@history_retention_period int = 1440 -- in minutes
,@overwrite bit = 0
,@ignoreremotemonitor bit = 0
)
as
begin
set nocount on
declare @retcode int
,@secondary_id uniqueidentifier
,@monitor_server sysname
,@monitor_server_security_mode bit
,@curdate datetime
,@curutcdate datetime
,@faddeddata 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

--
-- Check if the database specified exists - what state should this be in
--
if not exists (select * from sys.databases where name = @secondary_database)
begin
raiserror(15010, 16, 1, @secondary_database)
return 1
end

--
-- Parameter Check: @primary_server
--
if @primary_server is null or @primary_server=N''
begin
raiserror (32052, 16, -1, N'@primary_server')
return 1
end

--
-- Parameter Check: @primary_database
--
if @primary_database is null or @primary_database=N''
begin
raiserror (32052, 16, -1, N'@primary_database')
return 1
end

--
-- check if logshipping entry for this database already exists
--
if exists (select * from msdb.dbo.log_shipping_secondary_databases
where secondary_database = @secondary_database)
begin
if (@overwrite = 0)
begin
raiserror(32013, 16, 1, @secondary_database)
return 1
end
else
begin
exec sp_delete_log_shipping_secondary_database_internal
@secondary_database = @secondary_database
,@ignoreremotemonitor = @ignoreremotemonitor
,@overwrite = @overwrite
end
end
--
-- Check if this primary is registered
--
select @secondary_id = secondary_id
,@monitor_server = monitor_server
,@monitor_server_security_mode = monitor_server_security_mode
from msdb.dbo.log_shipping_secondary
where primary_server = upper(@primary_server)
and primary_database = @primary_database
if (@secondary_id is null)
begin
raiserror(32023, 16, 1, @primary_server, @primary_database)
return 1
end
--
-- validate block_size
--
if (@block_size < -1 or @block_size > 65536)
begin
raiserror(21055, 16, -1, N'@block_size', N'sp_add_log_shipping_secondary_database')
return 1
end
--
-- validate buffer_count
--
if (@buffer_count < -1)
begin
raiserror(21055, 16, -1, N'@buffer_count', N'sp_add_log_shipping_secondary_database')
return 1
end
--
-- validate buffer_count
--
if (@max_transfer_size < -1)
begin
raiserror(21055, 16, -1, N'@max_transfer_size', N'sp_add_log_shipping_secondary_database')
return 1
end
--
-- validate @threshold_alert
--
if (@threshold_alert is null)
select @threshold_alert = 14421
if (@threshold_alert != 14421)
begin
if not exists (select *
from master.dbo.sysmessages where error = @threshold_alert)
begin
raiserror(32028, 16, 3, @threshold_alert)
return 1
end
end
--
-- initialize the restore time for initial entry
--
select @curdate = getdate()
,@curutcdate = getutcdate()
--
-- add entry in log_shipping_secondary_databases
--
insert into msdb.dbo.log_shipping_secondary_databases (
secondary_database
,secondary_id
,restore_delay
,restore_all
,restore_mode
,disconnect_users
,block_size
,buffer_count
,max_transfer_size)
values (
@secondary_database
,@secondary_id
,@restore_delay
,@restore_all
,@restore_mode
,@disconnect_users
,@block_size
,@buffer_count
,@max_transfer_size)
if (@@error != 0)
goto UNDO
select @faddeddata = 1
--
-- Add a monitor metadata
--
exec @retcode = sp_MSprocesslogshippingmonitorsecondary @mode = 1
,@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
,@last_restored_date = @curdate
,@last_restored_date_utc = @curutcdate
,@history_retention_period = @history_retention_period
,@ignoreremotemonitor = @ignoreremotemonitor
if (@retcode != 0 or @@error != 0)
goto UNDO
--
-- all done
--
return 0

UNDO:
--
-- remove monitor metadata for the secondary and remote monitor
--
exec sp_MSprocesslogshippingmonitorsecondary @mode = 2
,@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
,@ignoreremotemonitor = @ignoreremotemonitor
--
-- remove the data on secondary
--
if (@faddeddata = 1)
begin
delete msdb.dbo.log_shipping_secondary_databases
where secondary_database = @secondary_database
and secondary_id = @secondary_id
end
return 1
end

No comments:

Post a Comment

Total Pageviews