Post reply

Restoring when all I have is the LDF file

  • 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

  • September 12, 2005 at 7:34 am

    #589054

    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

  • September 12, 2005 at 5:43 pm

    #589213

    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?

     

     

  • September 12, 2005 at 7:43 pm

    #589216

    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

  • September 13, 2005 at 2:26 am

    #589268

    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.

  • September 13, 2005 at 11:26 am

    #589461

    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."

  • September 14, 2005 at 5:23 am

    #589644

    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?

  • September 14, 2005 at 7:33 am

    #589701

    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

  • September 14, 2005 at 10:04 am

    #589775

    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."

  • September 20, 2005 at 6:24 am

    #591264

    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

  • September 20, 2005 at 4:14 pm

    #591535

    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.

  • September 20, 2005 at 4:16 pm

    #591537

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

     

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

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

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