locked
How to repair .mdf file?

    Question

  • My sql databases in SQL Server 2014 has the status "suspend" as I saw in SQL Management Studio. I can't restore to serviceable condition sql databases through standard procedures. I need to restore .mdf file.
    Thursday, March 20, 2014 11:14 PM

Answers

  • You may try to use third party tool SQL Server Repair Toolbox. Free demo version is available from here http://www.sqlserver.repairtoolbox.com/how-to-repair-an-mdf-file.html

    In case this solution couldn't help you, make use of next steps: 

    1. select ‘run’ from ‘start’ enter 
    2. ‘cmd’ and then enter type “chkdsk 
    3. x: /r” and press enter key, here X represents drive where mdf file is 
    4. stored to confirm restore press “Y” restart system, the drive will be scanned at startup and the MDF file is recovered
    Friday, March 28, 2014 8:59 PM
  • I believe you may mean status "suspect" instead of "suspend".  The first step is to identify the cause the database was marked suspect.  A common cause is inaccessible database files, either because they are missing or due to a permission issue.  If the file is corrupt, your best bet is to restore from backup.

    If you need further help, I suggest you post this question to the Database Engine forum (http://social.msdn.microsoft.com/Forums/en-US/home?forum=sqldatabaseengine), which is more appropriate for this question.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, March 21, 2014 2:43 AM
    Moderator
  • My sql databases in SQL Server 2014 has the status "suspend" as I saw in SQL Management Studio. I can't restore to serviceable condition sql databases through standard procedures. I need to restore .mdf file.

    Before going to any tool and spending money.There are lot of SQL Server commands to get your database back.

    Now do you have backup or not ?.If you have as Dan mentioned restoring from backup would be best solution

    If you don't have backup  .Can you run DBCC CHECKDB for database as per below query

    DBCC CHECKDB DB_NAME WITH NO_INFOMSGS ALL_ERRORMSGS.

    Post the result here so that we can analyze what can be done.

    If your not allowed to run checkdb then below will help

    ALTER DATABASE [db_name] SET EMERGENCY;
    GO
    DBCC CHECKDB (N'db_name', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
    GO
    --If it gives error database needs to be in single user mode run below query
    
    ALTER DATABASE [db_name] SET single_user;
    GO
    DBCC CHECKDB (N'db_name', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
    GO
    Please note Only try this method if you don't have backup because it can l cause DATA LOSS


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


    Saturday, March 22, 2014 10:18 AM

All replies

  • I believe you may mean status "suspect" instead of "suspend".  The first step is to identify the cause the database was marked suspect.  A common cause is inaccessible database files, either because they are missing or due to a permission issue.  If the file is corrupt, your best bet is to restore from backup.

    If you need further help, I suggest you post this question to the Database Engine forum (http://social.msdn.microsoft.com/Forums/en-US/home?forum=sqldatabaseengine), which is more appropriate for this question.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, March 21, 2014 2:43 AM
    Moderator
  • My sql databases in SQL Server 2014 has the status "suspend" as I saw in SQL Management Studio. I can't restore to serviceable condition sql databases through standard procedures. I need to restore .mdf file.

    Before going to any tool and spending money.There are lot of SQL Server commands to get your database back.

    Now do you have backup or not ?.If you have as Dan mentioned restoring from backup would be best solution

    If you don't have backup  .Can you run DBCC CHECKDB for database as per below query

    DBCC CHECKDB DB_NAME WITH NO_INFOMSGS ALL_ERRORMSGS.

    Post the result here so that we can analyze what can be done.

    If your not allowed to run checkdb then below will help

    ALTER DATABASE [db_name] SET EMERGENCY;
    GO
    DBCC CHECKDB (N'db_name', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
    GO
    --If it gives error database needs to be in single user mode run below query
    
    ALTER DATABASE [db_name] SET single_user;
    GO
    DBCC CHECKDB (N'db_name', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
    GO
    Please note Only try this method if you don't have backup because it can l cause DATA LOSS


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


    Saturday, March 22, 2014 10:18 AM
  • If you don't have database backup, then the only way to get into the database is to use EMERGENCY mode. This will allow you to get into the database. For detailed information about EMERGENCY MODE REPAIR, see this article: 

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

    To use emergency mode repair, you need to put database in emergency & single user mode 

    ALTER DATABASE [Database] SET EMERGENCY;
    GO
    ALTER DATABASE [Database] SET SINGLE_USER;
    GO
    DBCC CHECKDB (N'Database', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
    GO

    Note: Emergency mode repair with repair_allow_data_loss will cause data loss.

    You may also try 3rd party SQL repair program to avoid data loss. 

    Monday, March 24, 2014 4:46 AM
  • You may try to use third party tool SQL Server Repair Toolbox. Free demo version is available from here http://www.sqlserver.repairtoolbox.com/how-to-repair-an-mdf-file.html

    In case this solution couldn't help you, make use of next steps: 

    1. select ‘run’ from ‘start’ enter 
    2. ‘cmd’ and then enter type “chkdsk 
    3. x: /r” and press enter key, here X represents drive where mdf file is 
    4. stored to confirm restore press “Y” restart system, the drive will be scanned at startup and the MDF file is recovered
    Friday, March 28, 2014 8:59 PM
  • I would request moderators to please lock any thread related to disaster recovery after a period of time so that advertisers cannot use it for advertising any tool.

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

    Friday, March 28, 2014 9:52 PM
  • Thank you a lot Blake. I resolved my issue owing to SQL Server Repair Toolbox. It saved me! Good day...
    Saturday, March 29, 2014 2:13 PM