DataNumen logo Header Header
  Site Search:
 
  Home » Products » DataNumen SQL Recovery » Recover a Corrupt SQL Server MDF Database
 
         
 
Arrow DATANUMEN SQL RECOVERY
   
  News
  Recovery
  Download
  Order
  FAQ
  View Screenshot
  Awards
   
Arrow RELATED PRODUCTS
Arrow DataNumen Access Repair
Arrow DataNumen DBF Repair
Arrow DataNumen Database Recovery
Arrow DataNumen Oracle Recovery
 
Recover a Corrupt SQL Server MDF Database

When you SQL Server MDF database is corrupt, you can use the following methods to recover the data in it, to reduce the loss:

Note: Before performing any data recovery operations, please backup your corrupt MDF & NDF database files first.
  1. First of all, you can try SQL Server built-in command DBCC CHECKDB to recover your database. Assuming the corrupt database file is 'MyDatabase.mdf', then you can do as follows to recover the data inside it:
    1. Restart SQL Server.
    2. Do not perform any operations.
    3. In SQL Server Management Studio, execute the following SQL statements:

      use master
      declare @databasename varchar(255)
      set @databasename='MyDatabase.mdf'
      exec sp_dboption @databasename, N'single', N'true' -- Set the destination database to single-user mode
      dbcc checkdb(@databasename,REPAIR_ALLOW_DATA_LOSS)
      dbcc checkdb(@databasename,REPAIR_REBUILD)
      exec sp_dboption @databasename, N'single', N'false' -- Set the destination database back to multi-user mode

      The statement

      dbcc checkdb(@databasename,REPAIR_ALLOW_DATA_LOSS)

      may cause loss of some data in your database when perform the repair operation.

      While the statement

      dbcc checkdb(@databasename,REPAIR_REBUILD)

      will NOT cause any data loss during the repair operation, but it is more time-consuming.

      So if you are not on urgent, you can first try to perform the second dbcc checkdb statement only, if that statement cannot help to recover the database, then you can perform the first and second dbcc checkdb commands.

      After the repair operation, you can call

      dbcc checkdb('MyDatabase.mdf')

      again to see if the database is fixed or not.


    More detailed information about dbcc checkdb command can be found at http://technet.microsoft.com/en-us/library/ms176064.aspx

  2. If after using method 1, dbcc checkdb still reports errors in some tables, you can try SQL Server built-in command DBCC CHECKTABLE to recover these tables in your database. Assuming the corrupt database file is 'MyDatabase.mdf' and the table you want to repair is 'MyTable', then you can do as follows to repair it:
    1. Restart SQL Server.
    2. Do not perform any operations.
    3. In SQL Server Management Studio, execute the following SQL statements:

      use MyDatabase.mdf
      declare @dbname varchar(255)
      set @dbname='MyDatabase.mdf'
      exec sp_dboption @dbname,'single user','true'
      dbcc checktable('MyTable',REPAIR_ALLOW_DATA_LOSS)
      dbcc checktable('MyTable',REPAIR_REBUILD)
      exec sp_dboption @dbname,'single user','false'

      The statement

      dbcc checktable('MyTable',REPAIR_ALLOW_DATA_LOSS)

      may cause loss of some data in your table when perform the repair operation.

      While the statement

      dbcc checktable('MyTable',REPAIR_REBUILD)

      will NOT cause any data loss during the repair operation, but it is more time-consuming.

      So if you are not on urgent, you can first try to perform the second dbcc checktable statement only, if that statement cannot help to recover the database, then you can perform the first and second dbcc checktable commands.

      After you complete the repair operation for all the corrupt tables, you can call

      dbcc checkdb('MyDatabase.mdf')

      again to see if the tables in the database are fixed or not.


    More detailed information about dbcc checktable command can be found at http://technet.microsoft.com/en-us/library/ms174338.aspx

  3. If method 1 and 2 still cannot fix the corrupt database or the tables, or if these methods cannot recover the data you want, then you can use third-party tools to recover the data from your MDF database.