Post reply

Reading a SPECIFIC transaction out of Transaction Logs

  • June 23, 2011 at 11:36 am

    #243259

    I have been asked by me CEO to find out when which user entered/changed one SPECIFIC record in my database.

    (I know who it was and when; I need to PROVE it)

    My plan had been to just go back in time and then roll everything forwards and see who did what when.

    I have TRANSACTION logs for the time in question, but -unfortunately- do not have a full backup to "start" from.

    So I have two options / questions:

    a) can I extract information out of transaction logs if I know EXACTLY which record change I am looking for?

    OR

    b) can I "roll transactions backwards" if I have a current database and all transaction logs leading back to the incident?

    I need to go back about 3 weeks (!) on a 100Gb database that changes by 50Mb or so a day... Plus numerous maintenance plans that ran across the database (integrity checks, optimizations etc). So (b) does not sound like fun at all.

    Thanks for any help,

    Karl

  • June 23, 2011 at 11:51 am

    #1343284

    First of all the tran log backups are useless without the full back for the db.

    Next you can use from transaction log reader software , SQLlog rescue is one i have used before and its quite user friendly.

    If you really have issues with data , u need to start auditing

    Jayanth Kurup[/url]

  • June 23, 2011 at 12:08 pm

    #1343301

    I appreciate the comments.

    PURE luck that I had ancient tran logs. (I can usually go back a week, but not more than that...) Request caught me by surprise. Thx for the info

    K

  • June 23, 2011 at 12:08 pm

    #1343300

    Check out the following and determine if these might assist you.

    SQL Server 2005 Log File Viewer at:

    http://www.mssqltips.com/tip.asp?tip=1021

    or:

    http://decipherinfosys.wordpress.com/2008/04/02/log-file-viewer-in-sql-server-2005/

    Now both use SSMS and some manual labor, but just might suffice for this one time use.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply