May 1, 2002 at 1:55 pm
Hi,
I maintain a huge SQL Server Database (mostly storing data used for reporting statistics), like a datawarehouse, where millions of rows are added everyday. I need to know if there is a process to archive old data, Example: all of 2001 data ( and remove this data from the production Dadabase, and be able to retrieve it onto another system if needed for reporting.
hanks
May 1, 2002 at 2:14 pm
I bcp out my data into text files, zip them and move them off the server. Then you can delete the data you want knowing that you could restore it when and where you want. You could also backup the database and then delete as well.
May 1, 2002 at 2:28 pm
We do something similar for log files we have in a production system. Basically, we have a linked server created where we first move the relevant rows, then we delete the rows on the current server. However, we're not dealing with the number of rows you are.
BCP is probaby a much better solution. You can build a process to bcp and zip as cjlane has suggested, then unzip and populate on your "history" server.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
May 1, 2002 at 2:31 pm
Thanks !!
Once I do backup the old data, deleting seems to take a lot of time and also fills up the trasaction log. Is there no other way but to delete the old data using the delete statement
May 1, 2002 at 3:32 pm
Unfortunately you cannot do a truncate table on the data since you will get the good data too. Some things that might help is to do the deletes in pieces and truncate the transaction log. Also to speed things along you might consider dropping all non-clustered indexes unless it is the primary key index as these indexes will have to update each delete item that occurrs. Then add them back when done. Then for future maybe consider creating a table for each year so you can drop them out when you need to so you can use truncate instead.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply