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