Loss of Data - Help Needed

Question

Loss of Data - Help Needed

Prabhakar

SSC Veteran

Points: 234
More actions
August 4, 2003 at 11:51 pm

#159536

Hi All,
Today seems to be a bad day....i found that two tables in two databases that i have, have been truncated...... is there any way to know which user has done this and when he/she has done it
Also is thr a way to retreive it from the log file....
help needed at the earliest....thanx in advance
Prabhakar

Prabhakar

Viewing 8 posts - 1 through 7 (of 7 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi Prabhakar,

quote:
Today seems to be a bad day....i found that two tables in two databases that i have, have been truncated...... is there any way to know which user has done this and when he/she has done it

are you sure about the TRUNCATE statement?

Well, if so, I agree, it will be really a bad day. TRUNCATE TABLE operations are not logged!

If you meant DELETE, you might want to take a look at BOL for RESTORE LOG or transaction logs -> restoring

Good luck!

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 2

Have a look at Lumigent's

Log Explorer at http://www.lumigent.com/

Prabhakar SSC Veteran Points: 234 More actions · Answer 3

How do i read the log file, i should at least be able to find out as to who did it and when ?

quote:
Hi All,
Today seems to be a bad day....i found that two tables in two databases that i have, have been truncated...... is there any way to know which user has done this and when he/she has done it
Also is thr a way to retreive it from the log file....
help needed at the earliest....thanx in advance
Prabhakar

Prabhakar

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hi Prabhakar,

quote:
How do i read the log file, i should at least be able to find out as to who did it and when ?

that is really a good question!

I guess you haven't got the time to figure that out right now.

So I would prefer 5409045121009 suggestion.

Here are some more links on this topic:

http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=12807

http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=12279

Cheers,

Frank

Edited by - a5xo3z1 on 08/05/2003 12:48:35 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

SQLBill SSC Guru Points: 51440 More actions · Answer 5

By the way, have you restored your lost data?

If not:

Do you have a full backup? Have you been backing up the logs?

You can do a 'point-in-time' restore if you have the log backups.

Figure out when the data was lost. Let's say it was 11:15 PM.

RESTORE DATABASE mydatabase

FROM mybackupdevice

WITH NORECOVERY

RESTORE LOG mydatabase

FROM mybackupdevice

WITH RECOVERY, STOPAT = 'August 4, 2003 11:14 PM'

If you do differential backups, just add them to the restore commands also.

Refer to the BOL, use the Index tab, enter Restore Database, and select the entry for Transact-SQL.

-SQLBill

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 6

Alternatively restore full backup as a "new database". Copy last table to live database as a ANotherTable. Restore next differential or log. If data still in original table copy to Another and continue until you roll forward to a stage where it is gone.

A question will there be any entry of some sort in log to show that a truncate was executed. If so one should be able with Lumigent Log Explorer to determine when it was done and starting with a full backup roll forward till just before the truncate.

maijoe SSC Enthusiast Points: 140 More actions · Answer 7

You can use this system UDF to read from your log file:

SELECT * FROM ::fn_dblog(1,NULL)

Hope this will help