locked
Repair corrupted SQL server mdf file RRS feed

  • Question

  • Our SQL server database (2008 R2) has got corrupt due to physical failure and data have been lost.
    The last full backup is very old.

    Can anyone suggest me how to repair corrupt mdf file & recover data from it?

    I have already tried DBCC CHECKDB repair options but no success.
    Thursday, July 10, 2014 6:44 AM

Answers

  • Before you try the random tools suggested here, please make sure that you save a copy of the MDF file, so that you don't make things even worse. Although, to be honest, the database is most likely lost. Maybe data can be squeezed out of it, but it may be prohibitly expensive.

    You said that you were not able to query the database. What exactly does that mean? Is the database marked supsect and inaccessible? Or do you get an error when you try to query it?

    What you can try is a "hack-attach" as described here:
    http://www.sqlskills.com/blogs/paul/disaster-recovery-101-hack-attach-a-damaged-database/
    This way you may be able to access the database so that you can query it and extract data.

    It is worth adding that this is a process that requires good understanding of the data as well as a good understanding of SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Swan Darren Tuesday, July 15, 2014 4:01 AM
    Friday, July 11, 2014 12:13 PM
  • Hi Swan,

    I would like to suggest you 3rd party Stellar Phoenix SQL database repair software. It repairs corrupt SQL server database mdf & ndf files and supports SQL server 2012, 2008 R2, 2008, 20005 and other lower versions.

    Software is available as free demo & full feature versions, first try demo version to see the preview of corrupt database file. 

    Note: Make a copy of database and perform repair operation on the copied database.

    How to Repair Corrupt SQL Database Files with Stellar Phoenix SQL Database Repair

    Thanks

    • Marked as answer by Swan Darren Tuesday, July 15, 2014 3:55 AM
    Monday, July 14, 2014 3:26 AM

All replies

  • Hello Swan,

    Restore a backup and DBCC CHECKDB are the only Options you have to recover a damaged database.

    If you have the last full backup and your database is in recovery mode "Full", then you can run right now a log backup, and together with the full backup you can perform a Point-in-time restored to the Point of the last valid state of your database; see How to: Restore to a Point in Time (SQL Server Management Studio)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Thursday, July 10, 2014 10:12 AM
  • Are you able to query the database in anyway?

    If so try to script out as much as data as possible.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Thursday, July 10, 2014 10:17 AM
  • For your case, just wonder if you have choose the options REPAIR_REBUILD and REPAIR_ALLOW_DATA_LOSS when using DBCC CHECKDB to repair the corrupt MDF file? See http://technet.microsoft.com/en-us/library/ms176064.aspx for more detailed info.

    If REPAIR_REBUILD and REPAIR_ALLOW_DATA_LOSS do not work, then you may try some third party tools. The one I have used successfully is DataNumen SQL Recovery at http://www.datanumen.com/sql-recovery/ . There is a free demo so you can try to see if it works on your file.

    Good luck!
    Thursday, July 10, 2014 7:33 PM
  • Hello,

    Would it be possible for you to put the output of just a regular CHECKDB (no repair or anyting) with NO_INFOMSGS, ALL_ERRORMSGS? It would aid in knowing how bad the issues are.


    Sean Gallardy | Blog

    MCM 2008

    MCSM:Data Platform Charter Member

    Thursday, July 10, 2014 7:45 PM
  • Hi,

    Since the backup copy is very old. So the restore is not an option here.

    Please post the result of DBCC CHECKDB.

    Corruption, means that the internal structure of the database is inconsistent. We cannot predict what or how much data may be lost. To repair database, you may encounter data loss and/or physical and/or logical inconsistency.

    Thanks.


    Tracy Cai
    TechNet Community Support

    Friday, July 11, 2014 7:53 AM
  • @Ashwin Menon No, I'm not able to query the database. 
    Friday, July 11, 2014 9:35 AM
  • For your case, just wonder if you have choose the options REPAIR_REBUILD and REPAIR_ALLOW_DATA_LOSS when using DBCC CHECKDB to repair the corrupt MDF file? See http://technet.microsoft.com/en-us/library/ms176064.aspx for more detailed info.

    If REPAIR_REBUILD and REPAIR_ALLOW_DATA_LOSS do not work, then you may try some third party tools. The one I have used successfully is DataNumen SQL Recovery at http://www.datanumen.com/sql-recovery/ . There is a free demo so you can try to see if it works on your file.

    Good luck!

    I have tried the software suggested by you but no success.

    Friday, July 11, 2014 9:37 AM
  • Would you be able to post the checkdb output so that we can try to help as much as we can.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Friday, July 11, 2014 10:02 AM
  • Before you try the random tools suggested here, please make sure that you save a copy of the MDF file, so that you don't make things even worse. Although, to be honest, the database is most likely lost. Maybe data can be squeezed out of it, but it may be prohibitly expensive.

    You said that you were not able to query the database. What exactly does that mean? Is the database marked supsect and inaccessible? Or do you get an error when you try to query it?

    What you can try is a "hack-attach" as described here:
    http://www.sqlskills.com/blogs/paul/disaster-recovery-101-hack-attach-a-damaged-database/
    This way you may be able to access the database so that you can query it and extract data.

    It is worth adding that this is a process that requires good understanding of the data as well as a good understanding of SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Swan Darren Tuesday, July 15, 2014 4:01 AM
    Friday, July 11, 2014 12:13 PM
  • Hi Swan,

    I would like to suggest you 3rd party Stellar Phoenix SQL database repair software. It repairs corrupt SQL server database mdf & ndf files and supports SQL server 2012, 2008 R2, 2008, 20005 and other lower versions.

    Software is available as free demo & full feature versions, first try demo version to see the preview of corrupt database file. 

    Note: Make a copy of database and perform repair operation on the copied database.

    How to Repair Corrupt SQL Database Files with Stellar Phoenix SQL Database Repair

    Thanks

    • Marked as answer by Swan Darren Tuesday, July 15, 2014 3:55 AM
    Monday, July 14, 2014 3:26 AM
  • Thanks Mark and Erland for your suggestions.
    Tuesday, July 15, 2014 4:01 AM
  • To repair MDF file, open SSMS and execute DBCC statements (checkdb and dbrepair) as a new query. This SQL query scans the corrupt database file and repairs it. It also recovers objects of corrupt database. This recovery is possible is there are minor corruptions.

    For proper solution, follow these process:


    1. Dbcc checkdb ('database_name') with no_infomsgs
    You do not need to repair file if the above statement is executed without any errors. Before executing the repair operation, you need to set the database in single user mode by executing:

    2. Alter database 'database_name' set SINGLE_USER
    You can now start repairing process by using Repair_Rebuild model by executing:

    3. Dbcc checkdb('database_name', REPAIR_REBUILD)
    If this statement executes successfully, then execute 5.5 else execute:

    4. Dbcc checkdb('database_name', REPAIR_ALLOW_DATA_LOSS)
    This statement may lead to data loss. If it is executed properly then execute 5.5 else execute:


    5. 5.1 Alter database 'database_name' set EMERGENCY
    5.2 Alter database 'database_name' set SINGLE_USER
    5.3 Dbcc checkdb ('database_name', REPAIR_ALLOW_DATA_LOSS) with no_infomsgs, all_errormsgs
    5.4 Alter database 'database_name' set ONLINE
    5.5 Alter database 'database_name' set MULTI_USER

    Reference:- http://repairandmanage.blogspot.in/2014/06/sql-server-recovery.html

    Thursday, September 25, 2014 9:18 AM
  • Our SQL server database (2008 R2) has got corrupt due to physical failure and data have been lost.
    The last full backup is very old.

    Can anyone suggest me how to repair corrupt mdf file & recover data from it?

    I have already tried DBCC CHECKDB repair options but no success.

    If corruption is severe, the DBCC CHECKDB command may fail or not work. In such a situation, you should consider using a professional recovery solution. There are many vendors in the online marketplace which provide SQL Repair tool that can repair MDF file and restore SQL database objects from it. But before you can purchase any such software, make sure you download the demo version for free evaluation. By using the demo version, you can scan the corrupt MDF file and check the preview of your database objects prior to saving. And if you're satisfied with the results, then only you should purchase the software.

    Some useful information can be found in this article - Database Corruption – Top 4 Reasons You Should be Aware Of

    Thanks.

    Tuesday, December 11, 2018 12:36 PM
  • Hi, Agreed with the answer given by Erland Sommarskog. I Found an informative article for you which explains what to do when DBCC CHECKDB fails. I hope this will help.  Here is the Link

    https://academyict.net/2018/12/04/know-what-if-dbcc-checkdb-fails/

    Thanks and Regards  

    Wednesday, April 3, 2019 5:29 AM
  • May be  the MDF file has got damaged due to file header corruption or wrong information in file header.All such situations lead to the inaccessibility of MDF file and need to repair and restore the database. You can use SQL recovery software which helpful in each and every case of MDF corruption and thus allow you to have perfect MDF recovery.Stellar Phoenix SQL Database Recovery is recovery software which allows you to have effective and successful MDF recovery. This MDF repair software can extract all of the data from it. This software can restore all of the MDF objects including tables, reports, forms, macros, database constraints, stored procedures, triggers etc.
    • Edited by Dexter Data Tuesday, November 12, 2019 6:20 AM
    Saturday, August 3, 2019 9:36 AM