Methods to Repair Corrupted MDF and LDF Files

MS SQL Server is a relational database management system that is used for storing and retrieval of data when requested by other applications. The SQL database mainly comprises of three files- MDF, NDF and LDF. The first two files from the primary and secondary database of SQL Server and the latter stores the log database of SQL server database transactions. While the data files and the log file are accessed by a common server, these files do not share the same location for their storage. Both the data files and the log file are associated with SQL server; in this blog however, we will restrict our discussion to how to repair corrupted .mdf and .ldf files.

Primary & Log Database of SQL Server

Primary database aka Main Database File (MDF) as the name suggests is the main database file of SQL Server that are associated with other files. There exists only a single MDF file for every SQL Server database, which stores data items like triggers, views, stored procedures, etc. It would not be wrong to say that MDF file is the primary element for administration of SQL database.

The LDF (Log Database File) store the entire log information of the transactions or activities carried out in the SQL Server. They play a very crucial role in the restoration of SQL server data in cases of data disaster. The size limitation of LDF is decided at the time of the database designing. Information related to records updation, record deletion, insertion of new record, etc.

Corruption in MDF and LDF Files

Maintaining the database integrity of SQL server is the top priority of the users as any mishap can lead to loss of data that at times cannot be recovered easily. There can be umpteen reasons, which can end the MDF and LDF files in a corrupted state. Some of the reasons for MDF and LDF corruption are:

1. The biggest reason behind the corruption of MDF and LDF files is the storage size. In case the data exceeds the limit of MDF and LDF file, corruption is likely to occur.
2. In case the SQL server database is modified during the DROP operation, there are great chances of MDF and LDF files corruption.
3. Hardware issues such as problem in the hard drive sector storing the SQL database, may lead to corruption of the database.
4. Virus intrusion is also an important factor when it comes to corruption of MDF and LDF files.

Manual Solutions to Repair Corrupted MDF and LDF Files

In order to repair a corrupt MDF and LDF file, DBCC CHECKDB command can be used. It recovers the data from a corrupt SQL database file that has been damaged due reason such as logical corruption. This involves running an executable code in the SQL server in order to repair corrupted MDF and LDF files.

Note: Before running the DBCC CHCKDB command, the backup of the existing database should be taken.

In case the repair process fails and the backup of the MDF and LDF files does not exist, the user will not be able to restore the database back to its original state and hence will end up in having a corrupt MDF and LDF file.

Alternate Solution to Repair Corrupted MDF and LDF Files

Regarding the drawbacks of the manual procedure to repair corrupted MDF and LDF files, third party solutions are preferred over them. One such commercial utility that can be deployed for repair corrupted MDF and LDF files is SQL Server Database Recovery Tool. The software recovers the entire data from corrupt MDF and LDF files and save it in SQL compatible scripts or directly in SQL server database.