My requirement is to track old rows and modified rows from some particular table and the time when changes are made. Using "Change Tracking" and with help of any system table.

Due to some specific reasons I have to use change tracking as I was told not to use triggers and Change Data Capture.

Right now I am able to fetch current data and which column is modified. But in addition I want to track time of modification and old data.

But if anyone can help me to get any one of these two will be very helpful for me.

  • 3
    Without triggers or CDC? Ho hum. Stored procedures allowed...?
    – gbn
    Commented Feb 3, 2012 at 13:02
  • Yes we can use stored procedure but we are not maintaining any extra table for any input
    – user1166764
    Commented Feb 3, 2012 at 13:13
  • You can find some more additional information from my Article codeproject.com/Articles/537649/…
    – user18807
    Commented Feb 5, 2013 at 9:22

1 Answer 1


If you can't add any extra storage for the data (as noted in your comment) then you simply can't do this. SQL Server doesn't track the old and modified rows for you.

There's a duct-tape way of doing it temporarily: you can read the transaction log. When combined with the full chain of backups, you can build the old & new data together. Third party data recovery products like Quest LiteSpeed do this - for any transaction, they'll show you the old and new data by reading through the full backup and transaction log backups.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.