How to preserve old data that might have to be reinserted into the database?

Question

We have a partitioned view with 12 member tables. The partitioning column is a date, we have one table for each month. Data are continually being inserted into the table of the current month. Older tables are constant.

At the beginning of each next month a new table is going to be created and added to the view. At the same time we are going to remove the oldest table from the database but we have to preserve the data somehow because in the future we might have to reinsert those data into the database temporarily for analysis and comparison. For example in june 2011 when the partitioned view will have member tables from july 2010 to june 2011 we might have to reinsert the data of june 2009 and june 2010 temporarily.

My question is: How would you do this 'preserve' and then the 'reinsert' operation? Is there a recommended way or a well-known pattern for this?

We are using SQL Server 2005 Standard Edition. I'm a novice in database administration.

Thanks in advance.

(EDIT: Why might someone use partitioning and a new table for each month? See SQL Server 2000 Partitioned Views and SQL Server 2005 Partitioned Tables and Indexes)

Answer 1

Why not use the bulk copy tool (bcp) to export the contents of your table to a file.

You can then zip it up and keep it somewhere safe until you need to use the bcp tool again to recreate the table.

To export

bcp yourdb..Stuff200901 OUT Stuff200901.bcp -T -S yourdbsvr -r "\n" -t "|" -c

To import (note you'll need to create the table)

bcp yourdb..Stuff200901 in Stuff200901.bcp -c -S yourdbsvr -T -t "|" -k -e err.txt

Type bcp to get a full list of options at the command prompt.

Answer 2

Probably not the answer you want, but I'll say it anyways - this is typically what you would use a data warehouse for. Shuffle those old months off to the data warehouse and do your reporting & analysis there.

Answer 3

with data storage being so cheap these days why not keep the all the data in one table filtering it by date. Put an index on that date to help with performance as the table size grows.

If that is not an option I would just create a back up of the table that you can restore from later or create insert statements from the data you can save to a sql file and run at any time.

Answer 4

Well. I don't know if I understand 100% what you mean. But as I understand it, you have one table for each month of data, then use a view to merge serveral tables together (using unions I guess ?). If that's the case I wouldn't do anything except keep creating new tables for each month, and modify the view to remove old tables like you said. Then to "reinsert" old data I would just create a new view that includes all tables or modify the current if feasible. There should be no reason to delete the old data from the database.