Recover deleted records

Question

Recover deleted records

arpankashyap-869238

SSC Veteran

Points: 266
More actions
September 3, 2008 at 12:51 pm

#192459

I am using SQL Serer 2000 and want to know how to recover deleted records from a table / transaction log.
Unfortunately one of my team member has accidentally deleted some records of an important table.
Kindly suggest appropriate solution for the same.

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Go to redgate.com. If memory serves, they have a free tool that will read a SQL Server 2000 transaction log file (if your database is using the full or bulk-logged recovery model).

😎

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Restore most recent backup under a different DB name. Bilk import the records from that restored table.

Otherwise you need a log explorer like lynn suggested. You might need both actually, but that's a great start.

vicky_j77 SSC Journeyman Points: 99 More actions · Answer 3

Hi friends I am using MSSQL7 , One of my team member has deleted some records. This records are to be recovered now, I dont have any backup. PLEASE HELP ME AS how can I get these delected records back. I might Lose my job.. PLEASE HELP ME.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Check out this tool, it might be your only hope!

http://www.red-gate.com/products/SQL_Log_Rescue/index.htm

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

The first thing that I will recommend is that you go and explain to your boss what has happened. Hiding mistakes makes them worse, and there's a good chance that you will not be able to get the data back.

What recovery model is the database in? I assume, with no backups of any form, it's in simple recovery.

How long ago were the rows deleted?

You can try RedGate's log explorer (as Ninja suggested) or one of the other log recovery tools. I don't know which ones can read SQL 7. If the DB is in simple recovery, then it's quite likely that the log records for the delete have been discarded already. You can give it a try, but no promises.

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

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 6

Good resume

Good backup

you only need one.

Get Log Rescue, I think it works on v7 as well. Read the logs. Then go get backups setup.

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

Your toast... sorry...

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

Vegard Hagen Hall of Fame Points: 3078 More actions · Answer 8

vicky_j77 (10/12/2008)
I dont have any backup.

Oh dear, oh dear and INDEED oh DEAR!!!

I have nothing to offer except what's already been posted. Sorry.

Assuming the data is lost, your best bet is to be completely open and honest about what happened and why - and to immediately do a full review of your databases and make sure there aren't any more important data lying around with no backup. Mistakes can be forgiven as long as we learn from them. But if something like this happens again next week, it's going to be a lot harder to explain, isn't it?

Kind regards,

Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

It is better to light a candle than to curse the darkness. (Chinese proverb)

umalachu Old Hand Points: 341 More actions · Answer 9

This thread really helped me today!! I recovered approximately 5000 records using Red Gate SQL Rescue. It really helps and a great software. Hats off!

Good one! Anybody can try using this to recover records deleted from tables in SQL Server 2000.

Just curious to know if there is anything for SQL Server 2005?

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

Good one! Anybody can try using this to recover records deleted from tables in SQL Server 2000.

Providing the log records are there. Backups are still (and probable always will be) the recommended way to get back data that's been deleted. Snapshots can do the same on SQL 2005

Try Apex's log reader for SQL 2005. It's not free.

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

umalachu Old Hand Points: 341 More actions · Answer 11

umalachu

Old Hand

Points: 341

November 10, 2008 at 6:47 pm

#896672

Thank you!!

mimran18 SSC-Addicted Points: 451 More actions · Answer 12

In sql server 2005 , you can recover the deleted records as mentioned in this article.

http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/

MI
http://raresql.com

mimran18 SSC-Addicted Points: 451 More actions · Answer 13

This articale can help you to recover the deleted data from sql server 2005 and above.