Table of Contents

Configuring Primary

Be sure that both primary and target destination have access to the shared location which you will use for the backup. Alternatively, you can backup directly on the target server in case you have a space:

Disable Already existing Backup

If you have any scheduled backup either via job or maintanence plan, you have to disable them before adding other jobs on the primary which will require the transaction log.

Create a schedule

Schedule can be created in two ways:

This will create a schedule which can run jobs each 5 minutes daily:

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

Create jobs

Once we have the schedule, we have to create and add jobs to it as follows:

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_name}'
		,@backup_directory = N'\\shared_server_location_ip\path\%{database_name}'
		,@backup_share = N'\\shared_server_location_ip\path\%{database_name}'
		,@backup_job_name = N'LSBackup_%{database_name}'
		,@backup_retention_period = 4320
		,@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


EXEC msdb.dbo.sp_attach_schedule
		@job_id = @LS_BackupJobId
		,@schedule_name = N'Every5Minutes'   <- Schedule we just created.

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_name}'
		,@secondary_server = N'secondary_server_hostname'
		,@secondary_database = N'%{database_name}'
		,@overwrite = 1
GO

xp_cmdshell N'mkdir \\shared_server_location_ip\path\%{database_name}', NO_OUTPUT
GO

Perform initial backup

The backup will be done on shared location OR on the target server(in case you are rich and have money for storage :) ) Since we are poor people, let's do it on shared storage:

BACKUP DATABASE [%{database_name}] TO DISK= N'\\shared_server_location_ip\path\%{database_name}\%{database_name}.bak' WITH COMPRESSION

Remove of the configuration

If for any reason you mistake the configuration, you can remove it as follows:

sp_delete_log_shipping_primary_database [ @secondary_database = ] 'tainops'