2

I have a full chain of full backups and log backups. I am looking to investigate exactly what transaction was done by a certain user in a given period of time.

The database (thus all backups) are encrypted by TDE. Tools such as ApexSQL Log does not work on TDE-encrypted data source. Is there any way I can analyze the log file to find out the information I want?

I can run fn_dump_dblog which gives me some information, however I cannot retrieve the exact information on what was changed, such as what column was changed to what value.

  • I guess if you know the rough time window the change occurred, it would be better for you to restore database to standby mode with piece by piece of log backups and see what change have occurred. Reading/interpreting log is never easy (even if you can read it). – jyao Jul 6 '18 at 18:13
  • Hi @jyao - I don't know when was the data changed. I need to know what a user changed in say, a week of time. If the database is not encrypted I could have used ApexSQL Log to do this relatively easily. – elty123 Jul 6 '18 at 18:17
0

Do you have database audit in place? temporary tables? If you have one of these solutions in place, it can be done relatively easy.

If no, you can use undocumented functions fn_dump_dblog() to analyze log backup contents.

hope this helps!

0

You can use fn_dump_dblog() to gather user activity. Use SUSER_NAME()/SUSER_ID() along with transaction sid from the log dump.

The APEX SQL tool cannot show you anything more than you get can from the logs yourself (it's just much easier :)). There are ways to determine what was changed during a transaction. Here is one example

I'm not sure exactly what you are trying to find, but using some targeted Google queries should get you what you need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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