How to get DELETED data from log ?

Question

Post reply

How to get DELETED data from log ?

Amit Choudhary-216780

Old Hand

Points: 384
More actions
July 23, 2007 at 8:41 am

#125120

Hi,
How can i recover deleted data from a permanent table by delete clause ?
Suppose i did't use begin tran ..
Thanks in advance
Amit Choudhary

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

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

You need to use a tool that can read the transaction log then.

There are a few different out there.

The other option is to restore your database...

/Kenneth

Steve Jones - SSC Editor SSC Guru Points: 735912 More actions · Answer 2

Kenneth has the only items I know of (unless you had a trace running).

ApexSQL has ApexLog

Lumigent has Log Explorer

Red Gate has Log Rescue

Probably more out there.

Kiran Kumar SSC-Addicted Points: 463 More actions · Answer 3

I guess you can recover your data back in two ways:

1. By taking backup of your db, before deleting the data, and recover it later. Or

2. Create an object in "tempdb" by running the SELECT * INTO #objectname FROM <TableName> query, and after deletion you can recover it back by running the same above query on to your db. One problem of using "tempdb" is, the db iteself is temporary, i.e., if we close the connection whatever the objects we have created in tempdb will lose. So make sure the data should be recovered before the connection is closed.

Please Clarify if I'm wrong.

Regards,

-Kiran

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 4

??? He's already deleted the data by mistake... how is either method you suggest going to recover data that's already been deleted?

I agree with the others... only way is going to be able to get to the transaction log using one of the methods (software packages) listed.

Of course, you could always use the undocumented DBCC LOG (dbname,3) command and write your own hack

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 5

Before speaking all these first what is the recovery model your database is running if its simple no ways to recover the data only to restore to your last backup else if its full and you have a backup of the database take a log backup and do a point in time recovery.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

Good catch.

Didn't think of recovery model playing a crucial role..

(need more coffee...)

/Kenneth

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 7

Heh... and I just "assumed"... we all know what happens when you do that, eh

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)