The MDF file of SQL database is very prone to the corruption and causes inaccessibility of data. The main issue arises when the MDF file of the SQL database goes corrupted. It causes loss of data because the user is unable to access the data stored in the database. So it is very important to repair the corrupt MDF file from the SQL Server database storage. The below article discuss the MDF file in the SQL database, comprising the reason for its corruption along with all the simple tricks to repair the crashed SQL Server database easily. So let’s start with MDF file.
MDF file is also known as the master database file. It is the primary data file and stores all the information of the SQL database. MDF files are the group of a primary element for the SQL database. The stored component of the MDF files are as Tables, Index, Views, Triggers, and Rules.
MDF files easily get corrupted. There are several reasons that lead for corruption in MDF file. Following are some common reasons that causing crash in MDF file of SQL Server database:
1. Sudden power failure
2. Error arrived in the server
3. Corrupted Operating System
4. Improper system Shutdown
5. Hard Drive crashing
6. Virus affected systems
7. Updating to SQL Server to a newer version also results in database corruption issue.
Note: Among all the above reason hardware crashing is the most frequent one and happens in most of the cases.
The MDF file can easily be recovered with the help of Database Consistency Checker or Database Console Commands(DBCC). The commands check the physical and logical uniformity of the SQL Server database and troubleshoot all the existing issues.
Following are the command to run the operations in the database and need to be executed sequentially:
1. DBCC CHECKALLOC is used to check the file allocation of the database
2. DBCC CHECKCATALOG checks for catalog consistency within the specified database
3. DBCC CHECKTABLE is supported on databases that having memory-optimized tables in presence of disk-based tables.
4. FILESTREAM stores the varbinary(max) binary large object (BLOB) data as files on the file and authenticate the link-level constancy in the metadata table, file system directories, and files.
5. Finally proceeding to authenticate the Service Broker data in the database.
Note: If the user gets any corruption issue, it is recommended to proceed for the other repair process to resolve the issue.
The DBCC CHECKDB also check the:
1. Index Pointers
2. Data-Page Offsets
3. Linking between data pages and index pages
4. Structural content of the data and index pages
DBCC CHECKDB indicates the repair level required to fix the particular error in the database. Mostly the error reported by the DBCC CHECKDB is recommended to repair the database with an existing healthy backup. However, if the healthy backup is not present then it is required to use the repair with DBCC, with following syntax command:
DBCC CHECKDB (‘DB Name’, REPAIR_FAST)
REPAIR_REBUILD: If the missing rows in the non-clustered index need to be repaired and indexes need to be rebuilt. The following syntax is used:
DBCC CHECKDB (‘DB Name’, REPAIR_REBUILD).
REPAIR_ALLOW_DATA_LOSS: To fix all the reported error the following syntax is used:
Note:REPAIR_ALLOW_DATA_LOSS is only used if the error is unable to be resolved by REPAIR_REBUILD command. It is because the above syntax erases the data as stated in the repair command.
Following are the string used to repair the SQL Server 2008 database as shown in the figure:
There are several other methods to repair the crashed MDF file of the SQL server 2008 database. As it is advised by most of the experts that you can restore the database with the help of backup. But, other then backup the user can repair the crashed SQL Server database from the MDF file by the above-suggested solution.