Versioning data in SQL Server so user can take a certain cut of the data

I have a requirement that in a SQL Server backed website which is essentially a large CRUD application, the user should be able to 'go back in time' and be able to export the data as it was at a given point in time.

My question is开发者_开发技巧 what is the best strategy for this problem? Is there a systematic approach I can take and apply it across all tables?

---

Depending on what exactly you need, this can be relatively easy or hell.

Easy: Make a history table for every table, copy data there pre update or post insert/update (i.e. new stuff is there too). Never delete from the original table, make logical deletes.

Hard: There is an fdb version counting up on every change, every data item is correlated to start and end. This requires very fancy primary key mangling.

---

Just add a little comment to previous answers. If you need to go back for all users you can use snapshots.

---

The simplest solution is to save a copy of each row whenever it changes. This can be done most easily with a trigger. Then your UI must provide search abilities to go back and find the data.

This does produce an explosion of data, which gets worse when tables are updated frequently, so the next step is usually some kind of data-based purge of older data.

---

An implementation you could look at is Team Foundation Server. It has the ability to perform historical queries (using the WIQL keyword ASOF). The backend is SQL Server, so there might be some clues there.