|Summary: In SQL Server, you can restore a database by attaching an MDF file to the database. This blog discusses the ways to attach an MDF file in SQL Server. It also suggests using a SQL recovery tool to regain access to the database when the MDF file is corrupt. |
SQL Server stores all the information of a database in the primary data file (MDF). The server also uses a log file (LDF) that holds the information needed to recover a database. Situations may arise when you need to restore your database from an MDF file (with or without an LDF file) in SQL Server. These situations include:
- Migrating databases from an old SQL Server to a new server.
- Accessing client database on another system.
इस पोस्ट को हिंदी में पढ़ने के लिए यहां क्लिक करें
How to Attach an MDF File in SQL Server?
Before discussing the ways to attach an MDF file, there are some prerequisites you need to meet. Prerequisites
- Detach the database. This is because attempting to attach a database that is not detached will throw an error.
- Ensure that the MDF file and LDF files are stored in the same folder/location.
- Ensure that SSMS is installed on your system.
Methods to Restore Database from MDF File in SQL Server
Use any of these two methods to restore a database from an MDF file:
Method 1 – Use SQL Server Management Studio (SSMS)
Follow these steps to attach the primary database file using SSMS:
- Connect to a SQL Server instance.
- From the Object Explorer pane, right-click on Databases and click the Attach option.
- In the dialog box that appears, click the Add button
- Choose the MDF file you want to restore and then press the OK button.
- The data (.mdf) and log (.ldf) files will be added in the ‘Attach Database’ window. Validate the selected files and click OK.
- Refresh the databases and you can see the database in Object Explorer.
Note: You may receive a SQL error 5123 during the attach process. This may happen if you detach a database with several logins or if you do not have sufficient permissions required to attach the MDF file. To fix the 5123 error, you will need to grant login permission and full control over the MDF file, then try to attach the database. Or else, run SSMS as Administrator. For this, click on the Windows Start menu and search for SSMS. Right-click on SSMS and select ‘Run as Administrator.’
Method 2 – Use Transact-SQL (T-SQL) Query
You can also restore the MDF file by running a T-SQL query. To do so, perform these steps:
- Open SSMS and connect to an instance of your SQL Server.
- Click the New Query option from the toolbar.
| CREATE DATABASE TestDB_Suspect |
ON (FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\TestDB_Suspect.mdf’),
(FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\TestDB_Suspect_log.LDF’)
Note: Replace the ‘TestDB_Suspect’ with the name of the MDF file you want to restore. Also, change the data and log filename in the path mentioned above. If you want to attach a .mdf file without a .ldf file, then run the query below to attach the database.
|CREATE DATABASE TestDB_Suspect |
ON (FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\TestDB_Suspect.mdf’)
Executing the above T-SQL query will attach the MDF file in SQL Server and restore the database.
Note: The attach process may fail with an error message stating that the .mdf file is read-only. In that case, go to the location where the file is stored, open its Properties, and ensure that the ‘Read-only’ checkbox is marked as unchecked.
Related Blog Post: How to Restore Database in SQL Server?
What to do if Restoring the MDF File Fails?
The database restore process will fail if the MDF file you are trying to attach is damaged or corrupted. In that case, you can restore the database from the last known valid backup. If the backup is not available, use a specialized SQL repair tool to avoid further downtime associated with manually troubleshooting the corrupted database file. Stellar Repair for MS SQL is one such tool you can use to repair corrupt MDF files with up to 8X speed. The software helps retrieve all the database objects, including tables, deleted records, stored procedures, keys, etc. while maintaining data integrity.
For information about repairing MDF files using Stellar Repair for MS SQL software, refer to this link.
In this post, we discussed the methods to attach MDF (and LDF) files to restore a SQL database. You can attach the MDF file using SSMS or T-SQL query. If the restore process fails due to corruption in the database file, consider using Stellar Repair for MS SQL to fix the corrupted MDF file.