1

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.

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]  ******
| improve this answer | |
  • 1
    That makes sense, and we eventually just excluded the log shipped db from all jobs to be safe. I just needed a sanity check. Thank you for the clarification. – Justin Frye Oct 31 at 11:08
  • Thank you. Do you mind mark this as an acceptable answer if it satisfied your question fully, so others can be benefitted in the future? – SqlWorldWide Nov 1 at 1:57
  • 1
    Done! Again, thank you for the help. – Justin Frye Nov 3 at 3:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.