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:
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'