How to Extract Data from .mdf File in SQL Server?

extract data from mdf file

SQL Server uses two different kind of database files, first one is .mdf, which is used to store information like tables, views, stored procedure etc. And another is .ldf file, which saves transactional information of its associated primary database file (.mdf). Secondary database file is also named as .ndf, it is an optional database file used to store data physically. As .mdf is a primary database file of SQL Server, So, it's important to keep its data secure in case of disaster. An .mdf file can be inaccessible, if it's associated .ldf file get missed or lost due to any reason. But, there are some workarounds which will help users to extract data from .mdf file in SQL Server.

Method to Extract Data from .mdf File SQL

SQL Server provides Attach database option that user can use to get data from .mdf file. There are two different methods for attaching .mdf file in SQL Server. One is, by using SQL Server Management Studio and second is, going through T-SQL Script. User can select one of them according to their choice.

1. Attach .mdf file using SQL Server Management Studio

In this method we will go through the GUI of SQL Server database to attach a .mdf file. Below are the steps to perform this action:

Open SQL Server Management Studio
Connect to SQL Server instance of Database Engine
In Object Explorer, Select the Databases and Right-Click on it and then click on Attach.

In Attach Database window, Click on Add button to locate the .mdf file you want to attach.

Now, go to the directory where you have saved your .mdf file and select the .mdf file and then click on OK button.

Again, In Attach database window click on Ok button to attach .mdf file in SQL Server.

step5

After applying above steps on your SQL Server you can see the attached database on databases node.

step5

This simple procedure will be helpful in extracting data from .mdf file. In order to attach .mdf file in SQL Server database one can also try T-SQL script.

2. Attach .mdf file using SQL Server Management Studio

Another method that is used to attach .mdf file in SQL Server is by using T-SQL script.

CREATE DATABASE Mydatbase ON
(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Mydatbase.mdf')
FOR ATTACH_REBUILD_LOG
GO

The above command will attach the database in SQL Server even if the .ldf file is missing to the associated .mdf file. The ATTACH_REBUILD_LOG will create a new transaction log file and create a new database.

Limitations to Extract data From .mdf file in SQL Server

  • Make sure while attaching the .mdf file in SQL Server the database file is not in Read-Only state. For this you can go to the location of .mdf file and right click on .mdf file, then click on properties and verify the Attribute.
  • Check, if the database file is of higher version and you are trying to attach it in lower version.
  • The .mdf file you are trying to attach has a full permission or full access control.

After applying one of the above methods, user can easily attach the .mdf file in SQL Server database. But, there are some limitations listed above and also it is very important to have a healthy .mdf file while using the methods to extract data from .mdf file. Otherwise the SQL Server will prompt an error message if the file is corrupted due to any reason. However, normal corruption of .mdf file can be easily repaired, but, if the file is highly corrupted then it may not be possible to fix it via manual solution. In such situation going for a third party SQL Recovery software is the best approach one can make to overcome the situation and repair corrupt MDF file.

Upshot

In this article we have explained the methods to extract data from .mdf file by attaching it to SQL Server using SSMS and T- SQL script. Both the methods are helpful in order to extract data from SQL Server .mdf file, Also there are some limitations which we must considered before going to attach .mdf file in SQL Server.