Post reply

How can we retrieve the deleted data from

  • 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.

  • June 10, 2009 at 12:21 am

    #1007198

    Are you a DBA? What exactly is your requirement?

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

  • June 10, 2009 at 12:39 am

    #1007202

    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/

  • June 10, 2009 at 4:00 am

    #1007248

    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.

  • June 10, 2009 at 4:24 am

    #1007255

    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]

  • June 10, 2009 at 5:17 am

    #1007272

    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??.

  • June 10, 2009 at 5:31 am

    #1007278

    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]

  • June 10, 2009 at 6:44 am

    #1007317

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • June 10, 2009 at 10:17 am

    #1007529

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

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

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