• rferreira.dba (2/20/2013)


    Let me put this another way: Is there any valid reason for Microsoft not to fix this?

    Because there's nothing broken here?

    The kb article you referenced refers to a problem where an incorrect insert is made into the backup history tables reflecting a backup that did not happen, not the case where you really are taking valid backup of the database. The backup history tables do not drive valid restores, they're just a record of what backups happen. The issue you have is not related to that kb article.

    In your case, you are taking two types of full backups and the differential that runs is correctly based on the latest full (whatever it may be). This isn't a bug, this is documented, normal behaviour

    You can get into exactly the same mess with two scheduled of native backups, where one is taken off site and not available. A differential backup is always based on the latest non-copy only full backup

    Is this possible? Is there an after_successful_snapshot trigger somewhere, or something similar that I can use?

    As far as SQL is concerned, that's just a database backup run by a 3rd party product. No less valid or correct than a normal database backup. Can you hook into whatever is running the snapshot backups and have that run some SQL afterwards?

    Options:

    - Set the backup app to snapshot the machine but exclude the databases. Some tools can, some can't. If you have a good SQL backup strategy, as it sounds like you have, then there isn't a need to snapshot the databases as well.

    - Set the backup app to do a 'copy only' snapshot. Probably won't be an option, haven't seen that as a setting in many tools.

    - Change the backup strategy to be a mix of full and log backups. If you're not doing differentials then the additional full backups won't have any effect on restoring. Not the best option, probably the last option if nothing else is possible

    p.s. This is why I always say that all SQL backups should be done the same way and that there should only be one method for the SQL backups. Native or 3rd party, not a mix of both.

    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