Excluding log shipped DB from current database maintenance plan (Ola's solution)

Question

The Senior DBA and I were discussing our most recent rollout of log shipping to 25+ servers and one of the issues we had ran into during our pilot was the log chain breaking randomly.

We narrowed down several other factors that, at one point, were causing this issue (VEEAM backups were not using "copy only" in application-aware processing). However, after resolving many of the issues, one of our colleagues mentioned that we should remove the log shipped db from any maintenance plan we had in place.

But, and I cannot remember where, both my Senior DBA and I were under the impression that we had read SOMEWHERE that log shipped dbs were ignored by default. Despite this, we removed the log backups (including full and diff just to be sure) and that resolved the issue.

My question is this: Should we exclude the backups? And then the follow up question is whether they should be excluded from Ola's and/or MSFT's plans?

Regardless, our issue is resolved - this is just more of question of checking our sanity that we did read this somewhere.

Answer 1

When you set up Log Shipping, it creates a job for transaction log backup. If you already have an existing transaction log backup job, irrespective of kind (maintenance plan, Ola's solution, SQL Agent job) should be disabled/deleted.

There is a workaround to keep the existing transaction log backup job and set up Log Shipping. You can disable the job that gets created during the Log Shopping setup.

Sample code:

-- Execute the following statements at the Primary to configure Log Shipping 
-- for the database [HostName\SQL2019].[DbaDatabase],
-- The script needs to be run at the Primary in the context of the [msdb] database.  
------------------------------------------------------------------------------------- 
-- Adding the Log Shipping configuration 

-- ****** Begin: Script to be run at Primary: [HostName\SQL2019] ******


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'DbaDatabase' 
        ,@backup_directory = N'\\logshipping\backup' 
        ,@backup_share = N'\\logshipping\backup' 
        ,@backup_job_name = N'LSBackup_DbaDatabase' 
        ,@backup_retention_period = 4320
        ,@backup_compression = 2
        ,@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_HostName\SQL20191' 
        ,@enabled = 1 
        ,@freq_type = 4 
        ,@freq_interval = 1 
        ,@freq_subday_type = 4 
        ,@freq_subday_interval = 15 
        ,@freq_recurrence_factor = 0 
        ,@active_start_date = 20201029 
        ,@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 = 0 


END 


EXEC master.dbo.sp_add_log_shipping_alert_job 

-- ****** End: Script to be run at Primary: [HostName\SQL2019]  ******