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.
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.
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:
After applying above steps on your SQL Server you can see the attached database on databases node.
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.
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')
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.
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.