-
Nov 17th, 2017, 09:16 AM
#1
Thread Starter
PowerPoster
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.
-
Nov 20th, 2017, 06:31 AM
#2
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!
-
Nov 20th, 2017, 06:39 AM
#3
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’)
-
Nov 23rd, 2017, 10:14 AM
#4
Thread Starter
PowerPoster
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.
-
Nov 23rd, 2017, 11:32 AM
#5
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).
-
Nov 24th, 2017, 03:24 AM
#6
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|