Data deleted - how to tell when? (using .bak and .trn)

  • Hi there,

    We had an issue overnight when some of our data was deleted. Specific rows from specific tables.

    We have backup files (.bak) from before and after, and Transaction Log Files (.trn)

    How can I use these to tell when the data was deleted, and by who?

    Thanks

    Neal

  • do u have an audit trace running/3rd party auditing tool installed and running on server?



    Pradeep Singh

  • I'm not sure but I don't think so

  • You might be able to find the approximate deletion time by applying T-Logs one by one,

    but you won't be able to find user name, unless you have Audit Trace.

    Vishal Gajjar
    http://SqlAndMe.com

  • nealhudson77 (11/11/2010)


    I'm not sure but I don't think so

    what does the following code return?

    select value from sys.configurations where name='c2 audit mode'



    Pradeep Singh

  • Vishal.Gajjar (11/11/2010)


    You might be able to find the approximate deletion time by applying T-Logs one by one,

    but you won't be able to find user name, unless you have Audit Trace.

    Really?

    I would have thought a 'Transaction Log File' would give you this, seeing as by definition it should hold every transaction in the database?

  • No T-Log will not give you User Name,,

    Vishal Gajjar
    http://SqlAndMe.com

  • Vishal.Gajjar (11/11/2010)


    No T-Log will not give you User Name,,

    Thanks.

    Is there any way to parse this file to look at transaction?

    I know the table name the records were deleted from, and the individual rows.

    I'm not sure if there was a malicious attack on the server or not yet.

  • There are third party log reader tools available.

    you may want to see this.

    http://www.apexsql.com/sql_tools_log.aspx%5B/url%5D



    Pradeep Singh

  • ps. (11/11/2010)


    nealhudson77 (11/11/2010)


    I'm not sure but I don't think so

    what does the following code return?

    select value from sys.configurations where name='c2 audit mode'

    This returns 0

    🙁

  • nealhudson77 (11/11/2010)


    I would have thought a 'Transaction Log File' would give you this, seeing as by definition it should hold every transaction in the database?

    The transaction log is there for database integrity and recoverability. Neither of those require the login name be logged. It's not an audit log.

    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
  • nealhudson77 (11/11/2010)


    ps. (11/11/2010)


    nealhudson77 (11/11/2010)


    I'm not sure but I don't think so

    what does the following code return?

    select value from sys.configurations where name='c2 audit mode'

    This returns 0

    🙁

    Audit trace is disabled on your system. Check out for 3rd party tools. Apex log reader allows you to read from transaction log backups.



    Pradeep Singh

  • ps. (11/11/2010)


    Check out for 3rd party tools. Apex log reader allows you to read from transaction log backups.

    But costs around $1000 per licence.

    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
  • am just hoping the trial edition gives him that feature to read from log backups.. i haven't used it yet.



    Pradeep Singh

  • Thanks guys, seem to be up sh*t creek with this one...

    This is the second time the data has been mysteriously deleted from the server. I wrongly assumed that turning on transactino logging would be enough. Mind you - I'm not responsible for the server... don't even have access....

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

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