Use Database Snapshots
Use SQL Server's built-in Database Snapshot functionality. In the article on Typical Uses of Database Snapshots, the following is listed as a common use case:
Reasons to take database snapshots include:
- Maintaining historical data for report generation.
Because a database snapshot provides a static view of a database, a snapshot can extend access to data from a particular point in time. For example, you can create a database snapshot at the end of a given time period (such as a financial quarter) for later reporting. You can then run end-of-period reports on the snapshot. If disk space permits, you can also maintain end-of-period snapshots indefinitely, allowing queries against the results from these periods; for example, to investigate organizational performance.
This describes exactly what we want to do. However, I have some serious concerns about the implementation. In the article, Limitations and Requirements of Database Snapshots the following drawbacks (among others) are listed:
- Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.
- Database snapshots always work on an entire database.
- If a database snapshot runs out of disk space, it must be deleted (dropped).
That last one is a show stopper. The production database gets moderate use and we are looking to maintain the archives for 10+ years at least (at two per year).
We have never actually tried this approach, but it sounded intriguing (at least initially). However, this functionality seems to be geared more towards temporary or rolling snapshots, not indefinite use (though the use case statement specifically says indefinite use is possible).