How to Restore MDF File to SQL Server: Resolved

Problems

Most of the times when a user tries to open MDF files, they get the following error message on their machine. It creates an issue in the workflow for the management of Server database and causes troubles to restore MDF file to SQL Server.

Solution to recover MDF files

MDF files are the MS SQL Server database files whereas LDF files are related log files. However, you cannot open them. Instead of this, you have to attach them. Once it is attached, you will see the database in object explorer of SSMS. This way you can restore database in SQL Server using MDF file. In case the MDF database file got corrupted and the user is unable to attach the files, then it is necessary to Repair Corrupt MDF File before attaching.

Procedure to Attach MDF file in SQL Server 2010

There are numerous of a couple of different methods to attach .mdf file in SQL server database. An easy process is discussed below through SSMS (SQL Server Management Studio). Then, the other way is by using T-SQL queries.

Steps to restore database in SQL Server using MDF file with T-SQL queries:

• Launch SSMS in your machine
• Connect it to your MS SQL Server Instance.
• Now, right-click on databases in object explorer >> click attach.
• In the window of Attach Databases, simply click on Add button.
• Traverse to the directory having the .mdf and .ldf files.
• Choose MDF file >> OK.
• Again, click on Ok to attach database.

Now, you can see the database appearing in the Databases node. If you are not able to view it then, press F5. Explore the data by using SSMS. The similar steps are renowned in MSDN here.

However, in T-SQL there are two processes to do the similar thing as a first step to restore database in SQL Server 2010 using MDF file. First, you can utilize sp_attach_db-stored process. Otherwise, one can utilize CREATE DATABASE command with FOR ATTACH argument. If SQL Server Database is attached successfully then, stop understanding and use it. However, if you see some errors or unexpected behaviors while trying to perform restore MDF file to SQL Server then, you can freely try to share troubleshooting suggestions.

First, you may receive a generic error as mentioned below:

• Access Denied

So as to restore MDF file, you have to open it first. While trying to open the file, the most common problem is the Access Denied error which comes due to several reasons. You do not have the need to access to MDF or LDF files. It can occur if one gets the file from the other person. When they remove database file then, file authorizations are transformed to provide that only those users have full control. In addition, even if you get that file with complete control for Administrators group, it may not be sufficient to recover SQL Server database from MDF file. Evoke in MS Windows 7/Vista that all those permissions are frequently realized when the one is running the application as Administrator.

Therefore, there are various choices to resolve it:

1. The simplest solution is to close SSMS. After that, run it as Administrator. Execute the attach as Administrator. It is likely going to work.

2. Another solution is to openly grant complete control to MDF and LDF files to the user account. This can be ended by simply right clicking all these files, choosing Properties >> altering Security tab.

3. The final solution is to copy files to the default directory for other database files. To get whats that, you can utilize sp_helpfile process in SSMS. On the system it is:

   C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA.

Copy files to this directory, you will automatically acquire permissions applied, which will permit to successfully attach the .mdf file in SQL server 2010.



• Access Denied Variation 2

This error while trying to attach MDF file means the file is opened already exclusively by other application. The most likely reason is that the database file is attached already to an instance of MS SQL Server. Now, double-check your list of the databases to get whether it is in the list. It is also likely for applications to utilize MDF files directly with a feature known as User Instances. If an application is utilizing .mdf file this way then, it would close it before you might attach with that database file and then you can easily restore the database from MDF file in SQL server.



• Read-only Database

It is not much an error or an undesired result. When the one load database, it is in the state of read-only. You can say by gray shading of the database icon.

The most likely cause when we attach MDF file to SQL server 2010 is that it is in read-only mode. Simply right-click on MDF and LDF files >> choose properties >> uncheck check box of Read-only.



• Unable to Downgrade

This error comes when one tries to attach MDF file to SQL Server 2010 of higher edition to Server Instance of lower edition.

It is never supported. You can simply attach database of the previous edition of SQL Server to latest edition in order to restore MDF file, but you cannot go other direction.



The above-mentioned methods can even be used to restore and recover MDF file for SQL Server 2016/2014//2012/2008/20072005.

Summing Up:

This discussion is just covering the scenarios of how to restore MDF file to SQL Server. It makes easy for them to restore their complete data in exact form and along with this; various errors are also discussed that makes easy for them to overcome it.