question on Index Rebuilds

  • p.s.  Just in case there's a question about where I stand on this, I DO absolutely agree that taking a dif backup before and after Minimally logged rebuild should be done whether you're in the Bulk Logged or Simple Recovery Model.  The only point that I'm trying to make is that there is no reason to resort to breaking the log file chain by using the Simple Recovery Model.  I DO agree that a restore of Minimally Logged transactions files would replay the index rebuilds and so would take longer and that's the reason for the difs before and after but you cannot predict that you won't need to do a restore to sometime (definitely not a precise PIT) before that second dif.

    To be sure, not all of what is in a Minimally Logged transaction log file is all minimally logged.  Things like Inserts, Updates, and Deletes are still fully logged and will be recorded in the log file even though the Minimally Logged flag has been set on the file.  Also remember that the Minimally Logged rebuild actions are what is recorded.  The data must be present in the data files (you're screwed either way, if it's not).  The thing that makes Minimal Logging so small for index rebuilds is that it records the "positions" of data rather than the data itself.

    We differ in opinion only on whether or not using the Bulk Logged or Simple Recovery Model would be a "Pyrrhic victory" victory or not.  I'm simply not willing to take a chance on losing data from Inserts, Update, or Deletes during Minimally Logged index maintenance actions.  Going to the Simple Recovery Model offers no chance of recovering to some point in the middle of the rebuilds and that would not be any kind of a victory for me.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    We differ in opinion only on whether or not using the Bulk Logged or Simple Recovery Model would be a "Pyrrhic victory" victory or not.  I'm simply not willing to take a chance on losing data from Inserts, Update, or Deletes during Minimally Logged index maintenance actions.  Going to the Simple Recovery Model offers no chance of recovering to some point in the middle of the rebuilds and that would not be any kind of a victory for me.

    Further to this - you both are making assumptions about the availability of backup files and the ability to use those files for a restore and that those files do not contain any corruption.  If - for any reason - those backup files before and after the index rebuild cannot be used, for example - an IO issue occurred that caused corruption some time before the diff - you cannot use that diff to restore else it just restores the corruption.

    The path to recovery is from the last known good backup - and then all transaction logs from that point forward.  In Scott's case - switching to simple recovery breaks that restore path and he is limited to recovering to the point in time just prior to switching recovery models.  That is a risk I would not be willing to take with any of my systems - even if it can be shown to be a minimal risk.

    Restoring to the middle of an index rebuild process isn't the biggest concern.  Recovering past that point is much more concerning and breaking the log chain to 'save' some space is a much larger risk than some disk space savings.

    If you want to be able to guard against those types of IO issues, then you really want to setup AG's (even BAG's) for your critical databases as that allows SQL Server to automatically recover from some IO issues.  Of course - if you implement an AG then you cannot switch the recovery model.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    Jeff Moden wrote:

    We differ in opinion only on whether or not using the Bulk Logged or Simple Recovery Model would be a "Pyrrhic victory" victory or not.  I'm simply not willing to take a chance on losing data from Inserts, Update, or Deletes during Minimally Logged index maintenance actions.  Going to the Simple Recovery Model offers no chance of recovering to some point in the middle of the rebuilds and that would not be any kind of a victory for me.

    Further to this - you both are making assumptions about the availability of backup files and the ability to use those files for a restore and that those files do not contain any corruption.  If - for any reason - those backup files before and after the index rebuild cannot be used, for example - an IO issue occurred that caused corruption some time before the diff - you cannot use that diff to restore else it just restores the corruption.

    The path to recovery is from the last known good backup - and then all transaction logs from that point forward.  In Scott's case - switching to simple recovery breaks that restore path and he is limited to recovering to the point in time just prior to switching recovery models.  That is a risk I would not be willing to take with any of my systems - even if it can be shown to be a minimal risk.

    Restoring to the middle of an index rebuild process isn't the biggest concern.  Recovering past that point is much more concerning and breaking the log chain to 'save' some space is a much larger risk than some disk space savings.

    If you want to be able to guard against those types of IO issues, then you really want to setup AG's (even BAG's) for your critical databases as that allows SQL Server to automatically recover from some IO issues.  Of course - if you implement an AG then you cannot switch the recovery model.

    We're not forgetting anything.  We're talking about Minimal Logging for Index Rebuild and it's totally understood that you cannot change the Recovery Model for any of the things you bring up.

    What you state about corruption is for ALL backups and not just the ones we're talking about taking during a Minimally Logged evolution.  It's pretty much a given that if you have corruption in your database that it will affect backup and recovery no matter what.

    [EDIT]: Looking back at this, you're correct and I stand corrected... so far in this thread, the OP hasn't stated if he has such things in play and we're just assuming that, if he did, he would know better to than to change the Recovery Model to anything other than Full.

    The notes on corruption of the database are correct but how much can you not assume for such a post as rebuilding indexes?  😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    [EDIT]: Looking back at this, you're correct and I stand corrected... so far in this thread, the OP hasn't stated if he has such things in play and we're just assuming that, if he did, he would know better to than to change the Recovery Model to anything other than Full.

    The notes on corruption of the database are correct but how much can you not assume for such a post as rebuilding indexes?  😉

    I wouldn't assume anything...it just gets me into trouble 🙂

    My main concern is around anything that breaks the log chain - I don't disagree with switching to bulk-logged in principal...and in certain situations (dependent on the system, of course) have set that up to reduce the impact on the transaction log.  With that said, I would much rather just extend the drive for the transaction log - pre-grow it to the necessary size (with appropriately size VLF's) - and let the system work.

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    Jeff Moden wrote:

    We differ in opinion only on whether or not using the Bulk Logged or Simple Recovery Model would be a "Pyrrhic victory" victory or not.  I'm simply not willing to take a chance on losing data from Inserts, Update, or Deletes during Minimally Logged index maintenance actions.  Going to the Simple Recovery Model offers no chance of recovering to some point in the middle of the rebuilds and that would not be any kind of a victory for me.

    Further to this - you both are making assumptions about the availability of backup files and the ability to use those files for a restore and that those files do not contain any corruption.  If - for any reason - those backup files before and after the index rebuild cannot be used, for example - an IO issue occurred that caused corruption some time before the diff - you cannot use that diff to restore else it just restores the corruption.

    The path to recovery is from the last known good backup - and then all transaction logs from that point forward.  In Scott's case - switching to simple recovery breaks that restore path and he is limited to recovering to the point in time just prior to switching recovery models.  That is a risk I would not be willing to take with any of my systems - even if it can be shown to be a minimal risk.

    Restoring to the middle of an index rebuild process isn't the biggest concern.  Recovering past that point is much more concerning and breaking the log chain to 'save' some space is a much larger risk than some disk space savings.

    If you want to be able to guard against those types of IO issues, then you really want to setup AG's (even BAG's) for your critical databases as that allows SQL Server to automatically recover from some IO issues.  Of course - if you implement an AG then you cannot switch the recovery model.

    Obscure objection.  If there's corruption, you can't restore using your method.  You can't restore to the middle of the rebuild process.  Rebuild is atomic -- it works or it doesn't work, it doesn't partially work.  And you can't do point-in-time with minimal logging, as we all know.

    But I'm taking a FULL backup, which I can always restore, which means I don't lost the rebuilds.  The corruption will still be there, of course, but I can restore the db.  It's trying to apply a trans log that would likely fail due to the corruption.  I would think a differential would succeed, since it's putting entire pages back, not trying to re-play or reverse transactions.

    If you had to back up to address the corruption, once the differential applies correctly, it's also possible that a page restore(s) can fix the corruption without losing anything else in the db.  The rebuilds would be lost, but that's unavoidable in any case if the corruption forces the FULL backup to not be good enough.

    Of course you should also periodically verify that your dbs don't have corruption.  I've seen almost no corruption in 20 years of working with SQL Server (outside of bad hardware component(s)).  If the corruption is in a nonclus index, naturally you can just drop and recreate that index.  So it's only heaps and clus indexes that are really in danger of data loss from corruption.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher wrote:

    Obscure objection.  If there's corruption, you can't restore using your method.  You can't restore to the middle of the rebuild process.  Rebuild is atomic -- it works or it doesn't work, it doesn't partially work.  And you can't do point-in-time with minimal logging, as we all know.

    But I'm taking a FULL backup, which I can always restore, which means I don't lost the rebuilds.  The corruption will still be there, of course, but I can restore the db.  It's trying to apply a trans log that would likely fail due to the corruption.  I would think a differential would succeed, since it's putting entire pages back, not trying to re-play or reverse transactions.

    If you had to back up to address the corruption, once the differential applies correctly, it's also possible that a page restore(s) can fix the corruption without losing anything else in the db.  The rebuilds would be lost, but that's unavoidable in any case if the corruption forces the FULL backup to not be good enough.

    Of course you should also periodically verify that your dbs don't have corruption.  I've seen almost no corruption in 20 years of working with SQL Server (outside of bad hardware component(s)).  If the corruption is in a nonclus index, naturally you can just drop and recreate that index.  So it's only heaps and clus indexes that are really in danger of data loss from corruption.

    I am not concerned with restoring to the middle of an index rebuild - by breaking the log chain you have blocked the ability to restore past that point.  The point is that if you must revert to a previous backup and restore to a PIT after that break in the log chain - you have no restore path because the log chain has been broken.

    Switching to simple recovery and hoping you never have anything happen to your current backups - and that there is no reason to keep a previous backup around because you have a new backup is short-sighted at best.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    ScottPletcher wrote:

    Obscure objection.  If there's corruption, you can't restore using your method.  You can't restore to the middle of the rebuild process.  Rebuild is atomic -- it works or it doesn't work, it doesn't partially work.  And you can't do point-in-time with minimal logging, as we all know.

    But I'm taking a FULL backup, which I can always restore, which means I don't lost the rebuilds.  The corruption will still be there, of course, but I can restore the db.  It's trying to apply a trans log that would likely fail due to the corruption.  I would think a differential would succeed, since it's putting entire pages back, not trying to re-play or reverse transactions.

    If you had to back up to address the corruption, once the differential applies correctly, it's also possible that a page restore(s) can fix the corruption without losing anything else in the db.  The rebuilds would be lost, but that's unavoidable in any case if the corruption forces the FULL backup to not be good enough.

    Of course you should also periodically verify that your dbs don't have corruption.  I've seen almost no corruption in 20 years of working with SQL Server (outside of bad hardware component(s)).  If the corruption is in a nonclus index, naturally you can just drop and recreate that index.  So it's only heaps and clus indexes that are really in danger of data loss from corruption.

    I am not concerned with restoring to the middle of an index rebuild - by breaking the log chain you have blocked the ability to restore past that point.  The point is that if you must revert to a previous backup and restore to a PIT after that break in the log chain - you have no restore path because the log chain has been broken.

    Switching to simple recovery and hoping you never have anything happen to your current backups - and that there is no reason to keep a previous backup around because you have a new backup is short-sighted at best.

    The full backup taken after the rebuilds is your restore point.  The db is back in FULL recovery mode at that point (assuming OP normally keeps that db in FULL mode), so you can forward recover using that backup.

    I don't see anything being lost by doing the REBUILDs in SIMPLE mode.  You can't recover to a PIT in the middle of the rebuilds anyway, and you have a diff before and a full after the rebuilds to recover to if needed.

    However, if you "don't break the log chain", and something happens after the rebuilds, you WILL lose all data mods after the rebuilds, since you can't forward-recover using the minimally-logged log file.

     

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Again, though... they don't need to be full backups.  They can be diffs to save some time and, possibly, reduce the size of backups.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    Again, though... they don't need to be full backups.  They can be diffs to save some time and, possibly, reduce the size of backups.

    If you intend to go back to FULL recovery model after using BULK-LOGGED for the rebuilds, you'd want a full backup.

    Besides, after rebuilding multiple 100M+ row tables that are badly fragmented, I can't imagine a diff saving you time overall, since the diff is likely to be extremely large.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • That's not true, Scott.  Not that you should but you can go back to the FULL Recovery Model from the BULK_LOGGED recovery model at any time without any kind of backup whatsoever.  It's not like the SIMPLE Recovery Model in that case.

    The key is what may have been done in the log file.  Even if just one byte by one Minimally Logged action is taken, that log file carries a flag in it for the bulk operation, which will prevent a PIT restore to the middle of that log file.  It's not required but a very good practice is to do a log file backup both before and after an excursion to the BULK_LOGGED model to limit the time that such a log file covers.

    So far as what someone wants, whatever.  A DIF backup is usually good enough but if you want to burn some extra tape, then a FULL backup is also fine.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    That's not true, Scott.  Not that you should but you can go back to the FULL Recovery Model from the BULK_LOGGED recovery model at any time without any kind of backup whatsoever.  It's not like the SIMPLE Recovery Model in that case.

    The key is what may have been done in the log file.  Even if just one byte by one Minimally Logged action is taken, that log file carries a flag in it for the bulk operation, which will prevent a PIT restore to the middle of that log file.  It's not required but a very good practice is to do a log file backup both before and after an excursion to the BULK_LOGGED model to limit the time that such a log file covers.

    So far as what someone wants, whatever.  A DIF backup is usually good enough but if you want to burn some extra tape, then a FULL backup is also fine.

    I have to admit you're confusing me here.  I know I added to the confusion with my muddled wording earlier, particularly for non-DBAs.

    The tran log file backup taken after the (massive) rebuilds with minimal logging have been done must be huge, since SQL has to copy all the modified pages to the log (if I understand correctly, this is actually at the extent level, not the page level, so one page modified in an extent forces the full extent to be logged; but SQL may be able to determine that it needs to log only certain page(s) from that extent (?; such as if the page is still in a buffer??).

    I still think that, because of the page/extent logging, trying to forward recover to a point past the rebuilds would be an extraordinarily long process.  Not only do you have to restore the initial full backup to create a starting point, then SQL also has to restore (apply) all those modified pages from the extents in the log.  Not to mention the replaying of normal fully logged transactions from the log file.

    Timewise, that's typically like doing two full restores (at least).  In our environment, I don't have that kind of time to wait for a forward recovery.  (Plus, yeah, I also don't want to have a huge log backup file to take and then deal with.)  Thus, in my case, I'm better off taking a full backup that allows me to recover as quickly as possible to a time after the rebuilds.  I would have suspected it's the same for most shops -- you want to get the db back up as quickly as reasonably possible.  So, yes, I've "broken the log chain", but to me it's the lesser of two evils.  I prefer being practical to slavishly following "rules".

    Sorry, I know I didn't state this clearly before.  I hope I'm stating it clearly now.  I was very tired when I did some of those posts and so sometimes don't organize my thoughts well.

    Now, yes, I'm assuming that rebuilds of multiple 100M+ row tables would effect at least half the extents in the db (likely more, but even half should make a log-based forward recovery very time prohibitive).

    Is your thinking that the rebuilds wouldn't effect that many extents?  That only a (relatively) limited number of extents in the db would be effected?  I just don't see how that's a likely expectation.  I'd expect a rebuild of all huge tables that have shown that degree of fragmentation to effect far more than a limited number of extents of the table.  Definitely I would think it potentially could, enough so that I wouldn't risk having to a forward recovery past the rebuild point.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Jeff Moden wrote:

    p.s.  Just in case there's a question about where I stand on this, I DO absolutely agree that taking a dif backup before and after Minimally logged rebuild should be done whether you're in the Bulk Logged or Simple Recovery Model.  The only point that I'm trying to make is that there is no reason to resort to breaking the log file chain by using the Simple Recovery Model.  I DO agree that a restore of Minimally Logged transactions files would replay the index rebuilds and so would take longer and that's the reason for the difs before and after but you cannot predict that you won't need to do a restore to sometime (definitely not a precise PIT) before that second dif.

    To be sure, not all of what is in a Minimally Logged transaction log file is all minimally logged.  Things like Inserts, Updates, and Deletes are still fully logged and will be recorded in the log file even though the Minimally Logged flag has been set on the file.  Also remember that the Minimally Logged rebuild actions are what is recorded.  The data must be present in the data files (you're screwed either way, if it's not).  The thing that makes Minimal Logging so small for index rebuilds is that it records the "positions" of data rather than the data itself.

    We differ in opinion only on whether or not using the Bulk Logged or Simple Recovery Model would be a "Pyrrhic victory" victory or not.  I'm simply not willing to take a chance on losing data from Inserts, Update, or Deletes during Minimally Logged index maintenance actions.  Going to the Simple Recovery Model offers no chance of recovering to some point in the middle of the rebuilds and that would not be any kind of a victory for me.

    Again, I'm replying to this specific situation.  These are stand-alone rebuilds.  There are not other transactions occurring, as stated by the OP, and thus certainly not critical ones that absolutely can't be lost.  You can't modify the table while it's being rebuilt anyway, unless you're using ONLINE rebuilds, and there's no reason for that overhead here, since we expect app activity against the db to be stopped during the rebuild time anyway.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher wrote:

    However, if you "don't break the log chain", and something happens after the rebuilds, you WILL lose all data mods after the rebuilds, since you can't forward-recover using the minimally-logged log file. 

    Incorrect - you can restore all transaction log backups from the backup taken prior to altering the database to bulk-logged forward in time up to the latest transaction log backup available.

    What you cannot do is restore to a PIT in the middle of any of those transaction log backups where there is a minimally logged operation.

    As soon as you switch to simple recovery - you break the log chain and any transaction log backups will fail until you reset the log chain with a full backup.

    Again, if you have to revert to the backups prior to switching to simple recovery...you have no recovery path past that point.  If you switch to bulk-logged you can restore to the end of any transaction log taken between the alter to bulk-logged and the alter back to full - or to a PIT after switching back to full.

    And seriously, just because you haven't seen something happen doesn't mean it cannot occur or hasn't occurred.  I have seen plenty of weird and unexplainable failures in my 30+ years in this industry - failures that *could* never happen and yet still happened, regardless.

    Let's assume that your full backup after the index rebuilds was successful...but at some point after that the bak file is corrupted.  You cannot use that backup file to recover and you are unaware of the issue with the file (yet).  Someone decides to run a cleanup script - and accidentally deletes thousands of rows from a table...how do you recover that data if you no longer have access to that backup file?

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    ScottPletcher wrote:

    However, if you "don't break the log chain", and something happens after the rebuilds, you WILL lose all data mods after the rebuilds, since you can't forward-recover using the minimally-logged log file. 

    Incorrect - you can restore all transaction log backups from the backup taken prior to altering the database to bulk-logged forward in time up to the latest transaction log backup available.

    What you cannot do is restore to a PIT in the middle of any of those transaction log backups where there is a minimally logged operation.

    As soon as you switch to simple recovery - you break the log chain and any transaction log backups will fail until you reset the log chain with a full backup.

    Again, if you have to revert to the backups prior to switching to simple recovery...you have no recovery path past that point.  If you switch to bulk-logged you can restore to the end of any transaction log taken between the alter to bulk-logged and the alter back to full - or to a PIT after switching back to full.

    And seriously, just because you haven't seen something happen doesn't mean it cannot occur or hasn't occurred.  I have seen plenty of weird and unexplainable failures in my 30+ years in this industry - failures that *could* never happen and yet still happened, regardless.

    Let's assume that your full backup after the index rebuilds was successful...but at some point after that the bak file is corrupted.  You cannot use that backup file to recover and you are unaware of the issue with the file (yet).  Someone decides to run a cleanup script - and accidentally deletes thousands of rows from a table...how do you recover that data if you no longer have access to that backup file?

    Yes, sorry, as I stated in passing earlier, what I meant was that you "can't" "in reality" even if you can in theory because it just takes so long to do.  Again, I should have been clearer about that.  I guess, though, if you have no choice, you have no choice, you'd have to wait out the massive log restore(s).

    As I stated multiple times, after the rebuilds, I immediately switch the recovery model to FULL and take a full backup.  So naturally I can do any recoveries past that point using that full backup, including to a PIT after that.  You seem to be implying that there's some recovery you can do with your method that I can't do with mine, but I don't see it.

    How do you recover lost data in your method if the differential backup gets corrupted?  You now can't restore that diff, which is the only copy of the data in your method.

    Besides, that diff is likely to be almost the size of the db anyway.  I'd rather just do a full backup.  I see too many disadvantages of space and time to do a diff after rebuilds that large (again, I stated this same thing earlier).

    I've not seen a backup file so corrupted it can't be restored, at least in modern times (since SQL 2000), .  Maybe just lucky.  We do make two copies of backups on two separate tapes, if that matters here.    If you're that worried about backup corruption, include CHECKSUM in the backup.

    To me, this seems more about "breaking the rules about the log chain" than about actual processing and recovery of data.  I'll stick with the practical approach here when it's much more performant and much easier to recover with than when slavishly following the rules.

    On a different, but indirectly related subject, and one most vital for performance, the "rule" about "(always) using an identity value as the clustering index" is a horrible error.  The worst, most damaging myth in dbs.  So, yeah, I don't follow that "rule" either.  It's vital for best performance not to.  I see a diff backup here as part of a log chain that must be restored in the same way.  It's huge overhead and can really hurt performance for no actual gain -- so, in my mind, why do it?!

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher wrote:

    Yes, sorry, as I stated in passing earlier, what I meant was that you "can't" in practice even if you can in theory because it takes so long to do.  Again, I should have been cleared about that.  I guess, though, if you have no choice, you have no choice.

    As I stated multiple times, after the rebuilds, I immediately switch the recovery model to FULL and take a full backup.

    I can do any recoveries past that point by restoring that full backup, including to a PIT after that.

    How do you recover lost data in your method if the differential backup gets corrupted?  You now can't restore that diff, which is the only copy of the data in your method.

    Besides, that diff is likely to be almost the size of the db anyway.  I'd rather just do a full backup.  I see too many disadvantages of space and time to do a diff after rebuilds that large (again, I stated this same thing earlier).

    I've not seen a backup file so corrupted it can't be restored, at least in modern times (since SQL 2000), .  Maybe just lucky.  We do make two copies of backups on two separate tapes, if that matters here.    If you're that worried about backup corruption, include CHECKSUM in the backup.

    To me, this seems more about "breaking the rules about the log chain" than about actual processing and recovery of data.  I'll stick with the practical approach here when it's much more performant and much easier to recover with than when slavishly following the rules.

    On a different, but indirectly related subject, and one most vital for performance, the "rule" about "(always) using an identity value as the clustering index" is a horrible error.  The worst, most damaging myth in dbs.  So, yeah, I don't follow that "rule" either.

    I can recover using transaction log backups - because I can go to a *previous* backup file and apply all transaction log backups from that point forward - including a tail-log backup if something drastic has occurred.  If we lose those transaction log backups - then, of course we cannot restore...the point is that I don't *need* the DIFF to restore, but it does reduce the number of files I need to restore and will be faster than restoring all transaction log backups prior to that DIFF.

    This isn't about how much time it takes to recover - it is about how much data you can recover.  As soon as you break the log chain (and this isn't slavishly following the rules) - you no longer have the ability to recover across that break.  Just because you took a full backup after that does not *guarantee* that backup file is available - or that the database wasn't corrupted and you backed up that corruption - or many other issues that can occur.

    The point is - to me - very simple.  Why risk being able to recover your systems when you have a valid methodology available *that does the same thing*?  Use the exact same process and switch to bulk-logged instead of simple and you get the same benefits (minimally logged index rebuilds) and the added benefit of transaction log backups to recover across/past that process?

    It just doesn't make sense to me to add risk when then are better options with less risk available.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 15 posts - 31 through 45 (of 47 total)

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