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
|
