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