Recover data from .mdf and .ldf file?
Hello!
Hopefully someone can help me with the following (potentially huge) problem:
We've got a simple database application running on microsoft sql desktop engine. This database contains two tables. Up until now all worked fine, but probably due to a programma that crashed part of the database seems to be corrupted or broken while today only one table contains/returns data and the other table does not return any values!
When I open the .ldf and .mdf files in notepad I see that there is data belonging to both the tables. But how do I recover it?
The max. size of the database has not been reached by far (it's only 9Mbs), the sql queries are correct.
This .ldf file is a log file (?) I can't find detailed info about the principles of these .ldf and .mdf files on the internet, is it possible to trace back what has happened en recover the database that way?
Thanks in advance.
Greetings,
Rens Voogd
[968 byte] By [
RensV] at [2008-2-20]
Hi Rens,
I have some questions for you.
- Can you define exactly what you mean by 'corrupted or broken'? Is it that there's missing data or is DBCC CHECKDB returning errors? (I'm guessing the former)
- You mentioned 'probably due to a programma that crashed'. Applications cannot cause corruption in SQL Server databases. The only things that can cause corruption are hardware issues or bugs in SQL Server.
- How can you tell what data is in the database from looking at the ldf and mdf in notepad?
- Do you have any backups?
Thanks
Hello,
I hope this provides you with more clues what has happened:
1) By corrupted/broken I mean that somehow one of the two table does not return any data from one day till the next. This table has not been dropped or cleared. The two tables are used to log measurement data from TestStand. The first table contains info about the testsequence that is run (start date, execution time, etc.) and the second table (the one that does not return data anymore) contains the test-step results belonging to the testsequences. The first table still contains and returns data, so we can see what tests are performed. However the second table with our measurement data does not return any data with what ever database viewer we use. There is nothing wrong with the queries, while all works fine on other systems with the same database setup.
About DBCC, is it included with the SQL Desktop Engine? Because I can't find it on the system, I only see and occasionally use BCP and OSQL. Is DBCC the only tool to check a database's consistency?
3) About opening the .mdf and .ldf files in notepad: I know what kind of data is written to the table, when I open de .mdf file I see familiar text-strings and measurement data of course I can't make anything sensible from the rest of the file.
2) That gives me some peace of mind to know that it is impossible for a program crash, in our case LabView, to cause corruption of an SQL server database.
4) Yes, we have made backups. But of course only from the day before, and we must have the testdata from exactly that day when the database became corrupt!!
Thanks!
Rens
Hello,
Short update for the people that might be interested. I've managed to recover the data from the transaction log file (which the .ldf file appeared to be) with a tool that I found. So it is possible to recover lost data from the .ldf/.mdf file!
We suppect the problem is caused by some sort of software installation fault or a wrong setting somewhere deep in the system. We weren't able to reproduce it on another system.
It would be nice to find a good description on the microsoft site of the purpose of the .mdf and .ldf files, and tools to use this transaction log.
Btw: dbcc seems to be a command that can be used on the osql prompt.....
Rens
Hi Rens,
Apologies for the delay in getting back to you on this thread.
Did you run DBCC CHECKDB? Did it return any errors? From your description, it sounds like the test software wasn't entering data into the database but its possible the database files became corrupted in some way. If that's the case (please post the CHECKDB errors), why o you suspect software as the cause?
We don't provide any tools to recover data from the transaction log - you must be using a 3rd part tool. We provide backup/restore and database repair functionality.
For a high-level description of data and log files, please see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_9sab.asp
Yours is a classic case of a backup strategy (once a day) not meeting the service-level agreement (no work loss). With your current backup strategy, you may lose up to a day's work depending on when the disaster occurs. If you want to shrink the window of potential work loss, you need to come up with a more comprehensive backup strategy, for instance:
- Put the database into full recovery mode
- Daily full backup
- Differential backups every 2 hours
- Log backups every 1/2 hour
Then, if a disaster occurs, you can take a final log backup (which captures every up to the point of failure) and through restoring all these backups you shouldn't have lost any work at all.
Hope this helps
Hello Paul,
Thanks for your reply, it's really helpful. I'm going to implement the backup strategy as you described as soon as possible. I don't want another disaster to hapen like this anymore!
The strangest thing is that the DBCC CHECKDB did not return any errors, and the database appeared to be working normal. But our data dissapeared just like someone accidentally performed a DROP TABLE. However this action is absolutely not performed! Luckily the 3rd party tool saved our work!
We haven't figured out yet what caused the problem. We suspect malfunctioning hardware while the other systems with the same setup work without a single problem.
Thanks,
Rens
Hello Rens,
I have a similar problem to the one you described. Could you let me know the 3rd Party tool you used to recover the transaction log data.
Thanks
Hi Ramaj,
The 3rd party with which I managed to save the data: ApexSQL Log (http://www.apexsql.com/sql_tools_log.asp)
Works perfectly! But don't forget to implement a good backup strategy afterwards to avoid these problems...
Good luck,
Rens
You can also use Red Gate SQL Log Rescue or Lumigent Log Explorer for SQL Server. Although ApexSQL's evaluation copy has no restrictions on databases while Lumigent Log Explorer only works for Northwind and Pubs databases.
