November 7, 2002 at 1:03 pm
I just inherited a database that has a historical table
with several million records. I intend to use a date field
to "archive" older data, remove it from the table to a file,
but maintain the ability to reload it and reload subsequent "archives". I'm very familiar with Oracle but not Sql Server.
What is the best/most efficient way to perform this?
I've been looking at DTS but haven't seen anything intuitive to do this task.
Thanks - Steven
November 7, 2002 at 1:09 pm
I think you can create an archive table for keeping the historical data in any other machine which has space in it. Other option is Create a batch file to append the data to a file.
November 7, 2002 at 1:25 pm
BCP is another option, but I personally create databases in break downs. For instance we collect call data from an Aspect call center switch which we keep 2 years live on our server. However previous years are archived by created a DB name CCXXXXYEARXXXX with all the same table structures, we copy the data over for the year, back up that DB to disk, compress the file and put on CD, and finally detach the archive DB. However, with version changes this can be a future problem so I do understand the whole to file thing. USe either DTS which is simple to use and anyone can help point you around anything you might have trouble with. Also, BCP is a command line interface and real simple to use. One note, I would not suggest writting all the data to a single file personally, I would break it up.
BCP documentation can be found in SQL BooksOnline as bcp utility.
November 7, 2002 at 2:14 pm
When we remove data from tables to files, we use bcp for a table by table basis, and write the bcp configuration file along with it for easy reload, if needed....We break our files out into subdirectories based on the table name as well, which lends itself to a programmatic solution in order to automate the unloads and reloads. Also makes for easier finds in case of needing to visually look for a particular file.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply