SQL Server uses the primary data file (.MDF) to store data in form of tables and other database objects. Sometimes, you may need to restore the SQL database from an MDF file. Some of them are as follows:
- SQL Server crash
- Upgrading the current SQL Server version to a higher version
- Migrating a database to another location
SQL Server uses an MDF file to restore data from a database and a transaction log file to restore the database logs. However, if you don’t have a log file, you can use the primary data file alone to perform the restore process. But, you can only attach an MDF file without a transaction log file (LDF) if your database was properly shutdown and there were no open transactions during the shutdown.
Methods to Restore Database from MDF File in SQL Server
In order to restore a SQL database from the master database file (.mdf), you need to:
Method 1 – Attach SQL Server Database using MDF File
- Attach a Database using SQL Server Management Studio (SSMS)
- Attach a SQL Database using Transact-SQL (T-SQL)
Method 2 – Use SQL Repair Tool to Restore Database
Let’s discuss both these methods in detail:
Method 1 – Attach SQL Server Database using MDF File
Note: You cannot attach a damaged (or corrupted) MDF file in SQL Server. In that case, use DBCC CHECKDB with repair options or a specialized MDF file repair tool to fix the corrupted file and restore database from mdf file.
Following are the two methods to attach SQL database using an .mdf file:
Attach a Database using SQL Server Management Studio (SSMS)
Follow these steps to attach a database using SSMS:
- Open SSMS and connect to an instance of SQL Server.
Figure 1 – Connect to SQL Server Instance
- Right-click on Databases, and then select Attach.
Figure 2 – Select Attach Databases
- From the ‘Attach Databases’ window, click Add to select the database you want to attach.
Figure 3 – Select Add to Attach SQL Database
- From the ‘Locate Database Files’ dialog box, browse and select the .mdf file of the database to be attached. Press OK.
Figure 4 – Select MDF File to be Attached
Note: Are you receiving ‘Attach database failed for server’ error message with 5173 error code? Refer to this link for information on how to fix the error.
- The ‘Attach Databases’ window appears with MDF and LDF files displayed under the database details: section.
Figure 5 – SQL Database Files
- Click OK to restore your database (i.e. TestDB in our case).
Attach a SQL Database Using Transact-SQL (T-SQL)
Follow these steps to attach a SQL Server database using T-SQL:
- Open SSMS and connect to the Database Engine.
Figure 6 – Connect to Database Engine
- Click on the New Query tab.
Figure 7 – Select New Query
- Execute the following CREATE DATABASE command with “FOR ATTACH” clause to attach your SQL Server database:
CREATE DATABASE TestDB ON (FILENAME = 'C:\MySQLServer\TestDB_Data.mdf'), (FILENAME = 'C:\MySQLServer\ TestDB_Log.ldf') FOR ATTACH;
Note: Replace ‘TestDB’ with the database you want to attach in SQL Server.
If you failed to attach a database due to corrupted or damaged MDF file, use Stellar Repair for MS SQL software to repair the MDF file. Once the file is repaired, you can restore the database from MDF file keeping all the file data intact.
The SQL database repair tool can repair a severely corrupted MDF file and recover all its data in just a few clicks. It can handle all types of database corruption errors like 824, 825, 5172, 9001, etc. in SQL Server.
Conclusion
In the event of SQL Server crash, upgrading SQL Server to the latest version, or migrating your database to another location, you will need to restore your database from the primary data file (MDF). You can restore database from MDF files in SQL Server using the methods discussed in this blog. These methods apply to all supported SQL Server versions 2019, 2017, 2016, 2014, and earlier. You can attach the MDF file using SSMS or T-SQL and restore the database. However, if the file is corrupt, you may use DBCC CHECKDB with ‘REPAIR_ALLOW_DATA_LOSS’ to fix MDF file corruption. Or else, use Stellar Repair for MS SQL software to repair MDF file without any risk of losing data.
Was this article helpful?
