
-- Execute the following statements at the Primary to configure Log Shipping 
-- for the database [SQL2K12-SVR1].[CreditReporting],
-- 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: [SQL2K12-SVR1] ******

:CONNECT SQL2K12-SVR1
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'CreditReporting' 
		,@backup_directory = N'\\sql2k12-svr1\backup' 
		,@backup_share = N'\\sql2k12-svr1\backup' 
		,@backup_job_name = N'LSBackup_CreditReporting' 
		,@backup_retention_period = 4320
		,@backup_compression = 1
		,@monitor_server = N'SQL2K12-SVR3' 
		,@monitor_server_security_mode = 1 
		,@backup_threshold = 60 
		,@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_SQL2K12-SVR11' 
		,@enabled = 1 
		,@freq_type = 4 
		,@freq_interval = 1 
		,@freq_subday_type = 4 
		,@freq_subday_interval = 15 
		,@freq_recurrence_factor = 0 
		,@active_start_date = 20131001 
		,@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_primary_secondary 
		@primary_database = N'CreditReporting' 
		,@secondary_server = N'SQL2K12-SVR4' 
		,@secondary_database = N'CreditReporting' 
		,@overwrite = 1 
GO

-- ****** End: Script to be run at Primary: [SQL2K12-SVR1]  ******


-- Execute the following statements at the Secondary to configure Log Shipping 
-- for the database [SQL2K12-SVR4].[CreditReporting],
-- 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: [SQL2K12-SVR4] ******
:CONNECT SQL2K12-SVR4
USE [master]
RESTORE DATABASE [CreditReporting] 
FROM  DISK = N'\\sql2k12-svr1\backup\CreditReport.bak' 
WITH  MOVE N'CreditData' TO N'C:\SQLskills\MSSQL11.MSSQLSERVER\MSSQL\DATA\CreditData.mdf',  
	  MOVE N'CreditLog' TO N'C:\SQLskills\MSSQL11.MSSQLSERVER\MSSQL\DATA\CreditLog.ldf',  
NORECOVERY;
GO

RESTORE LOG [CreditReporting] 
FROM  DISK = N'\\sql2k12-svr1\backup\CreditReport_1.trn' 
WITH NORECOVERY;
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'SQL2K12-SVR1' 
		,@primary_database = N'CreditReporting' 
		,@backup_source_directory = N'\\sql2k12-svr1\backup' 
		,@backup_destination_directory = N'C:\SQLskills\MSSQL11.MSSQLSERVER\MSSQL\Backup\' 
		,@copy_job_name = N'LSCopy_SQL2K12-SVR1_CreditReporting' 
		,@restore_job_name = N'LSRestore_SQL2K12-SVR1_CreditReporting' 
		,@file_retention_period = 4320 
		,@monitor_server = N'SQL2K12-SVR3' 
		,@monitor_server_security_mode = 1 
		,@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 = 15 
		,@freq_recurrence_factor = 0 
		,@active_start_date = 20131001 
		,@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 = 15 
		,@freq_recurrence_factor = 0 
		,@active_start_date = 20131001 
		,@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'CreditReporting' 
		,@primary_server = N'SQL2K12-SVR1' 
		,@primary_database = N'CreditReporting' 
		,@restore_delay = 0 
		,@restore_mode = 0 
		,@disconnect_users	= 0 
		,@restore_threshold = 45   
		,@threshold_alert_enabled = 1 
		,@history_retention_period	= 5760 
		,@overwrite = 1 
		,@ignoreremotemonitor = 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: [SQL2K12-SVR4] ******

-- Configure Log Shipping Monitor Server
:CONNECT SQL2K12-SVR3
EXEC msdb.dbo.sp_processlogshippingmonitorsecondary 
		@mode = 1 
		,@secondary_server = N'SQL2K12-SVR4' 
		,@secondary_database = N'CreditReporting' 
		,@secondary_id = N'357fec6f-c305-4856-94f4-8a858e7caccc' 
		,@primary_server = N'SQL2K12-SVR1' 
		,@primary_database = N'CreditReporting' 
		,@restore_threshold = 45   
		,@threshold_alert = 14420 
		,@threshold_alert_enabled = 1 
		,@history_retention_period	= 5760 
		,@monitor_server = N'SQL2K12-SVR3' 
		,@monitor_server_security_mode = 1 
GO




-- ****** Failover the Mirroring Partner to setup Log Shipping for Failover ******
:CONNECT SQL2K12-SVR1
ALTER DATABASE [CreditReporting]
SET PARTNER FAILOVER;
GO






-- Execute the following statements at the Primary to configure Log Shipping 
-- for the database [SQL2K12-SVR2].[CreditReporting],
-- 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: [SQL2K12-SVR2] ******

:CONNECT SQL2K12-SVR2
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'CreditReporting' 
		,@backup_directory = N'\\sql2k12-svr1\backup' 
		,@backup_share = N'\\sql2k12-svr1\backup' 
		,@backup_job_name = N'LSBackup_CreditReporting' 
		,@backup_retention_period = 4320
		,@backup_compression = 1
--		,@monitor_server = N'SQL2K12-SVR3' 
--		,@monitor_server_security_mode = 1 
		,@backup_threshold = 60 
		,@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_SQL2K12-SVR21' 
		,@enabled = 1 
		,@freq_type = 4 
		,@freq_interval = 1 
		,@freq_subday_type = 4 
		,@freq_subday_interval = 15 
		,@freq_recurrence_factor = 0 
		,@active_start_date = 20131001 
		,@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_primary_secondary 
		@primary_database = N'CreditReporting' 
		,@secondary_server = N'SQL2K12-SVR4' 
		,@secondary_database = N'CreditReporting' 
		,@overwrite = 1 
GO

