LDF and MDF files are the components of the SQL Server database. When you create a database in SSMS, these files are automatically generated. MDF file is the primary data file that stores data and objects, whereas the log data file (transaction log file) stores all changes for recovery purposes. If the MDF and LDF files mismatch due to any reason, you may encounter various errors when accessing the database. In this article, we will discuss the methods for SQL database recovery to repair SQL Server database when these files don’t match.

Common Reasons Why MDF and LDF Files Don't Match

A mismatch between the primary data file and the transaction log file can occur due to the following reasons:

• Transaction log file is missing, renamed, or corrupted.
• Either the MDF file or LDF file was copied at different times and locations while the database was actively processing the transactions.
• MDF file is corrupted or damaged.
• The system on which both files are saved experienced an unexpected server shutdown.

How to Identify MDF and LDF Files Mismatch

Before attempting the SQL database repair, you must confirm the reason why the MDF and LDF files fail to match. For this, you can follow these ways:

1: Verify the Database State

SQL Server database is always in a specific state - ONLINE, SINGLE_USER, RECOVERY_PENDING, EMERGENCY, or SUSPECT. You can check the database state by simply looking at the status next to the database in SSMS under the Object Explorer.

Alternatively, you can run the following command to find the exact state:

SELECT name, state_desc

FROM sys.databases;

You can refer to the following table to know what each state indicates:

Note: The database state alone cannot confirm MDF and LDF mismatch. You can check the SQL Server error log.

2: Verify SQL Server Error Log

You can check the error log in SSMS to know the reason behind the MDF and LDF mismatch. For this, right-click on SQL Server logs under Management section and select View > SQL Server Log.

In the error log, look for the error messages like 9003 or 5173.

We have created a scenario and tried to run the DBCC CHECKDB command on database named “Johnson”. We have received the following errors related to LDF and MDF mismatch in the Error Log output:

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Johnson_log.ldf'. Diagnose and correct the operating system error, and retry the operation.

When we tried to create the backup of database – Johnson, the following error appears in the log:

BACKUP failed to complete the command BACKUP DATABASE Johnson. Check the backup application log for detailed messages.

3: Check and Analyze Error Messages Related to MDF and LDF

If there is a file mismatch or a missing log file, you may encounter the following errors:

1. MDF File Attaching Errors

If you attempt to attach a mismatched or corrupted database, SQL Server will immediately block the operation and displays the errors like:

• Error 5173: Occurs when the log file does not match the primary data file (MDF).
• Error 5172:  Occurs when the header of the MDF file is corrupted or unreadable.

2. Errors when Running DBCC CHECKDB Command

When you run the DBCC CHECKDB command on an inaccessible database, you will receive an error similar to this:

Msg 945, Level 14, State 2, Line 32

Database 'Johnson' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details.

3. File Activation Failure when Forcing the Database Online

When you run the ALTER DATABASE command to force the database to come online, you may experience an error like this:

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Johnson_log.ldf" may be incorrect.

Msg 5181, Level 16, State 5, Line 12

Could not restart database "Johnson". Reverting to the previous status.

Msg 5069, Level 16, State 1, Line 12

ALTER DATABASE statement failed.

Completion time: 2026-10-28T10:12:03.9149396+05:30

4. When Creating Database Backup

When you try to create a backup of the database with mismatched files, the operation will terminate abnormally with the 3013 error.

Msg 945, Level 14, State 2, Line 18

Database 'Johnson' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

Msg 3013, Level 16, State 1, Line 18

BACKUP DATABASE is terminating abnormally.

How to Repair SQL Server Database when MDF and LDF Files Don't Match

If you fail to access the database due to the primary data file and transaction log file mismatch issue, follow the given methods to fix it.

Method 1: Reconnect the Original Transaction Log

You can use this method if the matching .ldf file still exists on the system but was moved to a different location or folder path. You can check and copy the file back to the directory path where it belongs. After this, try to open/attach the database online.

Method 2: Rebuild the Missing Transaction Log

If the transaction log file is missing or corrupted, then you can try rebuilding the transaction log file by using the following command:

CREATE DATABASE Johnson ON

(FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Johnson.mdf')

FOR ATTACH_REBUILD_LOG;

GO

Method 3: Restore the MDF and LDF Files from Backup

If the server fails to read the MDF or LDF files due to corruption, then you can try restoring the database from backup. First, check the integrity of your .bak file. Next, run the RESTORE DATABASE command to restore the backup. Here’s how:

RESTORE DATABASE Johnson

FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\Johnson.bak'

WITH REPLACE,

STATS = 10;

GO

Note: The WITH REPLACE command can overwrite any existing database of the same name, causing irreversible data loss.

Method 4: Recover Data from Damaged MDF File

If the backup is not available, then you can perform SQL file repair to fix the corrupt or damaged MDF file using DBCC CHECKDB command.

First, change the database state to EMERGENCY and SINGLE_USER by running the following command:

Run ALTER DATABASE Johnson SET EMERGENCY;

GO

ALTER DATABASE Johnson SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

Next, run the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS to repair the MDF file.

DBCC CHECKDB ('Johnson', REPAIR_ALLOW_DATA_LOSS)

WITH NO_INFOMSGS, ALL_ERRORMSGS;

GO

The REPAIR_ALLOW_DATA_LOSS option can cause data loss because it repairs corruption by deleting damaged pages, indexes, or rows. Also, it may not work, if SQL server engine fails to access or open the severely corrupt database in EMERGENCY mode.

Method 5: Use a Professional SQL Repair Tool

If other repair methods fail, then you can take the help of a professional SQL repair tool, like Stellar Repair for MS SQL. This tool is designed for SQL database recovery software and can repair corrupt MDF file. Once the database file is repaired, you can save it in a New Database or Live Database and various other file formats, like XLS, HTML, CSV, etc. You can use the tool to fix the errors and issues that are related to MDF file corruption and page corruption. Also, it helps you perform specific object recovery with complete integrity.

If you’ve a small-sized MDF file (up to 500 MB), you can use Stellar’s online SQL repair service to repair it for free. It doesn’t require SQL Server and installation of any additional software.

Conclusion

To open or attach a SQL database, you need both the transaction log file and the MDF file. If the server fails to read either of these files, it can lead to errors and database unavailability. Above, we have discussed how to repair a SQL database if you fail to access it due to MDF and LDF mismatch. You can rebuild the transaction log file if it is corrupted or repair the MDF file using any suitable method described above.