Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A problem with one of our database tables arose at the end of last week and I wanted to restore that database to a server in order to retrieve the table. We only have the last backup, and a full set of transaction logs before the error occurred, up to and after the full back was taken.

However, when I restore, I can only use the transaction logs to roll forward, not back. Is what I'm trying to do possible?

Sequence of events:

  • Error occurred: Friday April 5 at 12:00
  • Full backup taken: Sunday April 7 at 02:00

Many thanks.

share|improve this question
2  
No, that is not possible. What was the nature of the error that occurred? –  Mark Storey-Smith Apr 8 '13 at 23:01
    
Thanks. I figured as much! :) –  Dan Atkinson Apr 8 '13 at 23:03
2  
Depending on what the nature of the error was and how valuable the lost data is, there may be a recovery route via specialist help or 3rd party tools. Was it a delete/drop or something more complicated? –  Mark Storey-Smith Apr 8 '13 at 23:05
    
It was a bad update which caused a large dataset to be updated with the wrong information. Not terribly valuable, no, but an annoyance to repopulate. :) –  Dan Atkinson Apr 9 '13 at 8:46
    
I restored the db from an archived backup and rolled forward, so no spilt milk. Thanks all for the info! –  Dan Atkinson Apr 11 '13 at 17:46

1 Answer 1

Its very difficult to reverse engineer and interpret SQL T-log records. As mentioned there are 3rd party tools like APEX SQL Log , RedGate's LOG Rescue, Log Explorer

Also, there is an undocumented command fn_dblog that can be used as below, but you wont have the flexibility like 3rd party tools.

SELECT 
    [Previous LSN],
    [Current LSN],
    [Operation],
    [AllocUnitName],
    [Page ID],[SPID],
    [Begin Time], 
    [Transaction Name],
    [Lock Information] 

FROM ::fn_dblog(null, null)
GO

Paul Randal has described how to read from T-log with excellent examples -- Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN .

share|improve this answer
    
Thank you for your answer. The Redgate and Log Explorer software are only for SQL Server 2000 however. –  Dan Atkinson Apr 9 '13 at 16:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.