Once all the backups and the configurations on the primary have been done, it is time to configure the secondary. Bear in mind that the secondary server SHOULD have access to the shared location or the backup should reside on the secondary server itself.

We need 2 types of schedules for the log shipping configuration:

• Copy Schedule
• Restore Schedule

The purpose of the copy schedule is to copy the log backup, performed by the primary server job, to the secondary server:

Like with the primary server schedule, we can use the same settings:

EXEC sp_add_schedule
    @schedule_name = N'DefaultCopyJobSchedule' ,
    @freq_type=4, 
    @freq_interval=1, 
    @freq_subday_type=4,
    @freq_subday_interval=5, 
    @active_start_time=000000,
    @active_end_time=235959 ;

The restore schedule and job are little bit specific, especially the job, since it depends if we will use the log restore as standby or as norecovery, as we are going to see later. The command for the schedule is more or less the same as the previous ones:

EXEC sp_add_schedule
    @schedule_name = N'DefaultRestoreJobSchedule' ,
    @freq_type=4, 
    @freq_interval=1, 
    @freq_subday_type=4,
    @freq_subday_interval=5, 
    @active_start_time=000000,
    @active_end_time=235959 ;

Once the schedules are created, we can proceed and restore:

xp_cmdshell N'mkdir C:\data\path', NO_OUTPUT
GO
xp_cmdshell N'mkdir C:\logs\path', NO_OUTPUT
GO
xp_cmdshell N'mkdir C:\standby\path', NO_OUTPUT
GO

RESTORE DATABASE [casino]
	FROM  DISK = N'\\10.80.5.101\backups\migration\casino.bak'
	WITH  FILE = 1,
	MOVE N'casino_data_01' TO N'C:\data\path\data_01.mdf',
	MOVE N'casino_data_02' TO N'C:\data\path\data_02.mdf',
	MOVE N'casino_data_03' TO N'C:\data\path\data_03.mdf',
	MOVE N'casino_data_04' TO N'C:\data\path\data_04.mdf',
	MOVE N'casino_log_01' TO N'C:\logs\path\log_01.ldf',
	MOVE N'casino_log_02' TO N'C:\logs\path\log_02.ldf',
	STANDBY = N'C:\standby\ROLLBACK_UNDO.BAK',  NOUNLOAD,  STATS = 10
	--NORECOVERY if NEWER VERSION
GO

Notice that for newer version, for example SQL2008 → SQL2016 we have to use NORECOVERY instead of Standby. After the backup completes, it will not open the database and the database will appear as “Restoring”

The jobs are needed so we can sync everything and start the log shipping/apply processes:

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'AG-PH-CSQL2'
		,@primary_database = N'casino'
		,@backup_source_directory = N'\\shared_storage_ip\path\migration'
		,@backup_destination_directory = N'C:\standby\path'
		,@copy_job_name = N'LSCopy_casino'
		,@restore_job_name = N'LSRestore_casino'
		,@file_retention_period = 4320     <-------You might want to change that so the transaction logs won't be kept so long
		,@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_attach_schedule
		@job_id = @LS_Secondary__CopyJobId
		,@schedule_name = N'DefaultCopyJobSchedule'

DECLARE @LS_SecondaryRestoreJobScheduleUID	As uniqueidentifier
DECLARE @LS_SecondaryRestoreJobScheduleID	AS int


EXEC msdb.dbo.sp_attach_schedule
		@job_id = @LS_Secondary__RestoreJobId
		,@schedule_name = N'DefaultRestoreJobSchedule'


END


DECLARE @LS_Add_RetCode2	As int


EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database
		@secondary_database = N'casino'
		,@primary_server = N'primary_hostname'
		,@primary_database = N'casino'
		,@restore_delay = 0
		,@restore_mode = 1(1 for standby, 0 for norecovery)   <- Again, if newer version, we should put 0
		,@disconnect_users	= 0
		,@restore_threshold = 45
		,@threshold_alert_enabled = 1
		,@history_retention_period	= 5760
		,@overwrite = 1


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

Congrats you have brand new log shipping configuration

Again if you mistake the configuration for some reason, you can use the following procedure to remove it:

sp_delete_log_shipping_secondary_database [ @secondary_database = ] 'tainops'  

P.S. This will remove also any created jobs.

If you made error of the restore type, you edit the restore job configuration as follows:

exec sp_change_log_shipping_secondary_database @secondary_database = 'casino', @restore_mode = 0 - Change a job to recover log from standby to norecovery;