How to Fix Database from MDF File in SQL Server 2008/2008 R2
This guide applies to Microsoft SQL Server versions 2016, 2014, 2012, 2008, and 2008 R2, as well as older versions.
Microsoft SQL Server is a popular database management tool, and versions like SQL Server 2008 and 2008 R2 are still widely used. However, like any software, it's not immune to issues, and one common problem is database corruption, which requires restoring the database from MDF and LDF files. Fortunately, repairing the database with these files is a straightforward process, and there are two methods to achieve it, allowing you to restore your SQL Server database quickly.
How to Restore Database from MDF File in SQL Server 2008/2008 R2
You have two options to restore MDF and LDF files to your SQL database.
Method 1. Restore Database from MDF Using SQL Server
You can choose to repair your SQL database with the help of SQL Server Management Studio or T-SQL.
Preparation:
- Detach the database you want to restore
- To put the MDF file and the LDF file in the same location and a specific folder, you can create a new folder, move both files into it, and then set the working directory for your project to that folder. This ensures that both files are in the same location and can be accessed by your project.
Tip 1. Use SQL Server Management Studio
Step 1. Open Microsoft SQL Server Management Studio and go to "Object Explore".
Step 2. Right-click on "Database" and choose "Attach".
Step 3. In the new window, click the "Add" button, search for and choose the .mdf file of the desired database, then click "OK" twice.
Tip 2. Use T-SQL
Execute the following command in the management studio:
Create database dbname
On
(
Filename= 'path where you store the MDF file',
Filename ='path where you store the LDF file'
)
For attach;
If the two options above fail to restore your database from the MDF file in SQL Server, don't be upset, you still have another choice. You can try to attach the MDF file to a new SQL Server instance.
Method 2. Restore Database from MDF in SQL Server with SQL Recovery Software
Professional SQL repair tool - Qiling MS SQL Recovery is a tool that can easily fulfill your needs of restoring the database from an MDF file in SQL Server. It is particularly effective in restoring corrupt databases by repairing their MDF files, and also performs well in other areas.
- To restore corrupted SQL server database objects, follow these steps:
- Repairing database log files that may result in database errors
- Recover deleted/dropped SQL database records
To restore your database from an MDF file in SQL Server using Qiling MS SQL Recovery, follow these steps:
Step 1. Stop MS SQL Server service via services.msc or Management Studio.
Step 2. Run Qiling SQL Recovery, select file types and click "Next" to start the recovery process.
Step 3. Choose the MDF/NDF file of the database you want to restore. Then click "Scan" to start repairing your MDF/NDF file.
If you know the exact location of the file, click "Browse" to locate the database. This will allow you to select the correct file from your computer and upload it to the relevant section.
If you're unsure about the location of the .mdf or .ndf file, you can click on the "Search" button to initiate a search for the file in your system. This feature helps you locate the file even if you're not familiar with its exact location.
Step 4. The database recovery process will scan the backup file, identify the database objects that were backed up, and restore them to their original location. Once the process is complete, the recovered database objects will be listed in the left pane of the window, allowing you to view and manage them as needed.
Step 5. To save your database objects, click on the "Export" button located in the bottom right corner of the screen. You will then be prompted to select a preferred format for the export, which can be either a Microsoft Database File (MDF) or SQL scripts. This allows you to choose how you want your database objects to be saved, whether as a single file or as a series of SQL scripts.
On the "Export to database" window, choose whether to create a new database or export to an existing one to save the repaired data. If creating a new database, enter the database name and select an SQL location, or select an existing database to export to it.
Step 6. Now restart the SQL Server.
Related Articles
- SQL Database Recovery Pending Access Denied
- How to Recover Deleted Records in MS SQL Server Automatically
- MS SQL Recovery Software to Recover MDF/NDF File
- How to Rollback DROP/TRUNCATE Table in SQL Server