How to archive data from my production database? Is there a tool or command built-into SQL Server for this?
Our SQL Server databases (SQL Server 2008 Standard edition, so to be R2) are getting bigger and bigger from years of data gets stored. So we are thinking of moving the old data (possibly older than a year) into an archive database. There are many reasons why we would benefit from having our production databases smaller.
This is how I plan to do that (using the Stock database as an example). For the Stock database, there will now be Stock and Stock_Archive databases. The StockResults table will be named StockResults_Current in the Stock database and StockResults_Archive in Stock_Archive. And in the Stock database there will be a view called StockResults that unions the two. At regular times, we'll archive our data (i.e. move it to the Stock_Archive database). That's the basic idea.
I have considered partitioning these tables by putting old data in a different filegroups, but I want to keep the main database small -- not just some of the data files.
Has anyone tried anything similar? Or have advice on trying to archive in this way? Or can recommend a better way?
I'm not too concerned about the performance of the archived data, but I don't want reading and writing of the current data to be affected. Tables like StockResults are used by our reports.
I'm planning on doing this for all transaction tables that store a lot of data.
Any advice would be much appreciated.
Submit an article or tip