How to recover deleted records in transaction log

strawberry

unread,

Apr 20, 2009, 11:13:01 AM4/20/09

to

Hi,

I have a question regarding how to to read the database transaction log? The
purpose is to recover any accidentally deleted records in tables.

The technical environments are as follow:
• Database version : MSDE 2000 SP4
• Operating System : Windows XP Pro SP2
• Database Transaction log file : *.ldf (files with extension .ldf)

--
strawberry

Richard Mueller [MVP]

unread,

Apr 20, 2009, 12:35:09 PM4/20/09

to

strawberry wrote:

I recover the last full backup, plus all transaction log backups up to, but
not including, the one in which the records were deleted. Check SQL Server
Books Online for documentation on the RESTORE command and the RECOVERY and
NORECOVERY parameters.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

Russell Fields

unread,

Apr 20, 2009, 3:25:19 PM4/20/09

to

In addition, you do not mention whether more updates have been made to the
database that you cannot afford to lose.

If that is the case, you may want to restore your backup, as described by
Richard, but to a scratch database instead of the original. The
programmatically copy the missing data back into the active database. If
you do that, it is up to you to maintain the referential integrity of the
data that you are copying.

RLF

"strawberry" <straw...@nospam.nospam> wrote in message
news:195877EA-2BFF-42CD...@microsoft.com...

Charles Wang [MSFT]

unread,

Apr 21, 2009, 12:17:15 AM4/21/09

to

Dear Strawberry,
Following Richard and Russell's suggestions, I would like to add more
comments on this thread.

Backup/Restore is the normal method used to recover data in case the
database is dirtied. You may first restore the backup file to a new
database which has the right state for your data and then transfer the
table from the new database to your working database.

For reading the transaction log, you can run the undocumented DBCC command
in SQL Server:
DBCC LOG(database_name, output_option)
Go

The output_option value can be:
0: Return only the minimum of information for each operation -- the
operation, its context and the transaction ID. (Default)
1: As 0, but also retrieve any flags and the log record length.
2: As 1, but also retrieve the object name, index name, page ID and slot ID.
3: Full informational dump of each operation.
4: As 3 but includes a hex dump of the current transaction log row.

However the input T-SQL text information is encrypted and you could just
see the binary information for any T-SQL statement .

You may also consider using some third party tools like ApexSQL or Log
Explorer to read the transaction log or even selectively recover data. Here
are some links for your reference:
http://www.sharewareplaza.com/Apex-SQL-Log-download_14955.html
http://www.axantech.com/brochures/lumigent/LogExp3.0.pdf
http://www.sqlservercentral.com/articles/Administration/reviewoflumigentloge
xplorerv4/1282/

Hope this helps. Please do not hesitate to let us know if you have any
other questions or concerns.

Best regards,
Charles Wang

Microsoft Online Community Support
Get Secure! - www.microsoft.com/security
====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
====================================================

Andrea Montanari

unread,

Apr 21, 2009, 10:20:19 AM4/21/09

to

hi all,
"Charles Wang [MSFT]" wrote:
> Dear Strawberry,

keaping "following" Richard, Russel and Charles suggestions,

> You may also consider using some third party tools like ApexSQL or Log
> Explorer to read the transaction log or even selectively recover
> data. Here are some links for your reference:
> http://www.sharewareplaza.com/Apex-SQL-Log-download_14955.html
> http://www.axantech.com/brochures/lumigent/LogExp3.0.pdf
> http://www.sqlservercentral.com/articles/Administration/reviewoflumigentloge
> xplorerv4/1282/
>

Red Gate's SQL Log Rescue,
http://www.red-gate.com/products/SQL_Log_Rescue/index.htm, is free

regards
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz
DbaMgr2k ver 0.21.1 - DbaMgr ver 0.65.1 and further SQL Tools
http://www.hotelsole.com - http://www.hotelsolericcione.de
--------- remove DMO to reply

strawberry

unread,

Apr 22, 2009, 1:02:01 AM4/22/09

to

Hi all,

The all the tips given have been very usefull. Thanks a lot.
--
strawberry

Maria Sartzetaki

unread,

Mar 29, 2010, 10:21:01 AM3/29/10

to

Please note that SQL Log Rescue does not support 64-bit versions of SQL
Server or SQL Server versions other than SQL Server 2000.

Andrea Montanari

unread,

Mar 29, 2010, 12:49:45 PM3/29/10

to

hi Maria,

Maria Sartzetaki wrote:
> Please note that SQL Log Rescue does not support 64-bit versions of
> SQL Server or SQL Server versions other than SQL Server 2000.

yep, but this is MSDE group, so we can assume we are talking about a 32 bit
version of a SQL Server 2000 code based edition :)