How to Restore Database from MDF File in SQL Server?

Summary: In SQL Server, you can use the option of attaching an MDF file to restore the database. This blog discusses the ways to attach an MDF file to restore the database in an SQL server. It also suggests using a SQL recovery tool to regain access to the database when the MDF file is corrupt.

SQL Server stores all the information of a database in the primary data file (MDF). The server also uses a log file (LDF) that holds the information needed to recover a database. Situations may arise when you need to restore your database from an MDF file (with or without an LDF file) in SQL Server. These situations include:

• Migrating databases from an old SQL Server to a new server.
• Accessing client database on another system.

इस पोस्ट को हिंदी में पढ़ने के लिए यहां क्लिक करें

What is an MDF file?

An MDF (Master Data File) file is the primary data file of a SQL Server database. It stores all the user and system data, including tables, views, indexes, stored procedures, triggers, and constraints. The MDF file extension represents this crucial component of a SQL Server database.

You can think of an MDF file as the foundation or backbone of your database since it contains everything needed to restore or rebuild it entirely. It’s essential to back up your MDF files regularly to ensure that you can recover your database in case of corruption issues or disaster events like a hard drive failure.

In addition to the primary data file (MDF), there are also secondary files known as LDF (Log Data Files). These files contain transaction logs that record every change made within a particular time frame. Both MDF and LDF files work together and play critical roles in ensuring proper functioning and maintaining high-performance levels for SQL Server databases.

Understanding what an MDF file is will help you appreciate its importance when restoring databases from backups or dealing with corrupt databases.

How to Attach an MDF File in SQL Server?

Before discussing the ways to attach an MDF file restore database, there are some prerequisites you need to meet. Prerequisites

• Detach the database. This is because attempting to attach a database that is not detached will throw an error.
• Ensure that the MDF file and LDF files are stored in the same folder/location.
• Ensure that SSMS is installed on your system.

Method- MDF File Restore Dat in SQL Server

Use any of these two methods to restore a database from an MDF file:

Method 1 – Use SQL Server Management Studio (SSMS)

Follow these steps to attach the primary database file using SSMS:

• Connect to a SQL Server instance.
• From the Object Explorer pane, right-click on Databases and click the Attach option.

• In the dialog box that appears, click the Add button

• Choose the MDF file you want to restore and then press the OK button.

• The data (.mdf) and log (.ldf) files will be added in the ‘Attach Database’ window. Validate the selected files and click OK.
• Refresh the databases and you can see the database in Object Explorer.

Note: You may receive SQL error 5123 during the attach process. This may happen if you detach a database with several logins or if you do not have sufficient permissions required to attach the MDF file. To fix the 5123 error, you will need to grant login permission and full control over the MDF file, and then try to attach the database. Or else, run SSMS as Administrator. For this, click on the Windows Start menu and search for SSMS. Right-click on SSMS and select ‘Run as Administrator.’

Also read, How to Fix Microsoft SQL Server Restore Error 3183?

Method 2 – Use Transact-SQL (T-SQL) Query

You can also restore the database from the MDF file by running a T-SQL query. To do so, perform these steps:

• Open SSMS and connect to an instance of your SQL Server.
• Click the New Query option from the toolbar.

CREATE DATABASE TestDB_Suspect ON (FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\TestDB_Suspect.mdf’), (FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\TestDB_Suspect_log.LDF’) FOR ATTACH;

Note: Replace the ‘TestDB_Suspect’ with the name of the MDF file you want to restore. Also, change the data and log filename in the path mentioned above. If you want to attach a .mdf file without a .ldf file, then run the query below to attach the database.

CREATE DATABASE TestDB_Suspect ON (FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\TestDB_Suspect.mdf’) FOR ATTACH_REBUILD_LOG;

Executing the above T-SQL query will attach the MDF file to the SQL Server and restore the database.

Note: The attach process may fail with an error message stating that the .mdf file is read-only. In that case, go to the location where the file is stored, open its Properties, and ensure that the ‘Read-only’ checkbox is marked as unchecked.

Another issue could be compatibility problems between the version of SQL Server used to create the backup file and the one being used for restoration. Make sure to check if both versions are compatible before starting the restoration process.  Double-checking all details related to backups and restorations before beginning can save time and prevent unwanted mistakes from occurring.

Related Blog Post: How to Restore Database in SQL Server?

What to do if Restoring the MDF File Fails?

The database restore process will fail if the MDF file you are trying to attach is damaged or corrupted. In that case, you can restore the database from the last known valid backup. If the backup is not available, use a specialized SQL repair tool to avoid further downtime associated with manually troubleshooting the corrupted database file.

Activate the full version of Stellar Repair for MS SQL – read on how to get the activation key of Stellar Repair for MS SQL.

Stellar Repair for MS SQL is one such tool you can use to repair corrupt MDF files with up to 8X speed. The software helps retrieve all the database objects, including tables, deleted records, stored procedures, keys, etc. while maintaining data integrity. For information about repairing MDF files using Stellar Repair for MS SQL software, refer to this link.

Conclusion

In this post, we discussed the methods to attach MDF (and LDF) files to restore an SQL database. You can attach the MDF file using SSMS or T-SQL query. If the restore process fails due to corruption in the database file, consider using Stellar Repair for MS SQL to fix the corrupted MDF file.

With these troubleshooting tips in mind along with our step-by-step guide on how to restore a database from an MDF file in SQL Server, you’ll be able to restore your valuable data without losing sleep over it!