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

Question

Post reply

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

nealhudson77

SSC-Addicted

Points: 491
More actions
November 11, 2010 at 5:36 am

#231232

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

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

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

ps. One Orange Chip Points: 29423 More actions · Answer 1

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

Pradeep Singh

nealhudson77 SSC-Addicted Points: 491 More actions · Answer 2

nealhudson77

SSC-Addicted

Points: 491

November 11, 2010 at 5:43 am

#1249414

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

Vishal.Gajjar SSCrazy Points: 2285 More actions · Answer 3

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

ps. One Orange Chip Points: 29423 More actions · Answer 4

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

nealhudson77 SSC-Addicted Points: 491 More actions · Answer 5

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?

Vishal.Gajjar SSCrazy Points: 2285 More actions · Answer 6

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

Vishal Gajjar
http://SqlAndMe.com

nealhudson77 SSC-Addicted Points: 491 More actions · Answer 7

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.

ps. One Orange Chip Points: 29423 More actions · Answer 8

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

nealhudson77 SSC-Addicted Points: 491 More actions · Answer 9

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

🙁

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

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

ps. One Orange Chip Points: 29423 More actions · Answer 11

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

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

ps. One Orange Chip Points: 29423 More actions · Answer 13

am just hoping the trial edition gives him that feature to read from log backups.. i haven't used it yet.

Pradeep Singh

nealhudson77 SSC-Addicted Points: 491 More actions · Answer 14

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....