2 Best Methods to Attach MDF file to SQL Server

Introduction to Attach MDF file to SQL Server

Microsoft SQL(Structured Query Language) server is a Relational Database Management System or RDBMS developed or managed by Microsoft. It is a platform-independent programming language.

SQL is used to interacting with the relational database that primarily stores and retrieves data requests by the users. And also provide a platform to attach MDF file to SQL Server.

SQL tied to T-SQL(Transact SQL). It provides us features of defining variables, exception handles and is used to attach any file in the SQL server for running the query of the SQL database.

Moreover, we know about how to attach MDF file in SQL Server 2008 r2 facing an error. Now have a look at the MDF File.

What is the MDF file?

MDF stands for Main Database File. SQL Server database stores data into MDF. It contains secondary data, primary data, and transaction log files. MDF files contain columns, rows, indexes, tables, data added by an application, and essential information of the database.

After, Getting knowledge about the MDF file. Now, understand the solutions to attach MDF files to SQL.

Methods to Attach MDF file to SQL Server

1. Attach MDF files to SQL using SSMS (SQL Server Management Studio)
2. Attach MDF files to SQL using T-SQL Query (Transact SQL)

Solution 1: Attach MDF files to SQL Server using SSMS

Follow these steps to attach the MDF files to the SQL database:

• Open SSMS (SQL Server Management Studio)
• Connect SSMS to SQL Database server.
• Right-click on the Database to choose the option Attach.
• Click on Attach

• On the Attach Database, choose the option

• Then, choose the directory from where you want to select MDF files. By default, it opens the folder where the system stores its Database.
• After that, select MDF files and review them by Database to attach and click

• Then, again Click OK to attach the MDF file to SQL Database Server.

By performing the above step, you can easily attach the MDF file to SQL Server. If unable to do so, then move to the next solution.

Solution 2: Attach MDF files to SQL Server using T-SQL

There are mainly two ways to attach MDF files to the SQL database:

1. i) CREATE DATABASE
2. ii) dir sp_attach_db

The code to CREATE DATABASE for attachment of MDF files to SQL Server using T-SQL.

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;

The code to “dir sp_attach_db” for attachment of MDF files to SQL Server using T-SQL.

USE [master]
GO
EXEC sp_attach_db @dbname = N'DatabaseName',  
    @filename1 = '<Location of the database file>',  
    @filename2 = '<Location of the Log file>';

By performing the above code, the user can attach MDF files to the SQL server database.

Error while Attach MDF Files to SQL Server

When you attach MDF files to the SQL Database, you might face the following errors:

• Access should be Denied, without Permission

Users face errors like access denied, Not able to open database files, Operating system errors. All these errors come because of a lack of permission to access database files. Using the below steps users can fix the error:

• Open SQL server and run it as an Administrator, and attach the database file.
• Grant full access to the MDF database file. To apply, right-click on the database file, click on security and grant full control to the user.
• The above two steps are not working. Then copy the database files and paste them to the default location the users should get permissions automatically.

• Version error.

  When the user attaches the database file to the SQL server, facing the error of the versions.

  These are some troubleshooting errors users may have faced while attaching MDF files to SQL Server Database. For avoiding these errors, users have to use the Third-Party Technique.

  Repair MDF file to Attach SQL Server

  If the MDF file is still not attached with SQL Server from the mentioned solutions. Then there is a chance of corruption in the MDF file.

  To restore the corrupt MDF file users have to use Microsoft SQL Repair Tool. This tool repairs the MDF file and then we use the MDF file to attach with SQL Server using the above-mentioned methods.

• Summary

  This article is based on how to attach an MDF file to SQL Server and what is MDF file. Moreover, we understand multiple methods to Attach the MDF file to SQL Database Server.

  After that, we cover some basic errors that we can face while attaching MDF files. We understand about the Third Party Tool that helps to recover the corrupt MDF file.

•