Restoring when all I have is the LDF file

Question

Restoring when all I have is the LDF file

Matt Krevs

SSC Rookie

Points: 34
More actions
September 12, 2005 at 12:14 am

#363583

Hi all
I have a customer that has just realised they havent been backing up a production database. One of their hard drives failed and the only file they have is the transaction log file (.LDF) which was being backed up to another hard disk which is operational.
They dont have _any_ backups of the original database (MDF) at all.
I have no knowledge of the database structure itself, but I was hoping to be able to view this transaction log file so I can at least find out what the table and view names in the destroyed database were, and perhaps to somehow apply the log to an empty database with empty tables in it.
Do I have any hope at all?
The whole project involved upsizing an MS Access db to SQL Server so I do have an MS Access db from 4 months ago so I know the structure of the db before it was upsized. I also have an MS Access db that contains the forms and linked tables to the SQL Server db that died so I have an MS Access interpretation of the new db structure.
Can anyone recommend a course of action (other than shooting the people who didnt do any backups)?
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

You are out of luck.. You need a full backup to be able to restore a Transaction Log. There's no 'native' way to read the transaction log, you need a third-party product like Lugiment's Log Exploreer, but that needs to be installed before you need it.

-SQLBill

Matt Krevs SSC Rookie Points: 34 More actions · Answer 2

Thanks for the reply

You said in your post "...you need a third-party product like Lugiment's Log Exploreer, but that needs to be installed before you need it."

I've installed several log reading applications but I havent been able to work out how to interrogate the LDF I have. It seems as though the LDF needs to be part of a complete database (ie paired with a valid MDF) before I can look at the log.

Would you agree? Am I (or my customer ) truly screwed or do these log viewing applications contain functionality that allows you to use/view/restore an orphaned LDF against a different MDF?

philcart SSC-Forever Points: 47796 More actions · Answer 3

Have you tried Log PI http://www.logpi.com/

I seem to recall that had the functionality to load log backups. Could get you somewhere maybe

--------------------
Colt 45 - the original point and click interface

Andrew Barton Mr or Mrs. 500 Points: 520 More actions · Answer 4

There are companies that specialize in restoring data from damaged disk drives but it isn't cheap. If all else fails this might be an option for them.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

Your situation is unfortunate. Now you need to turn to 'religion' for the answers. For the present, you can 'pray' that a media restoration company (for about 5k per 36gb disk) can 'ressurect' your mdf file. Even then, there is no gurantee that it is in the proper state for your ldf file. So your faced with a 'single file attach' situation at the very best. The very woorst is that you (and your client) turn to 'religion', again, and begin to practice the 'rites' of 'backup'. Then you'll only have to 'pray' that the off site media can be recovered when needed.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

ziller SSC Eights! Points: 897 More actions · Answer 6

Just a thought... but

Assuming there were no changes to the database structure since it was created, and the log contains all transactions since the sql db was created, couldn't he just rerun the upsizing from the original access db into a new database, then apply the transaction log to that?

SQLBill SSC Guru Points: 51440 More actions · Answer 7

No. The transaction log is 'flagged' to go with a specific .mdf (data file). The headers won't be the same. Log files use a LSN (I believe it stands for Logical Serial Number) to show which .mdf it applies to.

-SQLBill

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 8

LSN = log sequence number

(Since I didn't know the acronym positivelty I googled for "SQL Server LSN")

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 9

Did you try:

"Apex SQL Log

Apex SQL Log is a powerful Microsoft SQL Server log auditing tool that analyzes SQL Server's own transaction log to display information on data and structure changes

Since Apex SQL Log reads the transaction log, no database overhead is required and audits can be done on changes made even before the tool was installed.

Apex SQL Log has the ability to read online logs, detached logs and log backups. It allows users to recover dropped/truncated tables, tracks individual row history, and can generate UNDO and REDO scripts on the fly. Log also features powerful filtering abilities to help users locate and isolate specific transactions quickly and easily."

... Evaluations are FULLY FUNCTIONAL the only restriction is a 14 day evaluation period *

If it works (by creating REDO-Scripts) the company might consider buying the Software...

regards karl

Best regards
karl

Matt Krevs SSC Rookie Points: 34 More actions · Answer 10

Yes I tried Apex Log and every other product I could think of.

They all have the same restriction as SQL Server does - namely that you need to have an original MDF file to pair with the LDF file otherwise the LDF file cannot be read.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 11

... 'religion' is still the answer ...

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."