Read transaction log file

  • Hi,

    We have an issue where someone had fired a delete query on a table which did't have any audit triggers on it. The loss of data went unnoticed until now.

    We do have backups of the time when that query was fired. It was data relating to a school's session a few years back and now we have some requirement using that data. So, we have to retrieve it...somehow. The table gets truncated every session so we don't even know what data was there originally and what data got deleted.

    So, the option left was to read the transaction log from a backup of that session. I trial version of Apex SQL Log for this. But, it doesn't show any transactions from the log. It just displays a screen where it says, "Loading Transaction file"....processes for a while and then just doesn't display anything. When I manually query the transaction log, I can see that it has data. But, Apex SQL Log is not showing anything. Any Ideas...guys????

    Or is there any other free tools which could help me get queries that were run on the server from the Transaction Log????.....

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • What recovery model?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Full Recovery Model

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • Can you not just take a log backup (or take the regular log backups), restore the lastest full to a new DB, restore all the logs and STOPAT just before the delete happened?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail, thanks for all the help.

    I played around with Apex SQL Log....the software's fine...in fact its very good.

    The actual problem is something else. Sql Apex Log is only showing that data which I inserted into a table after I restored the backup on my server. That means its not reading the historical log and is only reading the log after I restored it on my server.

    This is confusing me a little. The actual database on the production server was in Simple Recovery Model. So, I am assuming that when you restore a backup of a database that is in Simple Recovery Model the Transaction Log does not show the historical data. It starts from the time after the restore takes place.

    Is that true??....

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • Hang on, if the database that the delete was run on was in simple recovery model, why did you tell me it was in full recovery?

    When restoring a database, all that you get in the log is operations after the restore. There's no reason why any log records from before would be needed and full backups only include the portion of the log over the time of the full backup anyway. That's for any recovery model.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sorry, I didn't check if it was in Full Recovery Model. I was so sure. But some how it is in Simple Recovery model and according to Apex SQL Log, the transaction Log that exists with the actual database is from 18th April 2013.

    So, I assume that there is no way of getting the data back....right??

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • Simple recovery = log truncated on checkpoint, meaning it'll get overwritten when the log wraps around.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hello,

    Do you have full backup taken every day , if it is try that before delete and recovery to the max you can.

    Regards
    Durai Nagarajan

  • GilaMonster (4/24/2013)


    Simple recovery = log truncated on checkpoint, meaning it'll get overwritten when the log wraps around.

    Thanks, so in simpler words it means I cant get the data back...right???......All I can do now is to set the recovery model to FULL so that something like this doesn't happen in future...right??....

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • vinu512 (4/24/2013)


    GilaMonster (4/24/2013)


    Simple recovery = log truncated on checkpoint, meaning it'll get overwritten when the log wraps around.

    Thanks, so in simpler words it means I cant get the data back...right???......All I can do now is to set the recovery model to FULL so that something like this doesn't happen in future...right??....

    yes, in addition to it sechdule transaction log backup (as per your need) so that log size will be in control and recovery to the nearest is possible.

    Regards
    Durai Nagarajan

  • guys am a new dba , just leanring, l will like to pos , what section can l do my post

  • durai nagarajan (4/24/2013)


    vinu512 (4/24/2013)


    GilaMonster (4/24/2013)


    Simple recovery = log truncated on checkpoint, meaning it'll get overwritten when the log wraps around.

    Thanks, so in simpler words it means I cant get the data back...right???......All I can do now is to set the recovery model to FULL so that something like this doesn't happen in future...right??....

    yes, in addition to it sechdule transaction log backup (as per your need) so that log size will be in control and recovery to the nearest is possible.

    Thanks for the tips Durai.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • fadewumi (4/24/2013)


    guys am a new dba , just leanring, l will like to pos , what section can l do my post

    Hi, Welcome To SSC.

    The Forum menu is very self descriptive.

    For SQL Server 2005 issues you can post in the SQL Server 2005 section....there are different links for administration and TSQL, depending upon ur requirement you can post in any of them.

    Similar, is the case for SQL Server 2008.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • GilaMonster (4/24/2013)


    Hang on, if the database that the delete was run on was in simple recovery model, why did you tell me it was in full recovery?

    When restoring a database, all that you get in the log is operations after the restore. There's no reason why any log records from before would be needed and full backups only include the portion of the log over the time of the full backup anyway. That's for any recovery model.

    Hi Gail,

    One small doubt that is still bugging me is that when i see the size of the log, of the newly restored db, it still shows 17 GB.....if it does not have any transactions before the time it was restored then what does the 17 GB log have??

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Viewing 15 posts - 1 through 15 (of 17 total)

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