How to Solve Issue of Log Shipping break/Fail due to Maintenance plan Transaction log backup on Primary

How to Solve Breaking Log Shipping due to manual/ Maintenance plan Transaction log backup on Primary


Demo:

Source Server (Primary) Enterprise SQL 2012 RTM

TestLogShipping database on primary

Destination Server (Secondary) Enterprise  SQL 2012 SP3

TestLogShipping database on with stand by read only mode

Primary server job info Backup

Secondary Job of copy and restore

To see the Transaction log Backup file info @ primary





SELECT Distinct top 20
    s.database_name,s.backup_finish_date,y.physical_device_name,s.first_lsn,s.last_lsn
FROM
    msdb..backupset AS s INNER JOIN
    msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN
    msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN
    msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id
WHERE
    (s.database_name = 'TestLogShipping')
ORDER BY
    s.backup_finish_date DESC;


















@ Primary you can see all transaction log file info with LSN Log sequence Number with date and time

@secondary we can see the status of restore and copy info

Now accidentally some one run Transaction Log backup or start schedule Transaction log backup on Primary through maintenance plan or manual

Note:

Full Backup/Differential Backup would not affect on Log shipping chain through maintenance plan or Manual

Transaction log backup location with date and time

After manual  transaction log backup through maintenance plan Log shipping Restore Job will be

went to fail it gives error as see below

we can see the what the current status which transaction log backup copied and restored

you can see file copied TestLogShipping_20170614043004 but not restored it only

restored TestLogShipping_20170614041503 up to this file only

compare this file as see below

When we see the LSN through query as see below

we have manual log backup file TestLogShipping_backup_2017_06_14_095341_8952854 before the log shipping log backup file TestLogShipping_20170614043004 so it causes log chain is broken

TestLogShipping_20170614043004  log shipping log backup @ 10 am
TestLogShipping_backup_2017_06_14_095341_8952854  manual log backp @ 9.53 am
TestLogShipping_20170614041503 log shipping log backup @ 9.45 am

if you are trying to restore TestLogShipping_20170614043004  log shipping log backup @ 10 am it will give you error as too recent to apply because we have already manual log backup file is there

we should need to restore vice versa to make log chain intact
we should need to restore manual log backup file manually

now we are going to restore manual log backup(maintenance plan) file manually

it gives you correct LSN

You can see it went success

now we can restore next log shipping log backup file this time it should be success because we

already restored manual log backup to make log chain intact

now this time it went success

after this Log shipping restore job will success on its next run

We can see the status from secondary as see below

That is it we fixed the issue successfully


Do Remember you can not take full backup / transaction log backup on Secondary