Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Recover deleted records Expand / Collapse
Author
Message
Posted Wednesday, September 3, 2008 12:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 16, 2011 4:48 AM
Points: 1, Visits: 11
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.
Post #563318
Posted Wednesday, September 3, 2008 12:55 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:19 PM
Points: 23,286, Visits: 32,012
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).




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #563324
Posted Wednesday, September 3, 2008 1:03 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:00 AM
Points: 21,397, Visits: 9,612
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.
Post #563331
Posted Sunday, October 12, 2008 12:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 16, 2008 1:10 PM
Points: 1, Visits: 1
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.
Post #584595
Posted Sunday, October 12, 2008 1:07 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:00 AM
Points: 21,397, Visits: 9,612
Check out this tool, it might be your only hope!

http://www.red-gate.com/products/SQL_Log_Rescue/index.htm
Post #584599
Posted Sunday, October 12, 2008 1:20 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:33 PM
Points: 42,818, Visits: 35,943
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 2008, MVP
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

Post #584601
Posted Sunday, October 12, 2008 7:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:13 PM
Points: 33,198, Visits: 15,341
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #584624
Posted Sunday, October 12, 2008 8:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:03 PM
Points: 36,983, Visits: 31,508
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #584635
Posted Wednesday, October 15, 2008 3:15 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 11, 2014 6:50 AM
Points: 517, Visits: 346
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?




Vegard Hagen
Norwegian DBA, blogger and generally a nice guy who believes the world is big enough for all of us.

@vegard_hagen on Twitter
Blog: Vegard's corner (No actual SQL stuff here - haven't found my niche yet. Maybe some day...)

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

Post #586043
Posted Saturday, November 8, 2008 3:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 3:14 PM
Points: 9, Visits: 166
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?
Post #599489
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse