1.1 SQL Change Tracking – Auditing – enable - disable- SQL Server Change Tracking, also known as CT, is a lightweight tracking mechanism, introduced the first time in SQL Server 2008, that can be used to track the DML changes performed in SQL Server database tables - Enabling SQL Change Tracking in Database In the DB Properites “Change Tracking” - Or enable in Script o ALTER DATABASE [CTAudit] o SET CHANGE_TRACKING = ON o (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON) - Once enabled in DB level then implement in Table level o USE CTAudit o GO o ALTER TABLE Employee_Main o ENABLE CHANGE_TRACKING o WITH (TRACK_COLUMNS_UPDATED = ON) - Disable tthe CT in DB level - SET CHANGE_TRACKING = OFF - Disable CT in Table Level DISABLE CHANGE_TRACKING - Retrive the data from CT (Employee_Main is table name) o SELECT * FROM CHANGETABLE o (CHANGES [Employee_Main],0) as CT ORDER BY SYS_CHANGE_VERSION |
