Two Ways to Restore Database from MDF File in SQL Server

DBA SQL Database Restoration SQL Tips

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.

  1. First of all detach the database. If you are trying to attach the database which is not detach you will get an error
  2. Maintain MDF File and LDF file in a specific folder before going to attach
  3. Locate MDF File and LDF File in the same location

There are two ways to restore database from MDF file in SQL Server:

  1. With the help of SQL Server Management Studio
  2. 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

  1. Login to SQL Server database engine
  2. Go to New Query Window and use Create database with Attach close
    ON (FILENAME = 'C:SQLServerMyDatabase_Data.mdf'), 
    (FILENAME = 'C:SQLServer MyDatabase _Log.ldf') 

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

  1. 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.

  2. 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.

  3. Pingback: Towing
  4. Pingback: 바카라사이트
  5. Pingback: engagement rings
  6. Pingback: w88 thailand
  7. Pingback: Website
  8. Pingback: Darknet Drogen
  9. Pingback: Empire Market
  10. Pingback: paykasa
  11. Pingback: saranapoker
  12. Pingback: designs 99
  13. Pingback: santali dong video
  14. Pingback: sehat
  15. Pingback: ignou mapc project
  16. Pingback: swiss watches
  17. Pingback: unreal
  18. Pingback:
  19. Pingback: link vao 12 bet
  20. Pingback: Kate

Leave a Reply