Unable to restore Differential backup

  • You could use the high-performance (asynchronous) mode. This would ensure that all transactions are committed locally.

    You still have the possibility of some lost transaction with your current t-log process evry minute. Due to a hardware failure (loss of the t-log) you could loss up to 1 minute of work.

    Just throwing out other ideas here.

  • usman.tanveer (6/9/2009)


    The problem with mirroring would be, in HA mode it will not commit the transaction on primary untill commited on secondary, which could be a performance hit as the secondary server is at a remote location. Also if it failed to commit on secondary it will roll back the transaction on primary BUT we cannot aford transaction failure AND transaction delay on primary due to any HA architecture.

    If your hardware is good and the network between the two is fast (dedicated) there shouldn't be a problem. Rather than mirroring to the remote secondary, get a second local server for the mirror and leave the remote as a log shipping destination. At least with mirroring you can have 0 data loss if the primary fails.

    Test it out in a lab, see how well it works under your production load.

    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
  • I don't think a 1 minute Tlog interval is too much. I've run that before and it works OK. HOWEVER, it doesn't mean you won't lose data. The important thing is to get the files off the server ASAP, which is a copy somewhere immedaitely. You can log restore (in log shipping) after 5-10 minutes (and I'd recommend a delay), and also send the files to remote locations.

    However, the same log transactions go in log shipping or mirroring, with arguably more overhead in log shipping with the file copies. I'd think non-synchronous would be the way to move for DR, but keep local log backups and your full/diff strategy on another local machine as well.

  • Tanveer,

    Yes. Performance and IO is the big concern if you have very active db. I think Gail already asked u the question on how much data you could afford to lose if disaster hit. That is usually the question we ask for our client if we want to setup tlog backup interval. 5-10m interval should be sufficient in your case. you might also consider to reduce the frequency of diff backup. any full or diff backup will block tlog backup.

  • djjwu (6/9/2009)


    Any full or diff backup will block tlog backup.

    Not in SQL 2005 or above they do not.

    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
  • djjwu (6/9/2009)


    Tanveer,

    Yes. Performance and IO is the big concern if you have very active db. I think Gail already asked u the question on how much data you could afford to lose if disaster hit. That is usually the question we ask for our client if we want to setup tlog backup interval. 5-10m interval should be sufficient in your case. you might also consider to reduce the frequency of diff backup. any full or diff backup will block tlog backup.

    Full and Differential backups no longer block t-log backups starting with SQL Server 2005.

  • usman.tanveer (6/8/2009)


    Hi,

    "

    Msg 4305, Level 16, State 1, Line 1

    The log in this backup set begins at LSN 149014000003456800006, which is too recent to apply to the database. An earlier log backup that includes LSN 148750000003326500001 can be restored.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE LOG is terminating abnormally.

    "

    So, did you get the database restored?

    Greg

  • Sorry Greg i should have provided the update on the actuall post too:-).

    But yes problem fixed - by mistake i was using restore LOG instead of restore DATABASE for the differential backup, and as ALWAYS Gila was right on top of it by pointing out the exact thing.

    Thanks everyone who responded.

Viewing 8 posts - 16 through 22 (of 22 total)

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