-- Execute the following statements at the Primary to configure Log Shipping
-- for the database [primary\instance1].[mydb],
-- The script needs to be run at the Primary in the context of the [msdb] database.
-------------------------------------------------------------------------------------
-- Adding the Log Shipping configuration
-- ****** Begin: Script to be run at Primary: [primary\instance1] ******
:setvar Server_Primary primary\instance1
:setvar Server_Secondary secondary\instance2
:setvar DATABASE mydb
:setvar Primary_Backup_path "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\"
:setvar Primary_Backup_Share "\\primary\d$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\"
:setvar Seconday_Backup_Share "\\secondary\d$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\"
:setvar Secondary_Data_Dir "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\"
:connect $(Server_Primary)
alter database [$(DATABASE)] set recovery full
DECLARE @LS_BackupJobId AS uniqueidentifier
DECLARE @LS_PrimaryId AS uniqueidentifier
DECLARE @SP_Add_RetCode As int
EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
@database = N'$(DATABASE)'
,@backup_directory = N'$(Primary_Backup_path)'
,@backup_share = N'$(Primary_Backup_Share)'
,@backup_job_name = N'LSBackup_$(DATABASE)'
,@backup_retention_period = 1440
,@backup_threshold = 180
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
,@backup_job_id = @LS_BackupJobId OUTPUT
,@primary_id = @LS_PrimaryId OUTPUT
,@overwrite = 1
IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)
BEGIN
DECLARE @LS_BackUpScheduleUID As uniqueidentifier
DECLARE @LS_BackUpScheduleID AS int
EXEC msdb.dbo.sp_add_schedule
@schedule_name =N'LSBackupSchedule_$(Server_Primary)'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 60
,@freq_recurrence_factor = 0
,@active_start_date = 20100101
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_BackUpScheduleUID OUTPUT
,@schedule_id = @LS_BackUpScheduleID OUTPUT
EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_BackupJobId
,@schedule_id = @LS_BackUpScheduleID
EXEC msdb.dbo.sp_update_job
@job_id = @LS_BackupJobId
,@enabled = 1
END
EXEC master.dbo.sp_add_log_shipping_alert_job
EXEC master.dbo.sp_add_log_shipping_primary_secondary
@primary_database = N'$(DATABASE)'
,@secondary_server = N'$(Server_Secondary)'
,@secondary_database = N'$(DATABASE)'
,@overwrite = 1
backup database [$(DATABASE)] to disk = '$(Primary_Backup_path)$(DATABASE)_dr_init.bak' with init
go
print '$(Primary_Backup_Share)$(DATABASE)_dr_init.bak'
print '$(Seconday_Backup_Share)'
print 'copy $(Primary_Backup_Share)$(DATABASE)_dr_init.bak $(Seconday_Backup_Share) /y'
!!copy $(Primary_Backup_Share)$(DATABASE)_dr_init.bak $(Seconday_Backup_Share) /y
go
!!del $(Primary_Backup_Share)$(DATABASE)_dr_init.bak /Q
go
-- ****** End: Script to be run at Primary: [primary\instance1] ******
-- Execute the following statements at the Secondary to configure Log Shipping
-- for the database [secondary\instance2].[mydb],
-- the script needs to be run at the Secondary in the context of the [msdb] database.
-------------------------------------------------------------------------------------
-- Adding the Log Shipping configuration
-- ****** Begin: Script to be run at Secondary: [secondary\instance2] ******
:connect $(Server_Secondary)
!!sqlcmd /E /S$(Server_Secondary) -i DRRestoreDatabase.sql -v BKDIR="$(Seconday_Backup_Share)" -v DATADIR="$(Secondary_Data_Dir)" -v LOGDIR="$(SLOGDIR)"
!!del $(Seconday_Backup_Share)$(DATABASE)_dr_init.bak /Q
go
DECLARE @LS_Secondary__CopyJobId AS uniqueidentifier
DECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier
DECLARE @LS_Secondary__SecondaryId AS uniqueidentifier
DECLARE @LS_Add_RetCode As int
EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary
@primary_server = N'$(Server_Primary)'
,@primary_database = N'$(DATABASE)'
,@backup_source_directory = N'$(Primary_Backup_Share)'
,@backup_destination_directory = N'$(Seconday_Backup_Share)'
,@copy_job_name = N'LSCopy_$(Server_Primary)_$(DATABASE)'
,@restore_job_name = N'LSRestore_$(Server_Primary)_$(DATABASE)'
,@file_retention_period = 1440
,@overwrite = 1
,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT
,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT
,@secondary_id = @LS_Secondary__SecondaryId OUTPUT
IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
BEGIN
DECLARE @LS_SecondaryCopyJobScheduleUID As uniqueidentifier
DECLARE @LS_SecondaryCopyJobScheduleID AS int
EXEC msdb.dbo.sp_add_schedule
@schedule_name =N'DefaultCopyJobSchedule'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 60
,@freq_recurrence_factor = 0
,@active_start_date = 20090505
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT
,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT
EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_Secondary__CopyJobId
,@schedule_id = @LS_SecondaryCopyJobScheduleID
DECLARE @LS_SecondaryRestoreJobScheduleUID As uniqueidentifier
DECLARE @LS_SecondaryRestoreJobScheduleID AS int
EXEC msdb.dbo.sp_add_schedule
@schedule_name =N'DefaultRestoreJobSchedule'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 60
,@freq_recurrence_factor = 0
,@active_start_date = 20090505
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT
,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT
EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_Secondary__RestoreJobId
,@schedule_id = @LS_SecondaryRestoreJobScheduleID
END
DECLARE @LS_Add_RetCode2 As int
IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
BEGIN
EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database
@secondary_database = N'$(DATABASE)'
,@primary_server = N'$(Server_Primary)'
,@primary_database = N'$(DATABASE)'
,@restore_delay = 0
,@restore_mode = 1
,@disconnect_users = 1
,@restore_threshold = 180
,@threshold_alert_enabled = 1
,@history_retention_period = 2880
,@overwrite = 1
END
IF (@@error = 0 AND @LS_Add_RetCode = 0)
BEGIN
EXEC msdb.dbo.sp_update_job
@job_id = @LS_Secondary__CopyJobId
,@enabled = 1
EXEC msdb.dbo.sp_update_job
@job_id = @LS_Secondary__RestoreJobId
,@enabled = 1
END
go
-- ****** End: Script to be run at Secondary: [secondary\instance2] ******
