如何归档我的生产数据库中的数据 [英] How to archive data from my production database

问题描述

如何归档我的生产数据库中的数据？ SQL Server是否内置了一个工具或命令？















我们的SQL Server数据库（SQL Server 2008标准版，因此是R2）越来越大多年的数据存储。因此，我们正在考虑将旧数据（可能超过一年）移动到存档数据库中。我们将生产数据库缩小的原因有很多原因。


这就是我打算这样做的方法（以Stock数据库为例）。对于Stock数据库，现在将有Stock和Stock_Archive数据库。 StockResults表将在Stock数据库中命名为StockResults_Current，在Stock_Archive中命名为StockResults_Archive。在Stock数据库中，将有一个名为StockResults的视图，它将两者联合起来。在常规时间，我们将归档我们的数据（即将其移动到Stock_Archive数据库）。这是基本的想法。



我考虑过将旧数据放在不同的文件组中来对这些表进行分区，但我想保持主数据库的小 - 不只是一些数据文件。



有没有人尝试过类似的东西？或者有关于尝试以这种方式存档的建议？或者可以推荐一种更好的方法吗？



我不太关心存档数据的性能，但我不想读取和写入当前数据受到影响。我们的报告使用了像StockResults这样的表格。



我打算为存储大量数据的所有交易表执行此操作。



我们非常感谢任何建议。

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.

推荐答案

参考：

如何归档我的生产数据库中的数据？ [ ^ ]

这篇关于如何归档我的生产数据库中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！