1

We currently have a front end application that is business critical and uses Microsoft SQL Server as the database provider. I have been asked to create another system that mimics some of the functions of this application and will insert/modify data in exactly the same way.

The only reason we are looking at doing this custom is because the developer does not provide any sort of API or assistance in manual data entry of their tables. Please note, we own the databases and all the data on the system but we still need the application to perform the day to day tasks.

I am looking for ways to track all changes made in a database (insert/update/delete) on all tables. I have a fresh copy of the system and a blank database to test, to give an example, I would generate a Purchase Order and then find out which tables were modified and what data was added so I could replicate in my code.

I know SQL has Data Change Tracking; however, I cannot see a way to easily implement this on all columns and tables within a database.

I am interested in any recommendations of third party applications or a walkthrough on how to utilise Microsoft's built in functions, appreciate the help if any.

2
  • What is the intent of the database copy? Will you use it only for reporting? If you are modifying data in the copy, are you intending on making those changes to the source database?
    – Hannah Vernon
    Commented Jan 28, 2017 at 0:28
  • @MaxVernon The copy is for the new system, it is blank so anything i do on the application is new to the database, i could just go through manually and look at each table, however there are 200+ tables and don't fancy that job, i would also need to go over multiple times for each action i perform
    – Recnats
    Commented Jan 30, 2017 at 9:42

1 Answer 1

1

I would recommend to script a loop or cursor to enable this on all of your databases and tables. It will capture all of the columns automatically.

Example of loop CDC Note: As always with code on the internet, evaluate cautiously and promote through personal and dev environments prior to production.

By default, all of the columns in the source table are identified as captured columns. If only a subset of columns need to be tracked, such as for privacy or performance reasons, use the @captured_column_list parameter to specify the subset of columns.

CDC Configuration MSDN

I think it would be better to run SQL Profiler though to catch the code that is running and re-create it that way however. CDC will show you the end results, not how it gets from A to C.

2
  • Thanks for the clarification, any chance you have an example of how i would code this? Loops and cursors are not my strongest point
    – Recnats
    Commented Jan 27, 2017 at 16:23
  • I found this example of a person using a loop to enable CDC on their DB, I'd dissect it in your dev or personal environment for testing and promote cautiously. myhumblesqltips.blogspot.com/2012/10/… Commented Jan 27, 2017 at 16:29

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.