Dear Team,
Is there any way to get the Record of any change made in Database .
For Eg : If i made an update command on any table of a databse that update command should to copy in text file so that i cn know that on that day i have made any change in the table..
Thanks...
Dear John
is we have to create it on the table suppose id i have 100 table then i have have made the change in any of the table ( Insert / Update / Delete )
then can i get the information for that Transaction ...
First, as Jonlee pointed out, if you are using SQL Server 2008 then you can use Change Data Capture, and that is probably your way to go.
If you are not using SQL Server 2008 or for whatever reason you do not like the Change Data Capture then you have a few options.
The first is to custom write your own triggers to log precisely what you want. This lets you log exactly what you want exactly how you want. Of course, it can take some time to set up and someone with the right permissions can bypass it by temprorarily turning off the logging or manipulating the the logger table, which makes this less than perfect for full scale auditing and accountability. This is a technique I have used with great success in SQL Server 2005 though.
The next is 3rd party software like Idera's compliance manager ( http://www.idera.com/Products/SQL-Server/SQL-compliance-manager/ ). I have done evaluations on them and found that for most purposes I prefer custom written triggers, but they are certainly useful for some use cases so you may want to evaluate for yourself.
Finally, you could just put it in full recovery mode and retain all logs. Then you can use third party log reader software to see all changes. For instance Apex SQL Log http://www.apexsql.com/sql_tools_log.aspx . This software is extremely useful in certain recovery scenarios, but it is somewhat slow and awkward to use in an auditing or history tracking scenario. The nice thing is that since it works with the transaction log it is very difficult (not impossible) for someone to avoid leaving a trail if you are trying to do a genuinely thorough audit and are worried about someone deliberately trying to hide changes.
The bottom line is that if you are using SQL Server 2008 then Change Data Capture is probably the way to go, and if you are in SQL Server 2005 than custom triggers are the overall best option. But you have several choice and they all have advantages and disadvantages.
