Restore Database from MDF File – Know How to Do?

MDF file is the primary storage file of SQL Server in which all physical data are stored. SQL Server also uses some other files LDF (transaction log file), NDF (secondary storage file). Now, we talk about how to restore the database from the MDF file without the LDF file. There are some situations where we need to restore data from MDF files such as if we migrate SQL Server if we refuse to use old SQL servers, etc.

Now the question is how to attach MDF file to the SQL server? – There are two ways available to perform this task, in this post we cover both methods in a detailed manner. So, let’s start-

How to Restore Database From MDF File in SQL Server?

Here we will describe two methods to attach or restore MDF files in SQL Server –

1. By using SQL Server Management Studio
2. By using T-SQL

Restore MDF File in SQL Server Without LDF by using SQL Server Managment Studio

Follow all the given steps to successfully attach the .mdf file in SQL Server.

1. Open SQL Server Managment Studio.



2. In Object Explorer, right-click on Database & then select attach.



3. Now Attach Database window open, click on Add button.



4. Browse location of MDF file, then select the file and click on OK button.



5. Now, you can see the database detail, to attach MDF file without LDF file you have select LDF file and then click on Remove button, then click OK.

SQL Server will create an LDF file when the MDF file is attached successfully. Now, you have to check the database in the database folder.

Attach or Restore MDF File in SQL Server with T-SQL Script

To attach MDF file in SQL Server by using T-SQL you have run the following T-SQL script –

CREATE DATABASE testdatabase ON
(FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\testdatabase.mdf')
FOR ATTACH_REBUILD_LOG
GO

By using the above solutions you can easily restore the database from MDF file in SQL Server, but sometimes due to some errors users are not able to attach MDF in SQL Server. Some errors are discussed below –

Consider the user’s queries –

1 – “For some reason, I have to restore the database from MDF file only, this MDF file is stored in my machine. When I try to attach the .mdf file into SQL Server by using T-SQL I got an error 5123. So what I have to do in this situation”

Solution – You are getting a 5123 error message from SQL Server because there are permission issues in your MDF or database file. Cause of these issues you are not able to attach MDF file in SQL Server or you cannot restore the database from MDF file in SQL Server. To solve this error you have to change the permission as the owner of MDF file & then attach the file to the SQL server by following the above-given solutions.

2 – “I have used T-SQL script to restore database from MDF file in SQL server, but when I execute the command SQL Server display a 5172 error (the header for file mdf is not a valid database file header. The file size property incorrect)” so how do I attach .mdf in SQL Server.”

Solution – This error occurs when header information of the MDF file gets corrupted and the database becomes inaccessible, so to resolve these issues you have to repair the MDF file. To remove error 5172 you need to use the Tool.

Download the Tool –

After repairing the MDF file by using the software you can attach MDF in SQL Server. This Software allows the user to recover deleted SQL database objects as well as deleted SQL table records. The user can easily repair the Primary as well as Secondary files by using this software. Users can also save the recovered data as SQL Server Compatible script or CSV File. The tool provides an option to export the data into a live SQL Server present in the same machine or a server network. Moreover, This software supports Microsoft SQL Server 2019 / 2017 / 2016 / 2014 / 2012 and below version.

Follow the below-mentioned steps to Restore database from MDF file only

1. Install and Run the Application and Click on Open.

2. Browse the MDF file from your system. Next Select the SQL Server version and the Advanced Scan Mode. ( The user can also check the recover deleted objects option if required.)

3. Preview the SQL database objects SQL Table, stored procedure, functions, views, indexes, etc. ( This software shows the deleted SQL database objects and table records in red color.)

4. Choose an Export option and fill in the required details to restore data from MDF file.

5. Now, choose the database object you want to save and then click on Export button.

Conclusion

By using SQL Server Management Studio, T-SQL script you can restore database from MDF file without the need of LDF file. In case, you get any error message from SQL Server while attaching the MDF file, then it is suggested to use the automated solution to attach .mdf in SQL Server.