Repairing Corrupt MDF File of SQL Server Database

It is not unusual for one to encounter a damaged MDF file of MS SQL Server database. The simple reason being, it is prone to getting corrupt, and any discrepancy in handling the same can result in a corrupt MDF file. When this happens, all the data stored in the database becomes inaccessible, therefore, leading to data loss. In such situations, what becomes essential is to repair corrupt MDF file of the SQL Server database storage.

This Blog will describe how to perform the repair process. Also, it will also give you the in-depth knowledge of the MDF file, reasons as to why it gets corrupt, identifies some tools to repair it, and also the most efficient resolution in case the free repair methods fail to produce healthy SQL Server database file stored in MDF format. Let’s get started!

What is an MDF file?

The MDF file housed in the Microsoft SQL Server, a relational database management system, is the primary data file of the database that stores all data. Therefore, you can also refer it as the main or master database file of the SQL Server. Each SQL Server database would contain at least one MDF file.

In-built tools to repair the damaged MDF file

Here, you will get to know about some tools that can repair corrupt MDF files of SQL Server and consequently, make the stored data available. In fact, these tools are a series of statements (commands) in Transact-SQL programming language and are known as Database Console Commands (DBCC). Earlier, DBCC was known as Database Consistency Checker. The function of these commands in DBCC is to test the physical and logical uniformity of the SQL Server database files as well as fix the troubling issues that persists.

DBCC CHECKDB is a statement through which you can check the logical and physical integrity of all the objects in a specific SQL Server database. You can do so by executing the following operations sequentially:

Running DBCC CHECKALLOC command in the database.

Running DBCC CHECKCATALOG command in the database.

Running DBCC CHECKTABLE on each view and table in the database.

Authenticating the content of each indexed view available in the database.

Authenticating link-level constancy among table metadata, file system directories, and files when storing varbinary (max) data using FILESTREAM in the file system.

Authenticating the Service Broker data in the database.

On completion of the above steps, if the utility finds any corruption issues or errors it recommends users to make use of several Repair options for fixing the troublesome issues. The Repair options are:

REPAIR_FAST – It maintains syntax for backward compatibility only; no repair actions are performed in actual. The syntax for this Repair option is:

DBCC CHECKDB (‘DB Name’, REPAIR_FAST)

REPAIR_REBUILD – This repair option executes repair procedure that scarcely has possibilities of data loss. This can perform quick repairs such as repair of the missing rows in non-clustered indexes, and even time-consuming repairs such as the rebuilding of indexes. The syntax is:

DBCC CHECKDB (‘DB Name’, REPAIR_REBUILD)

Note – It (REPAIR_REBUILD) does not fix errors that include FILESTREAM data.

REPAIR_ALLOW_DATA_LOSS – This command makes an effort to fix all the errors that are reported. However, it can cause loss of data as stated in the repair command itself. The syntax is:

DBCC CHECKDB (‘DB Name’, REPAIR_ALLOW_DATA_LOSS)

Note – You may end up losing data after performing repair operation with REPAIR_ALLOW_DATA_LOSS. Therefore, you should always use it as the last resort if none of the other repair options works.

Few Checks with Built-in Repair Options

Care should be taken while using these three free Repair options for the repair of damaged SQL Server MDF file.

The first and foremost requirement is that the particular database should be in a single-user mode to be able to run either of the three Repair commands. When a user does not set the database in single user mode then, the following error message occurs:

“Repair Statement is not processed. Database needs to be in Single user mode.

You can alter the SQL database into single user mode by the following command:

ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Secondly, the syntax to execute the DBCC CHECKDB repair commands must be used correctly. It is only then that the discrepancies shall get fixed, resulting in an entirely healthy database file.

The DBCC repair statements are not validated memory-optimized tables. In such cases, i.e. if integrity issues occur in a memory-optimized table, you must restore the backup to gain access to the data in MDF file, provided the backup file is not in a damaged state.

The DBCC DBCHECK repair tools failed! What next?

If the built-in DBCC DBCHECK fails to repair the corrupt MDF file, the next thing that you can do is to run any external SQL recovery software. However, it should have the capability to outshine the manual methods and achieve what is expected out of it. Stellar Phoenix SQL Database Repair software can be used for repairing the corrupt MDF files as well as extract the data stored within it.

The concept, especially the last paragraph when DBCC CHECKDB fails to fix the corruption and, what can a user further do to fix it. I installed Stellar Phoenix SQL Database Repair tool.

I select the MDF file and press repair button. A process happened and the whole data was listed on the screen. I checked the preview of my data and, all records were correct. It is a good tool to repair MDF file.

You can download the demo version of Stellar Phoenix SQL Database repair tool try on corrupt .MDF file. You can see the preview of all recoverable objects. It is the best way to find the right SQL repair software.

In my case, DBCC CHECKDB was not working and showing the out of space error. I read a blog of Paul Randal and found some reasons. These are:

1. DBCC CHECKDB uses database snapshot. It requires a snapshot file for each file in the source database. In a case of DBCC CHECKDB, these files created at the same place of existing database files. The user cannot control this and, when any changes occur during DBCC CHECKDB then snapshot files grow.

2. During database scanning, In-memory worktable stores the information to match the information for next scanning process. By this way, the info exceeds available memory in worktable causes on tempdb. Tempdb grows and if it can’t then DBCC CHECKDB get fails.