Posted by M Bourgon on 07/17/07 20:31

On Jul 11, 4:03 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> M Bourgon (bour...@gmail.com) writes:
> > On Jul 10, 4:48 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> >> But why use BCP as a backup tool? Why not simply BACKUP? Or are you
> >> trying to tell us that this is the only table in a big database that
> >> you want to back up?
>
> > Nope. :) We're working on moving to Partitioned Tables, but right
> > now we have home-grown partitioning, and we need to deal with old
> > "partitions".
>
> And the scripting is part of that? Maybe you could give more details?

Sure. We have a large table that constantly has new records added. We
want to be able to go back historically and pull from this data set.
The way we currently do it is to keep several days (the "hot" data)
available in one table (whilst auto-archiving 1 days' data out to a
table on a daily basis) several weeks available by a partitioned view,
and archive the older tables. This way, when we get requests for
older data, we can easily look in a set of tables for the results. We
split it up by day due to volume, and also because most of our
requests are "this subset of data, from date A to date B, further
filtered". We've discussed different ways of keeping the data
available, as what takes up the least amount of space is not
necessarily the easiest to query. I like the idea of keeping the data
in a database because then we can easily query it, and we don't have
to worry about any issues (i.e. if we save it out, and the table
format changes, we're don't run into issues months from now when we
try to load the data).

One question you'll probably ask - how do you create the one-days-
worth-of-data table? Unfortunately, that's just a dumb script also,
destined to break if we wind up changing the format of the table.

> If the main purpose is that the import of the BCP in native format will
> work, maybe it sufficient to save the format file with the table? You
> can create a format file from BCP with the format option. (You use "format"
> in place of "in" or "out".)

Can the format file be used instead of DDL? I'll have to try it.

Thanks.

[Back to original message]