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_08These 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:
Perhaps, but one can hope can't they...You give my co-workers far too much credit. I think TGV nailed the reason this is the way it is.
-
@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.
-