
-- Wykonaj poniższe instrukcje na serwerze głównym, aby skonfigurować przesyłanie dzienników
-- dla bazy [SQL2014DEMO\SQLSERVER01].[AdventureWorks].
-- Ten skrypt należy uruchomić na serwerze głównym w kontekście bazy [msdb].
------------------------------------------------------------------------------------- 
-- Konfigurowanie przesyłania dzienników

-- ****** Początek - skrypt do uruchomienia na serwerze głównym: [SQL2014DEMO\SQLSERVER01] ******


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'AdventureWorks' 
		,@backup_directory = N'C:\PrimaryBackupLog' 
		,@backup_share = N'\\SQL2014DEMO\PrimaryBackupLog' 
		,@backup_job_name = N'LSBackup_AdventureWorks' 
		,@backup_retention_period = 4320
		,@backup_compression = 2
		,@monitor_server = N'SQL2014DEMO' 
		,@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_SQL2014DEMO\SQLSERVER011' 
		,@enabled = 1 
		,@freq_type = 4 
		,@freq_interval = 1 
		,@freq_subday_type = 4 
		,@freq_subday_interval = 15 
		,@freq_recurrence_factor = 0 
		,@active_start_date = 20131220 
		,@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'AdventureWorks' 
		,@secondary_server = N'SQL2014DEMO\SQLSERVER02' 
		,@secondary_database = N'AdventureWorks' 
		,@overwrite = 1 

-- ****** Koniec - skrypt do uruchomienia na serwerze głównym: [SQL2014DEMO\SQLSERVER01]  ******


-- Wykonaj poniższe instrukcje na serwerze rezerwowym, aby skonfigurować przesyłanie dzienników
-- dla bazy danych [SQL2014DEMO\SQLSERVER02].[AdventureWorks].
-- Ten skrypt należy uruchomić na serwerze rezerwowym w kontekście bazy [msdb]. 
------------------------------------------------------------------------------------- 
-- Konfigurowanie przesyłania dzienników

-- ****** Początek - ten skrypt należy uruchomić na serwerze rezerwowym: [SQL2014DEMO\SQLSERVER02] ******


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'SQL2014DEMO\SQLSERVER01' 
		,@primary_database = N'AdventureWorks' 
		,@backup_source_directory = N'\\SQL2014DEMO\PrimaryBackupLog' 
		,@backup_destination_directory = N'C:\secondaryBackupDest' 
		,@copy_job_name = N'LSCopy_SQL2014DEMO\SQLSERVER01_AdventureWorks' 
		,@restore_job_name = N'LSRestore_SQL2014DEMO\SQLSERVER01_AdventureWorks' 
		,@file_retention_period = 4320 
		,@monitor_server = N'SQL2014DEMO' 
		,@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 = 20131220 
		,@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 = 20131220 
		,@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'AdventureWorks' 
		,@primary_server = N'SQL2014DEMO\SQLSERVER01' 
		,@primary_database = N'AdventureWorks' 
		,@restore_delay = 0 
		,@restore_mode = 1 
		,@disconnect_users	= 1 
		,@restore_threshold = 45   
		,@threshold_alert_enabled = 1 
		,@history_retention_period	= 5760 
		,@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 


-- ****** Koniec - skrypt do uruchomienia na serwerze rezerwowym: [SQL2014DEMO\SQLSERVER02] ******


