How to Restore Database from MDF File in SQL Server?

What Exactly Is an MDF File?

A Master Database File or MDF is the primary data file in a Microsoft SQL Server database. It stores all your essential database components, including:

• 📑 Tables, views, and indexes
• 📜 Stored procedures and triggers
• 👤 Constraints and user data

SQL Server will also generate a secondary LDF or Log Database File along with the MDF file, which tracks all transactions and changes made to the database. This helps ensure that your data is secure and recoverable in case of failures.

As you would’ve guessed, the MDF file is critical when you’re restoring a database. In most cases, it works alongside the LDF file, but SQL Server allows restoring an MDF file even without its log file—with certain limitations. Let’s see how to go about it.

Prerequisites Before Restoring an MDF File

Before you try and restore the database from an MDF file in SQL Server, keep these requirements in mind.

1. Your database must be detached: If the MDF file belongs to an active database, detach it first to help avoid conflicts.
2. Ensure MDF and LDF files are in the same location: If the LDF file is available, keep it in the same folder as the MDF file to simplify the restoration.
3. Run SSMS as an Administrator: This will help prevent permission-related holdups.
4. Check SQL Server version compatibility: Restoring an MDF file from a newer SQL Server version to an older one may not work, so it’s best to ensure they’re compatible.

With these prerequisites taken care of, let’s see how exactly we can proceed to use SSMS or T-SQL to restore the database.

Method 1: Restore Database From an MDF File Using SQL Server Management Studio (SSMS)

SSMS provides you with a graphical interface to attach an MDF file and restore the database. Here’s how to attempt it.

1. Open SSMS and connect to your SQL Server instance.
   
2. In Object Explorer, right-click on Databases and select Attach…
3. In the Attach Databases window, click Add and browse to select your .mdf file.
   
4. Click OK, and SSMS will attempt to attach the database.
   
5. Refresh the database list in Object Explorer to verify that your database has been restored.

   [⚠️ Note: If you get SQL error 5123 (Access Denied) while attaching your MDF file, it’s probably due to missing permissions. To fix this, right-click the MDF file, go to Properties>Security, and grant Full Control to the SQL Server service account. Alternatively, run SSMS as an Administrator.]

   Method 2: Restore Database From an MDF File Using Transact-SQL (T-SQL) Query

   If you prefer a command-line approach, you can attach an MDF file using a T-SQL query.

   1. Open SSMS and connect to your SQL Server instance.
   2. Click New Query and enter the following SQL command:
      CREATE DATABASE MyDatabase
      ON (FILENAME = ‘C:\Path\To\Your\File.mdf’)
      FOR ATTACH;
   3. Replace “C:\Path\To\Your\File.mdf” with the actual path to your MDF file.
   4. Click Execute to run the query. If successful, your database will now be restored and available in SSMS.
   5. In case your MDF file is missing its log file (LDF), use this query to rebuild the log file:
      CREATE DATABASE MyDatabase
      ON (FILENAME = ‘C:\Path\To\Your\File.mdf’)
      FOR ATTACH_REBUILD_LOG;

   This method forces the SQL Server to generate a new transaction log, allowing you to restore the database even if you don’t have the original LDF file.

   What to Do if the MDF File Fails to Restore?

   If you can’t attach the MDF file, the file might be corrupt or incompatible with your SQL Server version. Here’s some fixes you can try.

   1. Check file permissions

   Ensure that Full Control is granted to the SQL Server service account on the MDF file. Right-click the MDF file, go to Properties>Security, and add the correct SQL Server user.

   2. Verify MDF file integrity 

   Run DBCC CHECKDB to check for corruption using this command:
   DBCC CHECKDB (‘YourDatabase’) WITH NO_INFOMSGS, ALL_ERRORMSGS;

   If errors appear, the MDF file is likely damaged.

   3. Ensure SQL Server compatibility

   If the MDF file was created on a newer SQL Server version, it won’t attach to an older version.
   Check the database compatibility using:
   SELECT compatibility_level FROM sys.databases WHERE name = ‘YourDatabase’;

   You might need to upgrade your SQL Server to restore the database.

   Common MDF File Restore Errors and Fixes

   Restoring the database with the MDF file can come with some hiccups. You might encounter errors due to missing log files, permission issues, or file corruption. We’ve put together a list of common errors and quick fixes.

   1. Error 5123: “Access Denied”

   This error occurs when SQL Server doesn’t have the necessary permissions to attach the MDF file.

   ✅ Fix: Right-click the MDF file, go to Properties>Security, and ensure the SQL Server service account has Full Control. Then, run SSMS as Administrator and try again.

   2. Error 5171: “The MDF File is Not a Primary Database File”

   This happens when your MDF file is corrupted or incompatible with your SQL Server version.

   ✅ Fix: The first thing to do is to check the MDF file version and ensure that it matches your SQL Server version. After that, the simplest solution is to use an existing backup to restore your database, but this isn’t always possible. If a backup isn’t available, try attaching the database using the sp_attach_db command.

   You can also try restoring the database using DBCC CHECKDB, but with caution because this command may result in data loss. Use it only if the data is not of critical importance to you. You can use it by typing:
   DBCC CHECKDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS)

   A much better solution, one that doesn’t risk data loss, is to opt for something that fixes the error automatically. An SQL database repair tool like the ones discussed in this Reddit thread can help.

   3. Error 1813: “Could Not Open the New Database”

   This occurs when your MDF file references a missing LDF log file.

   ✅ Fix: Attach the MDF file without the log file using:
   CREATE DATABASE db_name ON (FILENAME=’C:\path\to\file.mdf’) FOR ATTACH_REBUILD_LOG;

   4. Error 823/824: “I/O Error Detected”

   This indicates a hardware or disk issue affecting your MDF file.

   ✅ Fix: Run CHKDSK to check for disk errors. Otherwise, try moving the MDF file to a different storage drive and try again. If the file is still unreadable, try restoring it from a backup.

   Alternatively, you can use a professional SQL database repair tool like Stellar Repair for MS SQL. This tool acts like a Swiss army knife for all your SQL Server-related problems and can be used to fix severely corrupted MDF files while still preserving your data’s integrity. It supports all SQL Server versions; restores your tables, stored procedures, and indexes; and allows you to preview recoverable data before saving. Unlike manual fixes, the Stellar Repair for My SQL tool minimizes any data loss risks and simplifies your recovery process.

   Conclusion

   Restoring a database from an MDF file in SQL Server is straightforward enough if the file is intact and properly detached. You can attach the MDF file using SSMS or a T-SQL query, depending on your preference between a GUI and a CLI interface. However, if the MDF file has other issues like a missing LDF file, or if it’s corrupted or incompatible with your SQL Server version, this restore process can fail.

   Remember, to avoid data loss, you must always ensure that you have a valid backup before performing any restoration. If the MDF file is corrupt, use DBCC CHECKDB commands for some minor DIY repairs or rely on a specialized tool like Stellar Repair for MS SQL.