On March 28, 2003 08:20 am, Riho-Rene Ellermaa wrote:

> What is the recommended, most reliable and most easily developed solution
> for archiving old data? I want my application periodically archive old data
> from various tables.
>
> I thought to create external tables and use SP to write data there.
> This way users can have access to older data also with other tools and my
> GDB file would also be smaller. HOw does this solution survive backup
> restore?
>
> Other thought was to have duplicate database and pump data there.
>
> Or are there better solutions?
>

It depends on what kind of access you need to the old data, is it for
posterity, government requirements, or something else? And what kind of
access do you need to it?

For example if you need to have old data available because the government
says a copy of it must exist somewhere, for x number of years; then you would
handle it differently, then needing it to be available to users somewhere.

If you need it all available to users, then a database, and an intelligent
pump mechanism is probably the best, even though you know that the big
massive .gdb file is going to be slow. They have a name for this, data
warehousing, it's a good analogy, a warehouse being a big building full of
crap, a data warehouse being a big database full of crap. Can you tell it's
Friday, yet :-)

If it doesn't need regular access, for example it's needed for regulations,
then I would write one or more fixed format reports, dump the reports to a
file, put those on removable media (CDR, Zip disk, even floppy), and store
them somewhere safe. The reasoning here is that no complex, platform
specific software is needed to later read the reports, if someone does need
to read them some day. If they are not needed, you can simply destroy the
media at the end of the storage period.

Third option, if you need access to some of it, but not all of it. Usually
you don't need all of the detail data, long term, but you do need some
summary data. In this case you create the fixed format reports, and dump the
detail data to the report, summarize and store the summary data, dumping the
detail for older periods. Again you move the fixed format reports to
removable media, and store those somewhere, safe. The summary data, if
small enough, could be kept in summary tables, in the main database.

A good example of summary data, we need a total of sales for each month going
back 10 years, for the Sales Year Over Year Report, but we don't care what we
sold, or whom we sold it to, it's a 2 column table with 120 rows.