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:

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.

Schedule can be created in two ways:

• Using GUI - Too boring to show.
• Using the command line - Not boring to show :)

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 ;

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

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

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

sp_delete_log_shipping_primary_database [ @secondary_database = ] 'tainops'