My article written on a subject of Introduction to Change Data Capture (CDC) in SQL Server 2008 is quite a popular and I have received many requests for uploading the script associated with this subject.
Change Data Capture records INSERTs, UPDATEs, and DELETEs applied to SQL Server tables, and makes a record available of what changed, where, and when, in simple relational ‘change tables’ rather than in an esoteric chopped salad of XML. These change tables contain columns that reflect the column structure of the source table you have chosen to track, along with the metadata needed to understand the changes that have been made. Pinal Dave explains all, with plenty of examples in a simple introduction.
SQL Server 2005 introduced the new features of ‘after update’, ‘after insert’ and ‘after delete’ triggers that almost solved the problem of tracking changes in data.  A better solution was introduced in SQL Server 2008 and is called Change Data Capture (CDC). CDC has allowed SQL Server developers to deliver SQL Server data archiving and capturing without any additional programming.
CDC is one of the new data tracking and capturing features of SQL Server 2008. It only tracks changes in user-created tables. Because captured data is then stored in relational tables, it can be easily accessed and retrieved subsequently, using regular T-SQL.
Download the Script
Reference: Pinal Dave (https://blog.sqlauthority.com)
