Repairing a Broken Log Shipping Plan From a Differential Backup

  • Comments posted to this topic are about the item Repairing a Broken Log Shipping Plan From a Differential Backup

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Great article. Very useful for DBA in production environment.

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • joeroshan (9/27/2012)


    Great article. Very useful for DBA in production environment.

    Hi, thanks for the feedback, please don't forget to rate the article if you found it useful

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Could this be used for a site to site DR test where the standby database is opened with recovery for the users to test and then log shipping is rebuilt from scratch by taking a backup of the primary and reinitializing the standby? Could we instead stop the log shipping jobs (bkp,copy,restore), open the standby for the users to test, take a differntial backup of the primary and use that to restore the standby, then restart the log shipping jobs.

  • database has once a day full backup and hourly log backup. No diff. log shipping is for every one hour. Secondary fails at restore due to out of sync lsn.

  • Very good one Perry.

    Rated it 5 star 🙂

    M&M

  • mohammed moinudheen (9/27/2012)


    Very good one Perry.

    Rated it 5 star 🙂

    Hey thanks Mohammed, appreciate the feedback 😉

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • karen_jonesdba (9/27/2012)


    Could this be used for a site to site DR test

    Not really, no.

    karen_jonesdba (9/27/2012)


    Could we instead stop the log shipping jobs (bkp,copy,restore), open the standby for the users to test, take a differntial backup of the primary and use that to restore the standby, then restart the log shipping jobs.

    No, the process above has the wrong ordering. You would need to follow along the lines of

    • stop all Log shipping jobs (backup, copy, restore and alert)
    • run the copy job and then the restore job to ensure that any remaining log backups are restored to the secondary.
    • take a tail log backup on the primary, this will leave the primary database in the restoring state
    • restore tail log backup to the secondary with recovery bringing the database online

    Perform the same again on the new primary to switch back to the original primary and then re enable the Log Shipping jobs

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Ok.. unfortunately we do not failback to the primary with data. The primary is left usable and running during the DR test and the standby is opened just for the duration of test then resyncronized with a full backup and new log shipping jobs. that is the policy.

    Steps are we drop logshipping, open the standby, and at the end of the test resync the standby and resetup logshipping. I was hoping that if we stopped the jobs on the primary(instead of dropping) that after the test was done I could take a differential backup, resync the standby with this backup and let then let the log shipping jobs run. This is a big database and takes a long time recyncing from a full.. Thanks for getting back!

  • Very nice article Perry! Thanks for sharing!

  • I did not know of this aritcle -- but when log shipping broke Saturday because of a third party log backup I decided I had nothing to lose if it did not work. I took a differential of the primary and applied it to the secondary with Standby= 'filename' and was very pleasently surprised (happy dance in the kitchen ). Then I kicked off the backup, copy and restore jobs and the alert stopped notification. I am very thankful for the people who programmed the restore and backup processes for SQL server and have made it very robust.

    Thanks for the article. I was thinking through the "what would happen if a Full had occurred" and this answered that question.

  • Nice article.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply