Hello Andy,

thanks for the article. But I think this only moves the problem around.

Archiving in my eyes means to move the data out of SQL Server.

Therefore I usually take the following approach:

I have a DTS Package that exports data from a table using BCP and deletes the data after export.

It takes the table name, retention period in months and date column as argument.

When exported, you zip the data (most cases this shrinks the size to 1% of the original size).

But I truly agree that archiving is an important topic. In "dark" past, I usually did not think about archiving until the server was too full. But thank god these times are over now. Every application now requires a space estimation (initial size and growth per month) and more important - an archiving strategy before you deploy the application. This archiving strategy is usually the mentioned DTS Package which can be called for any table that has a suitable date column.

One implication of the DTS Package is that it requires the original table structure to be able to recover the data. So if you add or remove one column from the table, a BCP export will not import successfully back into the table if it does not also contain this column. So this means you also need proper source control to make sure you can not only restore the data but also the structure to a point in time.