Thursday, July 27, 2017

Two Ways to Restore Database from MDF File in SQL Server

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:\SQLServer\MyDatabase_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.

4 comments:

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

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

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

    ReplyDelete
  4. 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.

    ReplyDelete