How to get snapshot of changed data and store them on daily basis I have a requirement from my client. He wants to store a snapshot of what data has changed for some given tables, mainly financial transaction tables, on daily basis. This is for audit purposes. Data is to be stored table wise, so that we can develop some reports based on it to keep track of changes on daily basis. What are the possible techniques for this in SQL Server 2008 R2 Enterprise Edition? I am also interested in the latest available version as we are planning to migrate to latest SQL Server version.
Enable Change Data Capture on the tables of interest. Copy the rows thus flagged to another table that matches the definition of the first, but with an additional datetime column to show when it was written. Essentially a slowly changing dimension, type 2 in the Kimball nomenclature.



SQL Server 2016 introduced temporal tables. These combine the features of CDC and an audit table I mention above. Once defined, the system itself copies row versions to a history table as data changes. All history is retained until explicitly deleted. Each row has a time interval recorded showing when it was valid.