How to Attach MDF and LDF Files in SQL Server

What are MDF and LDF files?

MDF or Master Database File is the primary database file of the SQL server that contains all the major information related to the database. It contains indexes, tables, queries, constraints, triggers, etc., of your database. It is the most useful file during database recovery as it helps you restore complete SQL database items.

Another significant database file is the LDF file or Log Database File. It is a log file that contains the history of every modification made on the SQL Server. This file type is of utmost importance during the database recovery procedure because it helps you to maintain the data integrity and hierarchy of your complete database.

Whenever you want to add a database file to the SQL server, you will require both of these files. Now, you have learned what MDF and LDF files are. It is time to see how to attach SQL database files to SQL server.

Two Different Methods to Attach MDF and LDF Files in SQL Server

There are two prominent methods to attach MDF and LDF files in SQL servers. Both these methods are simple and secure. In the first method, you use the SSMS SQL Server Management Studio to add your SQL database file. Alternatively, you can use the T-SQL Query to attach a database file to the SQL server. Let us check How to attach database in SQL server via two different methods.

#1. Attach MDF File via SSMS (SQL Server Management Studio)

• Open the SSMS and connect it to the server.
• After that, choose the required database and right-click on it. Select Attach.
• In the Attach Database wizard, click on the Add button.
• Now, select the desired MDF file in your system and press the OK button.

Note: The default location is defined as C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA

• After that, you will see the selected database files (.mdf and .ldf files) in the ‘database to attach’ section.
• It will add the selected database to the SQL server.

After performing the above steps, you will be able to attach MDF and LDF files in SQL server.

#2. Attach MDF File via T-SQL Query

Two T-SQL queries are available to attach the desired MDF and LDF file to the SQL server: ‘CREATE DATABASE. WITH ATTACH’ and ‘exec sp_attach_db’ commands.

• Open SSMS and connect it to the server.
• After that, click on the New Query button to give the required commands.
• Type the below ‘CREATE DATABASE’ query and click on the Execute button to add the MDF and LDF files.

USE [master] GO create database <DatabaseName> ON (name=’LogicalName of the Data file’, FileName=’Data File Name’), (name=’LogicalName of the Log file’, FileName=’Log File Name’) FOR ATTACH;

• Alternatively, you can use the exec sp_attach_db command as mentioned below:

USE [master] GO CREATE DATABASE [database_name] ON ( FILENAME = N’C:\MSSQL\SQLData\databasefilename.mdf’ ), ( FILENAME = N’C:\MSSQL\SQLLog\databasefilename_log.ldf’ )  FOR ATTACH GO

• After that, execute the below exec sp_attach_db query.

USE [master] GO EXEC sp_attach_db @dbname = N’database_name’,       @filename1 = ‘C:\MSSQL\SQLData\databasefilename.mdf’,       @filename2 = ‘C:\MSSQL\SQLLog\databasefilename_log.ldf’;

After performing the above procedure, you will be able to attach MDF and LDF files in SQL server. This procedure is feasible for every user who is using the SSMS. There are a few common error codes you might encounter when you attach the database files.

Common Errors and their Troubleshooting

Now, you have learned how to attach SQL DB file in SQL server. However, while attaching MDF and LDF files, you might encounter a few errors. Below we will describe these common issues and their troubleshooting methods.

#1. Access denied due to lack of permission – This problem occurs when the SSMS is not able to access data from the selected MDF and LDF files due to restrictions over permissions.

Solution – Run the SQL Server Management Studio as administrator.

Solution 2 – Enable full control access to the desired MDF and LDF files.

Solution 3 – Move the MDF and LDF files to the default location of the database files.

#2. Database file version not supported – This error arises when you try to attach an MDF file associated with a higher version of SQL Server to an SQL server of a lower version.

Solution – Upgrade your SQL server to the latest version.

How to Load MDF and LDF Files in SQL Server When They are Corrupted?

You cannot attach a corrupted database file to the SQL server. It becomes impossible to open MDF files from these files and attach MDF and LDF files in SQL server. Therefore, you need to repair the MDF file so that you can attach it to the SQL server. But repairing MDF files is a tedious task through manual methods. It is not suitable for every user because it has many limitations. Therefore, we suggest a more advanced professional solution.

DRS SQL Database Recovery Tool is the most recommended software to recover the maximum possible data from corrupted MDF and LDF files. It recovers maximum data like schemas, tables, queries, indexes, triggers, etc., from the corrupted MDF file. Moreover, the application provides various advanced features to simplify file recovery. Also, this application supports all significant versions of SQL database files.

Conclusion

I hope you have learned how to attach MDF and LDF files in SQL Server. MDF and LDF files are the database files required for restoring your database. You can manually add these database files to the server via SSMS. Also, you may encounter a few error codes. We explain these errors along with their troubleshooting. At last, we suggest professional software that helps you to repair corrupted MDF and LDF files.