The backup plan



  • I was looking at our database while writing some documentation and came across these interesting tables:

    tblapp_int_stats2_04
    tblapp_int_stats2_05
    tblapp_int_stats2_06
    tblapp_int_stats2_07
    tblapp_int_stats2_08
    tblapp_int_stats2_BACKUP
    tblapp_int_stats2b
    tblapp_int_stats2b_08

    These are all the same table.  Just different years worth of data.  And of course, random backup versions of random years worth of data.



  • Not bad... I've seen this before in both the database (production, no less) and with deployed code (App, App_BAK, App.April_3_2009BAK, App.April_3_2009BAK2, App-DELETEME, etc.).



  • Unfortunately this seems like it was a design pattern.  There are about 20 tables in this database that repeat this with different years and different backups.



  • Very often the way.

    Backup strategy is rarely considered early on in the process. Someone decides they want to see what happened last year. Oh shit, we don't keep data back that far. Okay, so change the code so that we do. How far back? Oh, as far back as it goes. Shit, that's a big ask ... bollocks, I'll just slap a year number on the end. 2 digits will do, by the time the next century comes around I'll probably not be maintaining this crapfile ...



  • @Peraninth said:

    Unfortunately this seems like it was a design pattern.  There are about 20 tables in this database that repeat this with different years and different backups.

    That would be really lucky! But my experience says too: these tables were made manually to resolve some stupid problem and there was no opportunity to it in another way. And then they thought: lets keep the tables around, just in case.



  •  Check for any views that may UNION ALL those tables.  Partitioned views are not all that uncommon and have some great benefits when contraints are properly applied to the underlying tables.

     Look at these [url=http://www.novicksoftware.com/presentations/sql-server-partitioning/partitioning-sql-server-tables-views-indexed-views-andrew-novick-2009-01-24.pdf]slides[/url] from Andy Novick (MVP) for some explanation



  • @galgorah said:

     Check for any views that may UNION ALL those tables.  Partitioned views are not all that uncommon and have some great benefits when contraints are properly applied to the underlying tables.

     Look at these slides from Andy Novick (MVP) for some explanation

    You give my co-workers far too much credit.  I think TGV nailed the reason this is the way it is.



  •  @Peraninth said:

    You give my co-workers far too much credit.  I think TGV nailed the reason this is the way it is.

    Perhaps, but one can hope can't they...

     



  • @Peraninth said:

    I think TGV nailed the reason this is the way it is.

    This kinda behaviour bugs me.

    Not someone being correct... I mean this dichotomy between "it's important we have backups" and "it's not important enough to have a clearly-defined process, well-identified file names and proven recovery procedures that someone carefully manages."

    <yoda>Do or do not.</yoda> There ain't any place for half-arsed measures like this.



  • @Cassidy said:


    <yoda>Do or do not.</yoda>

    May the performance be with you

     


Log in to reply