March 12, 2004 at 5:58 pm
Hi,
I have a backup strategy question that I'm hoping someone can help me with. We are setting up data warehouse where we are collecting raw website traffic data into staging, then munging the data into fact and dimension tables, and finally moving the raw data into an archive database.
Instead of putting the raw data into one gigantic archive table we want to divide it up into calendar quarters, so right now, for instance, all the raw data would into a table called '2004_Q1_web_traffic_data', starting in April we would go to '2004_Q2_web_traffic_data' etc. We would use check constraints to make sure data went to the right table.
We want to keep the data in the archive database for two quarters, which means that by September 2004, we would like get rid of the '2004_Q1_web_traffic_data' table. However we would like that table easily restorable if we need to use it for some reason down the road.
I had originally thought to create each quarter's table in it's own filegroup. Then I would backup the filegroup until that table will no longer be updated. Then I would drop the table and the filegroup once I didn't need it. If I wanted to restore it I would just restore the filegroup backup file. Unfortunately I don't think it's as simple as that since the backups will no longer be time consistent.
The other 'strategy' I thought of was to backup the table to a flat file and then dropping the table.
Any help/advice would be appreciate it.
Thanks,
Pete
March 14, 2004 at 9:50 am
If the table and filegroup are consistent within themselves and there are no changes to other tables that affect then, then consistency shouldn't be an issue.
Other than for performance, filegroups seem like a pain to me and I'd likely use a separate table if I could and backup to flat file.
March 15, 2004 at 11:06 am
Steve,
Thanks for the reply. I too am leaning towards the flat file method as well. As archaic as that sounds it just might be the best suited for this purpose.
Thanks again,
Pete
March 15, 2004 at 11:40 am
One advantage I have towards flat files is recovering very old data. You may not need to retain or restore after a long time. But I have been building a datawharehouse with input data going back to 1990.
If these had been done as DB level backups I probably could not even try to restore them. (MF DB/2).
Since I have all of the data in flat files I can easily extract the data I need and restore it.
KlK
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply