Third party backup tool breaking the backupset LSN chain

  • I am attempting to develop a workaround to this issue and was wondering if it is possible to modify the BACKUPSET lSN numbering of a backup so as to skip the piece or pieces of the backup completed by the third party tool so the next transaction log backup starts at the lsn numbering of the backup before the third party tool was run.

  • The simple answer, no. This information is also stored in the backup (full, differential, t-log) files as well as the tables in msdb.

    I'm sure that someone with more knowledge on this could provide a better answer.

  • Can the third party backup tool be configured to make the backup in "copy only" mode? (That should prevent the LSN from being changed and breaking the backup chain.)

  • Thinking about it, this third party tool is doing one of two things:

    1) Full or Differential backup with a log truncation or swithc of recevery model

    2) A transaction log backup

    Which is it?

  • can you please share the third party backup tool name you using?

    I am using litespeed since ages and never had any problem

    ----------
    Ashish

  • From the original post, it appears that the third party tool is being used inconjunction with the native tools. This, in itself, is probably what is causing the problem.

  • I'd be curious how you're incorporating the 3rd party tool into your native backups, assuming that's what's going on. Also, why.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • UMG Developer (3/23/2011)


    Can the third party backup tool be configured to make the backup in "copy only" mode? (That should prevent the LSN from being changed and breaking the backup chain.)

    Not unless it's log backups that the 3rd party tool is taking. Full backups do not break the log chain.

    http://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/

    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
  • In general I've found that mixing backup types (3rd party and native) is not a particularly good idea. Often OK if the 3rd party tool is just a VDI (taking the native backup stream and doing something, typically compression or encryption, to it), not necessarily so OK if it's doing some snapshot backup form

    I've successfully had LiteSpeed full and diff backups and native log backups without issue. I have had problems mixing snapshot-type backups with native log backups.

    What is the 3rd party backup tool? Have you checked the SQL error log to ensure that there's no switch to simple recovery happening? Have you checked the msdb backup tables to ensure that the 3rd party tool isn't also taking log backups?

    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
  • Thanks for your comments .The third party tool we are currently using is snap manager for sql server (IBM snap manager on a NetApp appliance) .What i am attempting to do is use log shipping from multi instance failover cluster on sql 2008 R2 server to a duplicate DR server but would like to pause the log shipping and once paused take snaps of the large DBs during upgrades etc. discard the snap shots on successful upgrade and continue with the log shipping if at all possible.

    The issue i have here is that i need to be able to delete all trace in sql server that these snap shots ever took place so that when i start the log shipping again it is unaware anything untoward has taken place and technically i not sure that this is even possible .

    To break the issue down even further i would like to use log shipping, native backups and snap manager together .

  • If what you are looking for re updates is something so that you can quickly undo data and schema changes, look at Database Snapshots (SQL feature). Unless you revert to the snapshot, it will have no effect on any backups.

    Otherwise, if the snapshot backup is mangling the log backup LSNs, about the only way you're likely to get that working is to take a full backup right after every log backup, then set up log shipping again with that new full backup.

    If the snapshot backup is doing odd things to the LSN (as you say) you won't be able to 'delete' the evidence. What's in msdb is just a record and is not what SQL relies upon for restoring. All log backups have the start and end LSN stamped in, if the end of one doesn't match the start of the next, the second log is not restorable, it can't be there are log records missing

    Honestly, I've never heard of much success in mixing snapshot and native backups together.

    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
  • Thanks Gail i am very quickly coming to the same conclusion that attempting to mix the two technologies is just to hard. We have a sql 2008 R2 failover cluster attached to an IBM N-Series (NetApp no longer supported by NetApp) and a duplicate DR setup as a hot stand by. What i am attempting to achieve is the ability to use snap shots into a SRM (site recovery manager test bubble) but still use sql side log shipping and backups as there is always a requirement to transfer copies of the sql backups to machines that are off this environment as native SQL backups.

    In these types of environments what would be the best way of making this environment work and also making effective use of the technology’s involved. I think that the biggest failing in this whole issue is that native sql backups should never be affected with third party tools.

  • Even though this thread is over 2 years old, for those who happen to stumble into it, this might help. Full snapshot backups taken by DPM (System Center) do not break the log chain. The attached image contains the first and last LSNs of sequential backups for a database. The backup (highlighted in yellow) is the snapshot backup and the log backups taken before and after this snapshot backup were taken using SSMS. As seen (highlighted in blue circles), the log chain remains unbroken in spite of the snapshot backup.

    Ajit Ananthram
    Blog - http://ajitananthram.wordpress.com

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

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