How can we retrieve the deleted data from

Question

Post reply

How can we retrieve the deleted data from

Kailash Mishra

SSC Veteran

Points: 286
More actions
June 10, 2009 at 12:08 am

#134979

Hi everyone,
How can we retrieve the deleted data from .ldf file in MS SQL Server 2000 or 2005? Help would be great..
Thanks in advance.

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

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

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 1

Are you a DBA? What exactly is your requirement?

You need a full backup to restore the lost data ..

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

There are some third party tools (for example log explorer) that can get the data from the database's log. I'm not aware of a way to get understandable data from the log with t-sql (you can get some info using fn_dblog and maybe some other functions, but it is not documented nor understandable). One option that might help you is restoring your database to the state that it was before the data was deleted. This can be done if your database's recovery model is set to full and you have done a full backup and have all the log backup files that were taken since the full backup (but since you didn't write much about your situation, I don't know if it is relevant for you).

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Kailash Mishra SSC Veteran Points: 286 More actions · Answer 3

Actualy, I have updated some data in my database and want to retrieve that data.

But I don't know how to retrieve that data.

I have also read some topics online about some third party tool with which we can retrieve the data,

but I think there must be a way given by microsoft to get back that lost data.

Please tell me if there is any tutorial or function which give some more knowledge about this.

Chand00 SSCertifiable Points: 6466 More actions · Answer 4

Kailash,

What is the recovery model of your database. If you are using full recovery model then there are chances. This concept in sql server is called as point in time recovery. For this you need the backup's. I am giving you a scenario for your understanding. Do you know exactly at what date and time you had modified the table. (If you are not sure then you have to go with the trial and error method). Search in google or microsoft with point in time restore and you will get good examples of that.

Thanks

Chandra Mohan N

[font="Verdana"]Thanks
Chandra Mohan[/font]

maechismo_8514 SSCoach Points: 17510 More actions · Answer 5

Kailash Mishra (6/10/2009)
Hi everyone,
How can we retrieve the deleted data from .ldf file in MS SQL Server 2000 or 2005? Help would be great..
Thanks in advance.

.ldf has never any data that you have entered. it records what has been done against the .mdf.

AS I understand your requrement is you have updated the data but you have deleted it, right? and you want that data to get back ? If that is so, unfortunately it's not possible and not even third party tools could do it. Am sure you are not doing this against your production server??.

joeroshan SSChampion Points: 10377 More actions · Answer 6

Kailash,

If you are looking for a kind of undo ctrl+Z for committed data, its not there in SQL Server

You can get the data back provided you are taking regular backups, and you are having full recovery model.

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 7

There used to be several tools available for reading log files. I know that Idera had one as did Red Gate. I'm pretty sure Red Gate's is gone. I'm not sure about Idera's.

Otherwise, what you need to do, assuming your database is in full or bulk-logged recovery models, is a point in time recovery. Usually, when retrieving data that was lost on a single table or over a short period, what you'll need to do is restore to a copy of your database, either on the same server or a different one, and then do the point in time recovery there. After that you can move the missing/modified data from the copy to the main database through TSQL queries.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

homebrew01 SSC Guru Points: 55548 More actions · Answer 8

Lumigent Log Explorer is another product. Not sure if it would help in this case.