Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday, 13 June 2017

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