7

We had a SQL Server 2005 server that housed roughly 200 low/no traffic archive databases, and that server was not backed up to any other device. The database files (MDF and LDF) were stored on a RAID array, and when the RAID controller failed we were concerned we were going to lose our data. We sent the RAID controller and array to a data recovery company, and they were able to recover most of the files, but in some cases we have the transaction log but are missing the main data files.

Is it possible to restore the database to the last transaction (which should be just after the database was created) using just the transaction log, or is this something that can't be done?

  • Wow no backup plan? – JonH Dec 23 '15 at 4:52
  • Yes, unfortunately I inherited a system with no backup plan. Fortunately, nearly all the important functionality had been migrated off to another server literally minutes before it failed. – LegendaryDude Dec 23 '15 at 17:11
10

Is it possible to restore the database to the last transaction (which should be just after the database was created) using just the transaction log, or is this something that can't be done?

No, restoring a transaction log is sequential. Transaction log relies on LSN (Log Sequence Number)

Also, you cannot restore your database with just transaction log. It requires the main database file (MDF) and NDF (secondary datafiles if there are any).

  • 1
    awesome answer. it must be +1 – Md Haidar Ali Khan Dec 22 '15 at 19:29
  • 2
    If the data is really important and there are no backups you can rely on, first fix the backup/restore story so you won't be in this situation again in the future. There is no "if", hardware and systems will fail so protect yourself. Then look at some of the .ldf reader tools out there from Apex, Toad, etc... It'll be a tedious manual effort but if you have the files, you'll get at least some of the data back. – SQLmojoe Dec 22 '15 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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