0

Recently, one of my customers had a server crash. Unfortunately, the 3th party who was responsible for the backup was 'forgotten' to schedule a backup of the DB (I know, I should have notice that).

After a professional recovery, I have the .mdf and .ldf files back. I was able to attach them back to SQL Server 2005 Express. At first glance, everything looked ok.

However, the most recent changes (say from one month before crash) are missing. Has anyone seen that before? Does anyone knows a solution for that? Is it possible that it is still in the .ldf? I think the recovery model was Simple.

As a side note, I'm sure that the data was there since there is a printed version. I'm also sure that it is the most recent .mdf / .ldf that is recovered and sent.

I did already several DBCC checkDB with different parameters (every time on a fresh .mdf/.ldf combination of course). I know there is a consistency error somewhere but it doesn't block me to query the last 20000 rows. So expect to find the last records there (or I am wrong here?). The fix of this consistency error is lower prio since it occurs in the older data region. Worst case scenario, we can remove that part.

So, please help me with the missing data.

Thanks in advance!

0

2 Answers 2

2

Did you run a DBCC CHECKDB on the 'recovered' database? Is is consistent?

Your case doesn't hold water though. In theory the most you could had lost was the transactions present in the log (LDF) but not flushed in the data (MDF). That would amount, at most, to the dirty pages not flushed since the last checkpoint, which would be minutes of data loss, definitely not days.

I'm also sure that it is the most recent .mdf / .ldf that is recovered and sent.

Being wrong on this point would be, by far, the simplest explanation. Other explanation is that you deleted the data after attach, which is kind of silly. Or the recovery returned a corrupted MDF that is missing data, but that would fail with corruption errors when trying to query it. I would double/triple check again that is indeed the latest mdf/ldf that was recovered.

6
  • I can only agree with @remus-rusanu. Just a question, you're talking of a professionnal recovery that enabled you to get the .mdf and .ldf files. Can you give us some extra information on this? Mar 29, 2013 at 9:00
  • For me, the disk was dead. So I sent it to a specialized company to recover some data. With their help, we were able to retrieve the mdf en ldf file. Now, remus-rusanu, you are propably right. A couple of hours ago, I started a new DBCC CheckDB and it is still running. Before it was ending very quickly. So, maybe it does have something todo with the consistency error that i mentioned in my question. Thanks for your help
    – bvbiz
    Mar 29, 2013 at 10:23
  • BTW, I'm glad to hear that in fact the lost data should be from the last minutes and not months. So, I push my focus on the consistency error first
    – bvbiz
    Mar 29, 2013 at 10:29
  • 1
    Right now that data is a gonner. There are services that manually repair DBs and can do a better job than allow_data_loss, from what I hear. YMMV. Mar 29, 2013 at 13:16
  • 1
    DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS option is usually the last measure I would try, as Paul Randal has blogged quite a few times: sqlskills.com/blogs/paul/… . But it's not supposed to delete massive amounts of data anyway, at least not a month of work. Mar 29, 2013 at 13:31
1

I hate to bear bad news, but if the recovery mode was SIMPLE, the portion of the log that contained the "lost" transactions was marked for reuse as soon as the transaction was committed. So the missing transaction won't be recoverable from the .ldf file.

SIMPLE recovery does not allow for point-in-time recoveries, it only permits recovery at the point of a full backup.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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