Two Ways to Restore Database from MDF File in SQL Server

Ways to Restore Database from MDF File

Introduction
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
    CREATE DATABASE MyDatabase 
    ON (FILENAME = 'C:SQLServerMyDatabase_Data.mdf'), 
    (FILENAME = 'C:SQLServer MyDatabase _Log.ldf') 
    FOR ATTACH; 

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

This entry was posted in DBA, SQL Database Restoration, SQL Tips. Bookmark the permalink.

31 Responses to Two Ways to Restore Database from MDF File in SQL Server

  1. What a helpful post for me right now! Thank you for sharing!

  2. Elia says:

    It's lucky for me to see this post. That's what I'm looking for. Thanks for sharing!

  3. Elen says:

    I have been looking for that for a long time! Thanks very much

  4. Getappvn says:

    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.

  5. Brydams says:

    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.

  6. Pingback: Towing

  7. Pingback: didi promotion code

  8. Pingback: 바카라사이트

  9. Pingback: engagement rings

  10. Pingback: http://antykorozja-poznan.pl/gazeta-wyborcza-katowice-205-2011-1417.php

  11. Pingback: w88 thailand

  12. Pingback: https://biznesblog.biz.pl/napisac-przedsadowe-wezwanie-zaplaty/

  13. Pingback: https://internetnews.net.pl/forum/nowe-technologie-f7/poznam-opinie-o-funkymedia-t62.html

  14. Pingback: Brunette Blonde Domination Foot Fetish

  15. Pingback: Website

  16. Pingback: Darknet Drogen

  17. Pingback: Empire Market

  18. Pingback: Tochka Market Exit Scam

  19. Pingback: paykasa

  20. Pingback: southern mississippi vs florida atlantic live

  21. Pingback: saranapoker

  22. Pingback: designs 99

  23. Pingback: santali dong video

  24. Pingback: sehat

  25. Pingback: ignou mapc project

  26. Pingback: swiss watches

  27. Pingback: unreal

  28. Pingback: www.cbdicals.com

  29. Pingback: link vao 12 bet

  30. Pingback: Kate

  31. Pingback: http://www.alp-city.org

Leave a Reply