.MDF file issue.

Question

hi team,

we have one old database, this old database we don't  have any backup.

here problem is this database datafile(.MDF) file was corrupted unable to access the this database, what we can do how we access the this database.

Answer 1

1. Try to Full Backup the DataBase.

Or

1. Stop the service of SQL Server then copy the files of DB (ldf and mdf), to get a Backup.

----------------------------------------------------------------------------------------------------------------

2. Start the SQL Server.

3. Run this command :

DBCC CHECKDB youDataBase

Note : not use  REPAIR_ALLOW_DATA_LOSS parameter, beacause you will lose the data

For more : http://technet.microsoft.com/en-us/library/ms176064.aspx

Answer 2

Hello,

Just to confirm: The database is attached to SQL Server, but is not accessable because the DB is in "Suspect state"? Please check first the SQL Server ErrorLog if you can detailed information about the reasons etc.

See
Know How and When to Use Emergency Mode Repair

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 3

1. Try to Full Backup the DataBase.

Or

1. Stop the service of SQL Server then copy the files of DB (ldf and mdf), to get a Backup.

----------------------------------------------------------------------------------------------------------------

2. Start the SQL Server.

Hello,

Starting a SQL server service when database is suspect is fatal idea.You should never do that.I seriously recommend not to use it.

Since you said you dont have backup you can go for repair_allow_data_loss option in dbcc checkdb be aware that it might cause data loss

please also try to find out what caused your DB suspect .See errorlog and eventviewer for more details

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Answer 4

What is the current state of the database? How do you say it is corrupted? Did you check the errorlog?

If its corrupt, Try to fix it using DBCC CHECKDB commands

If any errors were found by CHECKDB, messages will be logged in the ERRORLOG for the purposes of Error Reporting. 

Try to understand what type of errors its giving and analyze the errors

When you run DBCC CHECKDB a recommendation is provided to indicate what the minimum repair option that is required to repair all errors

DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

http://msdn.microsoft.com/en-us/library/ms176064.aspx

If its not repairable, then you need to have a clean backup.

If DBCC CHECKDB command does not work properly and unable to repair your precious database then you can go for using third party tools

http://www.datanumen.com/sql-recovery/

-Prashanth

Answer 5

You may try third-party tools such as DataNumen SQL Recovery, which is designed to recover corrupt SQL Server MDF databases.

Answer 6

MSDN,

Be aware of the fact that Micosoft does not support these tools so you have to use it on your own risk

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Answer 7

error log showing below message:

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x75407f70; actual: 0xe9f668d2). It occurred during a read of page (1:0) in database ID 6 at offset 0000000000000000 in file 'K:\xxx.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Answer 8

error log showing below message:

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x75407f70; actual: 0xe9f668d2). It occurred during a read of page (1:0) in database ID 6 at offset 0000000000000000 in file 'K:\xxx.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Points to fact that your database is corrupted.YOur action should be to restore from fresh backup as already pointed out.Did you found out reason why it became suspect/corrupted ,you should do that .As a guess I would ask you to get your disks analyzed see errorlog

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Answer 9

we are using "DATANUMEN" tool , getting below error message:

fail to repair "K:\xxx.mdf "! like that....

 

Answer 10

we are using "DATANUMEN" tool , getting below error message:

fail to repair "K:\xxx.mdf "! like that....

 

Then please ask Tool vendor ,this won't be correct forum for that.

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Answer 11

Shanky,

here we don't have any database backup,that is the problem .

how we can resolve this issue, this issue happen past 5 days on words,

can you please give some valuable ideas.  

Answer 12

Shanky,

here we don't have any database backup,that is the problem .

how we can resolve this issue, this issue happen past 5 days on words,

can you please give some valuable ideas.  

Did you tried repair allow data loss ? Did it worked for you.As you posted even tool which you used cannot recover the database .In such case not much can be done.if possible you should try to take as much as data from DB if you can open it ofcourse .Otherwise I dont think there is any solution.Did you read following articles

http://www.sqlskills.com/blogs/paul/disaster-recovery-101-fixing-metadata-corruption-without-a-backup/

http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort/

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Answer 13

here we don't have any database backup,that is the problem .

how we can resolve this issue, this issue happen past 5 days on words,

You resolve this issue by restoring a clean backup, and if you don't have any you are in serious trouble, at least if this database is critical to you.

Well, you "only" have a checksum error. This could mean that the data is largely correct. This is the scope about the checksum: When SQL Server writes a page to disk, it computes a checksum which it stores on the disk.
When SQL Server reads the page, it recomputes the checksum and compares with what is stored on the page. If there is a mismatch, it sounds the alarm, becuase it indicates that the data has been altered when it was supposed to be at rest on the disk.

First, run DBCC CHECKDB, to see how wide the range of corruption is. I don't know for sure, but I suppose that it will produce an error for page with an incorrect checksum. It may also produce more errors, if the bits that have been flipped are in the internal page structure.

I could go on, but I have not performed this sort of exercise myself, and I don't want to give half-hearted advice in a potentially serious situation. I would recommend that you open a case with Microsoft. They will not repair your database, but they may help you with the process how to copy data out of the database. They should also help you with interpreting the error messages, so you that can judge exactly how critical these pages are.

And keep in mind that these errors do not come out of the blue. You should consider the hardware on which you have this file to be suspect and replace it. And you should make sure that if you have other databases that are dear to you that they are backed up properly...

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 14

You can check out an other excellent application for SQL Database Recovery from here :- http://www.recoveryfix.com/sqldatabaserecovery.html by using this utility you can repair badly damaged MDF file of SQL Database in less time.

I like to point out that if you decide to use any such tool, that you work on a copy of your damaged file. A recovery tool may be able to recover data, but keep that basically they only do the same as DBCC CHECKDB REPAIR. They may be more aggressive in which repairs they perform, but this also means that they may perform repairs that are not safe.

In this particular case, checksum error, it is not difficult to make changes to the data file to keep SQL Server from screaming blue murder, at least as long as the bad bits has not caused any other physical inconsistency. You simply compute a new checksum from the existing bits, and you are done. But does this mean that your database is now all and well? Of course not! Very most likely you have incorrect data on that page. The error may be trivial, the customer "Smith" is now listed as "SmiTh". (one bit in "t" has been
zeroed). But it may also means that Smith's salary has risen from 5000€/month to 70536€/month. (One bit incorrect in an int.)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 15

Agree with Erland this type of question works more like advertiesment to various tools.I have never used such tools so cannot comment more but opening case with MS as pointed out is a good option.It will surely help you recover as much data as possible.

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers