locked
Log shipping re-enable problem following primary db restore RRS feed

  • Question

  • Hi Folks,

    Hope you can help... I have a database running on Microsoft SQL Server 2008 R2 (SP1)  that has been happily log shipping to standby (along with a bunch of others) for 5 years. Occasionally a user or developer will screw up and need the primary to be restored from backup at which point I'll disable the log shipping, do the primary restore and then re-enable the log shipping with a full backup. I've never run into any issues before.

    Yesterday I needed to restore a backup from a couple of days previous. When I re-enable the log shipping the database backs up and restores fine, the scheduled backup, copy and restore jobs are created and both the backup and copy jobs work fine. However the tran log restore job is failing. It's skipping old tran log files as expected, then finding one that it verifies, tries to apply, then fails as the tran log is from before this restore. I can manually apply logs from after the restore with no issues, but I can't get the LSrestore/sqllogship.exe job to skip further than this particular old log and start applying logs from the correct point.

    Log msgs:

    "Error: Could not apply log backup file <old log file name> to secondary database <databasename>" 

    "This backup set cannot be applied because it is on a recovery path that is inconsistant with the database." 

    "The log backup file was verified but could not be applied to secondary database <databasename>."

    So far I've tried simply renaming the offending log to move it out of the way but then it fails saying it can't find it.

    I also tried setting the primary database to simple recovery then back to full recovery, followed by dumping the log then running the re-enable/restore again. No joy.

    I've deleted the database at the standby side to remove the history. No joy.

    Does anyone have any ideas? I've hunted high and low for information on where the restore is getting the restore sequence from but either I'm not understanding it or not finding it. Yes, I'm looking through BOL... Everything I'm reading suggests that a full backup/restore ought to start the sequence from the correct point.

    Any help greatly appreciated!

    A bit more info... (I've masked the path/filenames)

    This is the old log it's failing on:

    restore headeronly from disk ='xxxxxx_20160105101505.trn'
    FirstLSN  LastLSN  ChkptLSN  DBBkpLSN
    387176000001509900001 387177000000060200001 387176000001503100144 387176000001503100144

    this is the new backup and subsequent logs

    restore headeronly from disk = 'xxxxxx.bak'
    387176000001915300040 387176000001916900001 387176000001915300040 387176000001912700040

    restore headeronly from disk ='xxxxxx_20160106151504.trn'
    387176000001917700001 387176000001917700001 387176000001915300040 387176000001915300040

    restore headeronly from disk ='xxxxxx_20160106153004.trn'
    387176000001917700001 387176000001917700001 387176000001915300040 387176000001915300040

    restore headeronly from disk ='xxxxxx_20160106154504.trn'
    387176000001917700001 387176000001917700001 387176000001915300040 387176000001915300040

    After checkpoint on primary...

    restore headeronly from disk ='xxxxxx_20160106175539.trn'
    387176000001918700001 387176000001921700001 387176000001918700070 387176000001915300040




    • Edited by LD1972 Wednesday, January 6, 2016 6:15 PM
    Wednesday, January 6, 2016 5:03 PM

Answers

All replies

  • Hi LD1972,

    Thank you for your question.

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. Thank you for your understanding and support.

    Thanks,

    Ice fan


    Ice Fan
    TechNet Community Support


    Thursday, January 7, 2016 9:27 AM
  • Hi,

    Since you said that you can manually restore the log files but it fails via job I have a feeling you might be hitting issue mentioned in Below support article

    https://support.microsoft.com/en-us/kb/2685132#/en-us/kb/2685132

    I strongly suggest you to apply SQL Server 2008 R2 Sp3. And see if this issue subsides


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Thursday, January 7, 2016 10:17 AM
  • Hi Guys,

    Many thanks for the replies, much appreciated!

    That's an interesting link to the support article. I was already wondering if this might be a timing issue and the scenario described sounds plausible. My usual MO is to re-initialize the log shipping and then leave it alone to catch up and do it's thing on the schedule, however, yesterday, I was kicking off the backup, copy and restore jobs as soon as the initialization was complete, to "check it was working"... After several more attempts - during which I tried restoring different backups to the primary in case I had a corrupted backup - I finally got one to stick that had previously failed, so something is variable. I'm going to keep playing with another non-critical db to see if I can replicate the issue on the same primary/standby pair.

    In the meantime I think your advice to apply SP3 is spot on!! Yes, probably a bit overdue... ;)

    Thanks again,

    Luke


    • Edited by LD1972 Thursday, January 7, 2016 2:19 PM
    Thursday, January 7, 2016 2:18 PM
  • Hi,

    Based on the error message you provided :"a recovery path that is inconsistant with the database", we can see that the log chain was broken caused via changing the recovery model to simple and back to full or bulk-logged. 

    At this time, it is not allowed to roll forward across recovery model changes that involve the simple recovery model, because the log chain is broken. You need to take the latest differential backups and restore it to the database to bridge the gap. Then, errors should go away when running the restore job.

    Here is a link which discusses the same issue: http://dba.stackexchange.com/questions/114728/errors-restoring-a-differential-backup

    Additionally, please have a check on these two articles for the detailed information on Log Sequence Numbers (LSN) and Recovery Paths:


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, January 11, 2016 3:20 AM
  • >I can manually apply logs from after the restore with no issues, but I can't get the >LSrestore/sqllogship.exe job to skip further than this particular old log and start applying logs from the >correct point.

    Vicky I doubt that this might be issue because OP said that manually restoring the log backups work

    

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Monday, January 11, 2016 7:15 AM
  • Hi LD1972,

    Hope you are doing well. I would love to hear from you if the SP upgrade solved your issue. If you found out the issue do share with us so that it can benefit the community.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Wiki Articles

    MVP

    Friday, January 22, 2016 11:10 AM