Detect DB Changes

I have to trace changes in my SQL 2k5 DB.  This could be anything from a SP to an addition to a row, change, modification, etc.

Is there a way to easily do this?  
LVL 2
chrisryhalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin HillSr. SQL Server DBACommented:
All DDL or DML (Schema vs. Data), or a mix of the two?

Tracking every data change is going to KILL your performance and is rarely needed except in the highest of secure applications.

If just schema, check out DDL triggers in Books Online:
http://msdn2.microsoft.com/en-us/library/ms190989.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
moss_guruCommented:
The two ways I know of doing this are 1) adding a trigger to each table to write audit records on change, and 2) periodic comparisons to a "Baseline DB" using third party DB comparison tools.  My favorite is SQL Delta (http://www.sqldelta.com/)...
0
climbingjaffaCommented:
In short no SQL Server 2008 will have a nice new feature but not for us on SQL 2005

You can track DDL changes using the new DDL triggers available in SQL Server 2005

http://technet.microsoft.com/en-us/library/ms190989.aspx

As for data chnages such as updates , deletes and inserts you would have to set up archive tables and triggers to populate them. See the link below it may be of help

http://www.sqlservercentral.com/articles/Advanced/dataarchivingproblemsandsolutions/1729/
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Kevin HillSr. SQL Server DBACommented:
So..you recommend taking the two previous answers?   ;)
0
Faiga DiegelSr Database EngineerCommented:
The SQL Dashboard (http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en) has one report for Schema Changes History. Try installing it to a non production environment and test if it meets your needs for schema changes logs.

For modifications/updates in a row... you should carefully plan this. One thing we did was setup a Report DB or archival DB. This temporary DB is updated every week. We use Red Gate to compare (Red Gate SQL Bundle 5 >> Data Compare) row by row updates/modifications. The SQL Compare (Red Gate product too) is also a very useful tool to compare schema changes (http://www.red-gate.com/products/sql_tools_overview.htm)
0
climbingjaffaCommented:
To answer Kevin3NF comment i had to go to a meeting while i was writing the comment when i started no one had added any comments when i came back only after i submitted i realised that i had been pipped to the post...

As for the neccesity of archiving its crucial in just about any enterprise system ..sadly software development isn't infalable (my spelling :( sorry ) developers write code that the customers acccept that can end up running wreck. And if you go auditing processes you'll to track deletes the phrase we do hard deletes can cause heart attacks in some circles. Soft deletes and updates within the table is another approach but do go down this road it takes performance and starts beating it black and blue.
0
climbingjaffaCommented:
Typo central above hit Submit by accident oops... please reed this instead

To answer Kevin3NF's comment I had to go to a meeting while I was writing the comment when I started no one had added any comments, when I came back only after I submitted I realised that I had been pipped to the post...

As for the neccesity of archiving its crucial in just about any enterprise system ..sadly software development isn't infalable (my spelling :( sorry ) developers write code that the customers acccept that can end up running wreck.
And if you go auditing processes you'll have to track deletes and updates, the phrase we do hard deletes can cause heart attacks in some circles.

Soft deletes and updates within the table's is another approach but do not go down this road it takes performance and starts beating it black and blue.
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
You lmay set up WMI alerts for that.

Hope this helps...
0
Kevin HillSr. SQL Server DBACommented:
climbingjaffa....I was just messing around of course :)
0
climbingjaffaCommented:
No worries dude ..... :)
0
chrisryhalAuthor Commented:
All comments were very good, and I greatly appreciate the help with this
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.