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_primary(nvarchar @primary_server, nvarchar @primary_database
, nvarchar @backup_source_directory
, nvarchar @backup_destination_directory
, nvarchar @copy_job_name
, nvarchar @restore_job_name
, int @file_retention_period
, nvarchar @monitor_server
, bit @monitor_server_security_mode
, nvarchar @monitor_server_login
, nvarchar @monitor_server_password
, bit @overwrite
, bit @ignoreremotemonitor)
MetaData:
create procedure sys.sp_add_log_shipping_secondary_primary ( @primary_server sysname -- cannot be NULL ,@primary_database sysname -- cannot be NULL ,@backup_source_directory nvarchar(500) -- cannot be NULL ,@backup_destination_directory nvarchar(500) -- cannot be NULL ,@copy_job_name sysname = NULL ,@restore_job_name sysname = NULL ,@file_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 ,@copy_job_id uniqueidentifier = NULL output ,@restore_job_id uniqueidentifier = NULL output ,@secondary_id uniqueidentifier = NULL output ,@overwrite bit = 0 ,@ignoreremotemonitor bit = 0 ) as begin set nocount on declare @retcode int ,@secondary_database sysname ,@jobdescription nvarchar(512) ,@jobcommand nvarchar(3200) ,@user_specified_monitor bit ,@fcreatedmonitorlink bit ,@toolpath nvarchar(260) -- -- 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 -- -- 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 -- -- Parameter Check: @backup_source_directory -- if @backup_source_directory is null or @backup_source_directory=N'' begin raiserror (32052, 16, -1, N'@backup_source_directory') return 1 end -- -- Parameter Check: @backup_destination_directory -- if @backup_destination_directory is null or @backup_destination_directory=N'' begin raiserror (32052, 16, -1, N'@backup_destination_directory') return 1 end -- -- Check if the specified primary already exists -- select @secondary_id = secondary_id from msdb.dbo.log_shipping_secondary where primary_server = upper(@primary_server) and primary_database = @primary_database if (@secondary_id is not null) begin if (@overwrite = 0) begin raiserror(32024, 16, 1, @primary_server, @primary_database) return 1 end else begin -- doing an overwrite -- delete any existing secondary databases for the secondary id declare #hc cursor local fast_forward for select secondary_database from msdb.dbo.log_shipping_secondary_databases where secondary_id = @secondary_id open #hc fetch #hc into @secondary_database while (@@fetch_status != -1) begin exec sys.sp_delete_log_shipping_secondary_database_internal @secondary_database = @secondary_database ,@ignoreremotemonitor = @ignoreremotemonitor ,@overwrite = @overwrite fetch #hc into @secondary_database end close #hc deallocate #hc -- delete the secondary primary exec sys.sp_delete_log_shipping_secondary_primary @primary_server = @primary_server ,@primary_database = @primary_database select @secondary_id = NULL end end -- -- Get the agent path -- exec @retcode = sys.sp_MSgetlogshipagentpath @toolpath = @toolpath OUTPUT if @retcode != 0 begin raiserror(32018, 16, 3) return 1 end -- -- Generate a secondary ID -- select @secondary_id = newid() -- -- 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 (upper(@monitor_server) != upper(@@servername)) begin if not exists (select * from msdb.dbo.log_shipping_secondary where monitor_server = @monitor_server and secondary_id != @secondary_id) 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) return 1 select @fcreatedmonitorlink = 1 end end -- -- Prepare copy job name if not specified -- if (@copy_job_name is null or @copy_job_name = N'') begin select @copy_job_name = sys.fn_MSgetlogshippingjobname(@primary_server, @primary_database, 1) end select @jobdescription = isnull(formatmessage(32002, @primary_server, @primary_database), N'Message 32002') ,@jobcommand = N'"' + @toolpath + N'sqllogship.exe" -Copy ' + cast(@secondary_id as nvarchar(40)) + N' -server ' + @@servername -- -- Create a copy job for the secondary -- exec @retcode = sys.sp_MSprocesslogshippingjob @type = 2 ,@mode = 1 ,@jobid = @copy_job_id output ,@job_name = @copy_job_name ,@description = @jobdescription ,@command = @jobcommand ,@overwrite = @overwrite if (@retcode != 0 or @@error != 0 or @copy_job_id is null) goto UNDO -- -- Prepare restore job name if not specified -- if (@restore_job_name is null or @restore_job_name = N'') begin select @restore_job_name = sys.fn_MSgetlogshippingjobname(@primary_server, @primary_database, 2) end select @jobdescription = isnull(formatmessage(32003, @primary_server, @primary_database), N'Message 32003') ,@jobcommand = N'"' + @toolpath + N'sqllogship.exe" -Restore ' + cast(@secondary_id as nvarchar(40)) + N' -server ' + @@servername -- -- Create a restore job for the secondary -- exec @retcode = sys.sp_MSprocesslogshippingjob @type = 3 ,@mode = 1 ,@jobid = @restore_job_id output ,@job_name = @restore_job_name ,@description = @jobdescription ,@command = @jobcommand ,@overwrite = @overwrite if (@retcode != 0 or @@error != 0 or @restore_job_id is null) goto UNDO -- -- Add an entry in the log_shipping_secondary -- insert into msdb.dbo.log_shipping_secondary ( secondary_id ,primary_server ,primary_database ,backup_source_directory ,backup_destination_directory ,file_retention_period ,copy_job_id ,restore_job_id ,monitor_server ,monitor_server_security_mode ,user_specified_monitor) values ( @secondary_id ,upper(@primary_server) ,@primary_database ,@backup_source_directory ,@backup_destination_directory ,@file_retention_period ,@copy_job_id ,@restore_job_id ,@monitor_server ,@monitor_server_security_mode ,@user_specified_monitor) if (@@error != 0) goto UNDO -- -- all done -- return 0 UNDO: -- -- delete copy job if needed -- if (@copy_job_id is not null) begin exec sys.sp_MSprocesslogshippingjob @type = 2 ,@mode = 2 ,@jobid = @copy_job_id end -- -- delete restore job if needed -- if (@restore_job_id is not null) begin exec sys.sp_MSprocesslogshippingjob @type = 3 ,@mode = 2 ,@jobid = @restore_job_id end -- -- drop monitor if needed -- if (@fcreatedmonitorlink = 1) begin exec msdb.sys.sp_MSprocesslogshipmonitorlink @mode = 2 ,@monitor_server = @monitor_server ,@monitor_server_security_mode = @monitor_server_security_mode end -- -- delete entry in log_shipping_secondary -- delete msdb.dbo.log_shipping_secondary where secondary_id = @secondary_id -- -- reset output vars -- select @secondary_id = NULL ,@copy_job_id = NULL ,@restore_job_id = NULL return 1 end
No comments:
Post a Comment