Home » Tech Query » How to Restore Database from MDF File in SQL Server 2014, 2012, 2008

How to Restore Database from MDF File in SQL Server 2014, 2012, 2008

Mariya Beckham ~ Published: July 20th, 2017 ~ Tech Query ~ 5 Minutes Reading

The desktop database applications like Microsoft SQL Server provides many profits to its users because it keeps all the records in a speedy, flexible and safe manner of the professional users. The major objective behind the creation of such applications is to provide e-commerce software, data mining, and large transactions on the large scale. SQL server is most widely used on platforms of the company for fulfilling the purpose of data analysis, processing of the components, and the integrated data. Having so many benefits of using the server have some problems as well. There can be chances that the data inside the SQL Server may get damaged or corrupted. Thus, users can’t afford their so much important data getting corrupted so, in this article, we are going to discuss the working solutions to restore SQL Server database from MDF file.

The database of the SQL server comprises of the three file types:
1. Primary Database File (MDF file)
2. Secondary Database File (NDF file)
3. Log file (LDF file)

Among the three files format i.e., MDF, LDF, and NDF, one file present in the database is the primary file, which consists of the every essential information related to the database and points out towards the other files mentioned in it. The user data and objects are stored in this file, having .mdf file extension. Every single database consists of a primary data file that is kept separate. Another data file among these files is the Log file. This file format stores the record of activities performed within the database. The file carries relevant information to be accessed at the time when one recovers the SQL Server database from MDF file. The SQL server must contain at least one database file for maintaining the log files. In this section, one is going to clear their concepts regarding SQL, figure out the need for the same and then, discuss several methods to solve queries regarding how to restore MDF file in SQL Server 2014, 2012, 2008.

Necessity to Recover SQL Database from MDF File

A user does not have the SQL Server backup, which is sometimes a valid fact. An MDF can be saved only with the help of the Windows backup. In this case, if the .mdf file is not damaged or corrupted then, one will be proficient enough to recover the database at an ease from the SQL server.

Methodologies to Restore SQL Server Database from MDF File

Here the article will be discussing two methods through which one can repair or restore the database of the SQL server from the MDF file:

  • Hit the Start button. Shift the cursor towards All programs then, click on the SQL Server for generating preview of the database program.
  • Now, move to SQL Server Management Studio application and then, click on the tab i.e. Connect to Server.
  • Select the server present on the Web, set an Authentication, and hit the Connect button.

Repair the database of SQL Server along With SSMS

The following points mentioned below will guide the users to understand how to restore MDF file in SQL Server 2008, 2012, 2014 with the GUI:

  • Press the Attach option, after hitting on the Database
  • Click on Add tab to find database files location
  • Type the full name of the .mdf file. For example: C:\data files\users_data.mdf.
  • Opt for OK button, once the user has completed with all functioning from the SQL Server Management Studio and load the MDF files.

Restore SQL Server Database from MDF File with the SQL Transact Script

Following steps are mentioned to perform the recovery process using a script of Transact SQL:

  • Opt for the New Query in the Server Management Studio toolbar
  • Make a database with the same name through following code:
    Use master
    GO
    CREATE DATABASE userdata ON (NAME = userdata_dat, FILENAME = ‘e:\Program Files\Microsoft SQL Server\Data\userdata.mdf’, SIZE = 10, MAXSIZE = 500, FILEGROWTH = 10) LOG ON (NAME = userdata_log, FILENAME = ‘e:\Program Files\Microsoft SQL Server\Data\userdata.ldf’, SIZE = 5MB, MAXSIZE = 250MB, FILEGROWTH = 10MB)
  • Stop the SQL Server Database Service.
  • Copy the userdata.mdf file from directory, which holds backup on a path as C:\Program Files\Microsoft SQL Server\Data\.
  • Hit on Yes option, if asking for overwriting the previous existing userdata.mdf file. Delete the userdata.ldf file from the path C:\Program Files\Microsoft SQL Server\Data\ directory and then, the SQL Server will recreate a log file again.
  • Start the SQL Server service, which was stopped in Step (3).

Using the above-described methodologies, one will easily be able to manage the damaged SQL Server database from files present in the MDF folder.

Observational Verdict

Now after explaining every small factor of the SQL files, we are winding up the methods with the help of which one can restore SQL Server database from MDF file. It provides end users several benefits for the business purposes and the personal too. These two methodologies help one in recovering SQL Server DB whenever required.