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.

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 https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15
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 https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checktable-transact-sql?view=sql-server-ver15
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 DataNumen SQL Recovery to recover the data from your MDF database.