Results 1 to 6 of 6

Thread: How to recover the deleted records from the MS SQLServer2008 log file?

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    How to recover the deleted records from the MS SQLServer2008 log file?

    The data in my SQLServer2008 was accidentally deleted, and I wanted to recover the deleted records from the log file. I found a link:

    https://raresql.com/2011/10/22/how-t...rom-sql-sever/

    However, the store-procedure in this link can't be executed. Is there any good method or tool to view and recover the deleted records? Thanks in advance.

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: How to recover the deleted records from the MS SQLServer2008 log file?

    I'm not detracting from this site at all but I see you have not had a response. Try the MS SQL Server section of this site:

    http://www.tek-tips.com

    There are some top notch DBA types "lurking" there.
    Please remember next time...elections matter!

  3. #3
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How to recover the deleted records from the MS SQLServer2008 log file?

    From that link you posted there is a query down in the explanation that appears to give you the raw rows that were deleted.

    Code:
    Select [RowLog Contents 0] FROM sys.fn_dblog(NULL,NULL)
    WHERE AllocUnitName =‘dbo.Student’
     AND Context IN (‘LCX_MARK_AS_GHOST’, ‘LCX_HEAP’)
     AND Operation in (‘LOP_DELETE_ROWS’)

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4

    Thread Starter
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,083

    Re: How to recover the deleted records from the MS SQLServer2008 log file?

    @TysonLPrice,
    Thank you for your link. I'll try it.

    @szlamany,
    Thank your for your reply. I've tested the following code, but there is no result. My database didn't make a full backup, I guess the missing data can't be restored.

    Code:
    Select [RowLog Contents 0] FROM sys.fn_dblog(NULL,NULL)
    WHERE AllocUnitName ='dbo.MyDatabase'
     AND Context IN ('LCX_MARK_AS_GHOST', 'LCX_HEAP)
     AND Operation in ('LOP_DELETE_ROWS')
    Even if I reduce the filter condition of the query, there is still no result.

    Code:
    Select * FROM sys.fn_dblog(NULL,NULL)
    WHERE AllocUnitName ='dbo.MyDatabase'
    Last edited by dreammanor; Nov 23rd, 2017 at 10:24 AM.

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How to recover the deleted records from the MS SQLServer2008 log file?

    In the end, the log is not intended to be a place for you to go to find deleted data. It's a place to hold "still to be committed" transactions. Once a transaction is committed how the log chooses to reuse or reclaim log space is out of your control.

    If we could go back in time and you had just DELETE'd the data moments ago maybe setting the DB to single user mode or something like that could open a small window where that deleted data could be seen. I kind of doubt it though - you would have to know a lot about how the SQL engine (and the specific version you are running) internally moves through processes (all kinds of lazy time algorithms run to do a variety of jobs).

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: How to recover the deleted records from the MS SQLServer2008 log file?

    In the end, the log is not intended to be a place for you to go to find deleted data. It's a place to hold "still to be committed" transactions. Once a transaction is committed how the log chooses to reuse or reclaim log space is out of your control.
    bit more specific: the above is true if your database is set to "simple Recovery Model".
    If you have it set to "Full Recovery Model" then the log keeps the data even if it is already committed and the data stays there until the next full backup.

    if you do not have a recent full backup and the db is in "simple" mode, the data is lost.
    if the db is in "full" mode, then you can take the last recent backup (might even be days ago), restore this backup and restore the Transactions that happened after the backup from the log file. in this case you can Chose the Point in time you want to recover. in your case this would be the time right before the DELETE happened.

    But assuming this is all new to you and you do not have a recent backup i guess the data is gone

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width