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_primary_database(nvarchar @database, nvarchar @backup_directory
, nvarchar @backup_share
, nvarchar @backup_job_name
, int @backup_retention_period
, nvarchar @monitor_server
, bit @monitor_server_security_mode
, nvarchar @monitor_server_login
, nvarchar @monitor_server_password
, int @backup_threshold
, int @threshold_alert
, bit @threshold_alert_enabled
, int @history_retention_period
, bit @overwrite
, bit @ignoreremotemonitor
, tinyint @backup_compression)
MetaData:
create procedure sys.sp_add_log_shipping_primary_database ( @database sysname -- cannot be NULL ,@backup_directory nvarchar(500) -- cannot be NULL ,@backup_share nvarchar(500) -- cannot be NULL ,@backup_job_name sysname = NULL ,@backup_retention_period int = 1440 -- in minutes ,@monitor_server sysname = NULL ,@monitor_server_security_mode bit = 1 ,@monitor_server_login sysname = NULL ,@monitor_server_password sysname = NULL ,@backup_threshold int = 45 -- in minutes ,@threshold_alert int = 14420 ,@threshold_alert_enabled bit = 0 ,@history_retention_period int = 1440 -- in minutes ,@backup_job_id uniqueidentifier = NULL output ,@primary_id uniqueidentifier = NULL output ,@overwrite bit = 0 ,@ignoreremotemonitor bit = 0 ,@backup_compression tinyint = 2 -- 0 disabled, 1 enabled, 2 server default ) as begin set nocount on declare @retcode int ,@jobdescription nvarchar(512) ,@jobcommand nvarchar(3200) ,@toolpath nvarchar(260) ,@user_specified_monitor bit ,@curdate datetime ,@curutcdate datetime ,@fcreatedmonitorlink bit ,@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 -- if not exists (select * from master.sys.databases where name = @database) begin raiserror(15010, 16, 1, @database) return 1 end -- -- cannot support SIMPLE recovery mode -- if (DATABASEPROPERTYEX(@database, N'Recovery') = N'SIMPLE') begin raiserror(32015, 16, 1, @database) return 1 end -- -- Warn if the database is not online -- if (DATABASEPROPERTYEX(@database, N'Status') != N'ONLINE') begin raiserror(32008, 10, 1, @database) end -- -- Parameter Check: @backup_directory -- if @backup_directory is null or @backup_directory=N'' begin raiserror (32052, 16, -1, N'@backup_directory') return 1 end -- -- Parameter Check: @backup_share -- if @backup_share is null or @backup_share=N'' begin raiserror (32052, 16, -1, N'@backup_share') return 1 end -- -- Parameter Check: @backup_compression -- if @backup_compression not in (0, 1, 2) begin raiserror(21055, 16, -1, '@backup_compression','sp_add_log_shipping_primary_database') return 1 end -- -- check if logshipping entry for this database already exists -- select @primary_id = primary_id from msdb.dbo.log_shipping_primary_databases where primary_database = @database if (@primary_id is not null) begin if (@overwrite = 0) begin raiserror(32009, 16, 1, @database) return 1 end else begin -- -- overwriting the entry -- cleanup any existing secondaries defined for this primary on primary -- delete msdb.dbo.log_shipping_primary_secondaries where primary_id = @primary_id -- delete the primary now exec sp_delete_log_shipping_primary_database @database = @database ,@ignoreremotemonitor = @ignoreremotemonitor select @primary_id = NULL end end -- -- validate @threshold_alert -- if (@threshold_alert is null) select @threshold_alert = 14420 if (@threshold_alert != 14420) begin if not exists (select * from master.dbo.sysmessages where error = @threshold_alert) begin raiserror(32028, 16, 1, @threshold_alert) return 1 end end -- -- Generate a primary ID -- select @primary_id = newid() ,@curdate = getdate() ,@curutcdate = getutcdate() -- -- initialize moniter_server if not specified -- select @user_specified_monitor = case when (@monitor_server is null) then 0 else 1 end select @monitor_server = case when (@monitor_server is null) then upper(@@servername) else upper(@monitor_server) end -- -- Prepare monitor link if not local -- if (@monitor_server != upper(@@servername)) begin -- -- create link for monitor server -- exec @retcode = msdb.sys.sp_MSprocesslogshipmonitorlink @mode = 1 ,@monitor_server = @monitor_server ,@monitor_server_security_mode = @monitor_server_security_mode ,@monitor_server_login = @monitor_server_login ,@monitor_server_password = @monitor_server_password if (@retcode != 0 or @@error != 0) begin raiserror(32054, 16, 2) return 1 end select @fcreatedmonitorlink = 1 end -- -- Get the agent path -- exec @retcode = sys.sp_MSgetlogshipagentpath @toolpath = @toolpath OUTPUT if @retcode != 0 begin raiserror(32018, 16, 2) return 1 end -- -- Prepare backup job name if not specified -- if (@backup_job_name is null or @backup_job_name = N'') begin select @backup_job_name = sys.fn_MSgetlogshippingjobname(NULL, @database, 0) end select @jobdescription = isnull(formatmessage(32001, @database), N'Message 32001') ,@jobcommand = N'"' + @toolpath + N'sqllogship.exe" -Backup ' + cast(@primary_id as nvarchar(40)) + N' -server ' + @@servername -- -- Create a backup job for the primary database -- exec @retcode = sys.sp_MSprocesslogshippingjob @type = 1 ,@mode = 1 ,@jobid = @backup_job_id output ,@job_name = @backup_job_name ,@description = @jobdescription ,@command = @jobcommand ,@overwrite = @overwrite if (@retcode != 0 or @@error != 0 or @backup_job_id is null) goto UNDO -- -- Add an entry in the log_shipping_primary_databases -- insert into msdb.dbo.log_shipping_primary_databases ( primary_id ,primary_database ,backup_directory ,backup_share ,backup_retention_period ,backup_job_id ,monitor_server ,monitor_server_security_mode ,user_specified_monitor ,backup_compression) values ( @primary_id ,@database ,@backup_directory ,@backup_share ,@backup_retention_period ,@backup_job_id ,@monitor_server ,@monitor_server_security_mode ,@user_specified_monitor ,@backup_compression) if (@@error != 0) goto UNDO select @faddeddata = 1 -- -- Add a monitor metadata -- exec @retcode = sp_MSprocesslogshippingmonitorprimary @mode = 1 ,@primary_id = @primary_id ,@primary_server = @@servername ,@monitor_server = @monitor_server ,@monitor_server_security_mode = @monitor_server_security_mode ,@primary_database = @database ,@backup_threshold = @backup_threshold ,@threshold_alert = @threshold_alert ,@threshold_alert_enabled = @threshold_alert_enabled ,@last_backup_date = @curdate ,@last_backup_date_utc = @curutcdate ,@history_retention_period = @history_retention_period ,@ignoreremotemonitor = @ignoreremotemonitor if (@retcode != 0 or @@error != 0) begin raiserror(32055, 16, 2) goto UNDO end -- -- all done -- return 0 UNDO: -- -- delete backup job if needed -- if (@backup_job_id is not null) begin exec sys.sp_MSprocesslogshippingjob @type = 1 ,@mode = 2 ,@jobid = @backup_job_id end -- -- drop monitor metadata -- drop monitor if needed -- if (@fcreatedmonitorlink = 1) begin -- -- 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 link -- exec msdb.sys.sp_MSprocesslogshipmonitorlink @mode = 2 ,@monitor_server = @monitor_server ,@monitor_server_security_mode = @monitor_server_security_mode end -- -- delete data on primary -- if (@faddeddata = 1) begin delete msdb.dbo.log_shipping_primary_databases where primary_id = @primary_id end -- -- reset output vars -- select @primary_id = NULL ,@backup_job_id = NULL return 1 end
No comments:
Post a Comment