Ways to Restore Database from MDF File
In todays decade Relational database Management system became the necessity of the organizations whether they are small level or large level. In such context SQL Server is the best and successful RDBMS which provides highly integrated services to manipulate the SQL database over the Server. As we talk about the SQL Server database the Server provides the primary storage call MDF File, NDF file also known as secondary file, LDF log transaction file. Well, in this section we will discuss about how to restore LDF and MDF files to a database so lets make some prerequisites perform tasks without any trouble.
- First of all detach the database. If you are trying to attach the database which is not detach you will get an error
- Maintain MDF File and LDF file in a specific folder before going to attach
- Locate MDF File and LDF File in the same location
There are two ways to restore database from MDF file in SQL Server:
- With the help of SQL Server Management Studio
- Using T-SQL
1. Through SQL Server Management Studio follow some steps:
- Open SSMS and go to object explorer of the database engine instance
- Then make a right click on the database and select Attach option of the list button
- Windows appers, then click on Add button
- Select the .mdf file and click on OK Button
- Validate the selected files and go to OK Button
Now check the attached database
2. On using T-SQL
- Login to SQL Server database engine
- Go to New Query Window and use Create database with Attach close
CREATE DATABASE MyDatabase ON (FILENAME = 'C:SQLServerMyDatabase_Data.mdf'), (FILENAME = 'C:SQLServer MyDatabase _Log.ldf') FOR ATTACH;
In the above section, we learned how to restore LDF and MDF files to a database. It is highly recommended that you have to detach the MDF file before attaching a new one. This is a very helpful method to restore database from MDF file in SQL Server. The second method performs same task using T- SQL, for the users who needs to attach MDF file through SQL script. In case, if the transaction log file is missing then you can still attach MDF file in SQL Server without LDF file and if the MDF file is not accessible or corrupted then you can repair MDF file using third party SQL Recovery tools.
31 thoughts on “Two Ways to Restore Database from MDF File in SQL Server”
What a helpful post for me right now! Thank you for sharing!
It's lucky for me to see this post. That's what I'm looking for. Thanks for sharing!
I have been looking for that for a long time! Thanks very much
Keep up the good work , I read few posts on this web site and I conceive that your blog is very interesting and has sets of fantastic information.
Hi, I have SQL Server 2012 on my production where i have made a logical isolation of database on server drives. Now i want to restore those isolated database on another server, would like to know i should be doing that, though i have taken the back-up (not sure if it is coming out full back-up) but while restoring it is throwing some errors. Any help would really be grateful.