I currently have a database with an attached CMS. The CMS has about 50+ people with various permission levels that can log in etc... I would like to know if there is a way I can look at the transaction logs (taken every 30 minutes) and see where a particular value was updated on a bit switch. I can then cross reference it with the person who was logged in at the time so I am fine on that part. If someone can guide me on how to read the transaction log to find the specific update to table x that would be a huge help.
-
What type of database are you using? Oracle? SQL Server? MySQL? PostgreSQL? Something else? The answer is going to be very specific to the database in question.– Justin CaveCommented Dec 11, 2012 at 18:50
-
you'll have to specify which SQL product you're using.– Erwin SmoutCommented Dec 11, 2012 at 18:50
-
I am using SQL Server 2K12. Sorry i neglected to mention.– JTJCommented Dec 11, 2012 at 22:13
Add a comment
|
2 Answers
If you're using MS SQL Server you're much better off downloading a tool like this instead of trying to figure out how to go through the entire transaction log and use those functions. It has a 14 day free trial and should make it a lot more readable for you.
If you end up having to do that check out this thread on DBCC LOG:
answered Dec 11, 2012 at 19:21
-
I have looked into Apex the thing i am not to fond if is having to install drivers and other stuff for the tool on said production system to read the log. Is there any tool that does not require this?– JTJCommented Dec 11, 2012 at 22:14
-
I'm not familiar if there are any, have you considered taking a full backup and restoring it to a different machine, then installing Apex? A full backup includes the transaction log. Commented Dec 11, 2012 at 22:18
-
you know what that is a good idea. I am sorry for being a clown and not thinking of that before! respect.– JTJCommented Dec 11, 2012 at 23:13
-
-
Actually, a full database backup doesn't include the complete transaction log (just a little part of it). Here's the recommendation for using the tool on a different machine: knowledgebase.apexsql.com/2009/04/… Commented Feb 25, 2013 at 8:41
There are different ways to open an LDF file, and most of them do just that – opens it. It’s tricky to get any human readable information and make a use of it though
ApexSQL Log is a SQL Server transaction log reader which reads online transaction logs, detached transaction logs and transaction log backups – both native and natively compressed. As an LDF viewer, it’s focused on operations (both DML and DDL, 45 in total), and what was changed by execution of these operations: Open LDF file and view LDF file content
And you can selectively view or script one or all operations in a transaction - create Undo or Redo script
Disclaimer: I work as a Product Support Engineer at ApexSQL
