Differential backups breaking Log shipping

  • Hi Everyone,

    I have setup log shipping on a Primary Server for all the DB's. The Log shipping has been failing for one particular db, because differential backup is taken on daily basis. I am sure because, when the diff backup has been taken, the LSN chain breaks..i.e..the Last LSN and First LSN does not match..

    We take the diff backup and to send them to the client everyday and they also want log shipping to be implemented on their DB. As, the diff backup is breaking log shipping..I am thinking we can send them Tlogs on daily basis instead of sending them differentials...But the client has to restore multiple t-logs per day..which might be a lengthy process..

    Is there a better solution for this??

    Please let me know..Appreciate your help!!

  • Diff backup should not break log chain. Have you checked the server logs to determine if something else is doing log backups, or if something is altering the recovery mode of the database during the diff backup?

    Make sure it's not being set to simple for some reason.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • I've got to agree with Craig, differential backup shouldn't break log shipping. I've implemented this before without issues. There must be something else going on, like a truncate log.

    Cheers

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • I recently ran into an issue where a server's diff backups were failing. DB set to full weekly, diffs daily (test db, so no t-logs). On the third day after the full, diffs would fail. FINALLY tracked it down... SAs implemented volume shadow copying. When it kicks off, it initializes all Volume Shadow Services - including the SQL VSS service. When this kicks off, it invalidates the backup, even if a backup isn't done. Stopping the SQL VSS service, and setting it to Manual, fixed this issue for me. Might be worth taking a look at for you.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • there are no t-log backups been taken out side the log shipping, other than the LsBackup job...and the recovery model is set to FULL.

    The backup scenario is, we send them Full backup every week, and diff backup everyday....

    We also log ship that particular DB on our own DR server too...which is also breaking..

    And, all other db's on that same instance..are log shipped successfully...except the one which has diff backup on it....

  • Did it fail after the very first differential backup?

    Just to be sure can you post the differential backup code you use?

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • Yes, it started failing after the very first diff backup..and the daily diff backups are taken in a maint. plan.

    and here is the code:

    BACKUP DATABASE ABC

    TO DISK = 'D:\Backups\ABC\ABCDifferential.bak'

    WITH INIT, DIFFERENTIAL, NAME = 'ABC_Differential', DESCRIPTION = 'ABC Differential backup'

    The Maint. Plan has three tasks...

    1) Diff backup (above code)

    2) T-Log Backup

    3) Full Backup with COPY_ONLY option

  • kiranjanjanam (11/8/2010)


    Yes, it started failing after the very first diff backup..and the daily diff backups are taken in a maint. plan.

    and here is the code:

    BACKUP DATABASE ABC

    TO DISK = 'D:\Backups\ABC\ABCDifferential.bak'

    WITH INIT, DIFFERENTIAL, NAME = 'ABC_Differential', DESCRIPTION = 'ABC Differential backup'

    The Maint. Plan has three tasks...

    1) Diff backup (above code)

    2) T-Log Backup

    3) Full Backup with COPY_ONLY option

    If the maint plan that's doing the DIFF backup is also doing a T-Log backup, you break the log chain for log shipping. Pull step 2 from the maint plan.

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • Hi,

    So as per converstaion the I am thinking Diffrential backup not make any impact on Log shipping.

    Am I right ?

    I have implemented logshipping on a VLS DB and currently daily fullbackup is taken.

    So i am thinking for following steps or maintenance plan

    1) Take diffrential backup Daily

    2) Take fullbackup on Weekend

    Any Other suggestions expert ?

    Thanks

    Raj

    http://www.indiandotnet.wordpress.com

  • kiranjanjanam (11/8/2010)


    The Maint. Plan has three tasks...

    1) Diff backup (above code)

    2) T-Log Backup

    3) Full Backup with COPY_ONLY option

    There's your problem. It's not the diff backup that's breaking the log shipping, it's that log backup that's in there. If you're doing log shipping, all log backups must be done through the log shipping and no extra log backups can be done anywhere else.

    I'm curious, why take a diff backup then a full backup. Seems very strange, usually the point of a diff backup is when there's not enough time for a full.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • If you read the posts carefully you'll see they send the Diff backups to the client every night. This is more efficient than sending a full backup every night.

    Then they have a full backup for their own usage. More relevant for me would be why do the full every night if they are already doing the differential.

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • Thanks guys for your replies...

    After seeing the posts, I think its the log backups taken out of log shipping that is breaking the log shipping.

    i will communicate with the client and will disable that step.

    And I have one more question...Shrinking all the DB's everyweek in a good or bad thing to do? I have a shrink db step in my maint. plan and the maint. plan failed beacuse shrink db and backup's executed at the same time...

    any suggestions please????

  • kiranjanjanam (11/10/2010)


    Thanks guys for your replies...

    After seeing the posts, I think its the log backups taken out of log shipping that is breaking the log shipping.

    i will communicate with the client and will disable that step.

    And I have one more question...Shrinking all the DB's everyweek in a good or bad thing to do? I have a shrink db step in my maint. plan and the maint. plan failed beacuse shrink db and backup's executed at the same time...

    any suggestions please????

    Under 98% of circumstances, don't shrink your files unless you've just done a massive, one-off, operation. They'll just have to regrow, costing you more time and resources.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • kiranjanjanam (11/10/2010)


    Thanks guys for your replies...

    After seeing the posts, I think its the log backups taken out of log shipping that is breaking the log shipping.

    i will communicate with the client and will disable that step.

    And I have one more question...Shrinking all the DB's everyweek in a good or bad thing to do? I have a shrink db step in my maint. plan and the maint. plan failed beacuse shrink db and backup's executed at the same time...

    any suggestions please????

    After going through lot of post and self test i will suggest please do not shrink file if you are doing then shrink with no truncate option.

    Thanks

    Rajat

    http:/www.indiandotnet.wordpress.com

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

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