How to Fix Database from MDF File in SQL Server 2008/2008 R2
Applies To: MS SQL Server 2016, 2014, 2012, 2008/2008 R2 and older versions
As one of the best database management tools, Microsoft SQL Server is frequently used among database administrators, and the versions, SQL Server 2008 and 2008 R2, still gain widespread popularity. However, similar to using any other software, it is not without any problem while using SQL Server. One of the common issues encountered by SQL Server users is the corruption of the database, which will need you to restore the database from the MDF and LDF files in the SQL Server. Fortunately, the way to repair your database with the MDF and LDF files are quite straightforward and there are two different methods to achieve it. Apply your preferable solution and restore your SQL Server database without delay.
How to Restore Database from MDF File in SQL Server 2008/2008 R2
There are two options for you 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
- Put the MDF file and the LDF file in the same location and a specific folder
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. Then search for and choose the .mdf file of the database you want to restore. Click "OK" > "OK".
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.
Method 2. Restore Database from MDF in SQL Server with SQL Recovery Software
Professional SQL repair tool - Qiling MS SQL Recovery, will easily fulfill your needs of restoring the database from MDF file in SQL Server. It is especially good at restoring the corrupt database by repairing its MDF file. Besides, it also performs well in:
- Restoring corrupted SQL server database objects - tables, triggers, indexes, keys, rules & stored procedures
- Repairing database log files that may result in database errors
- Recover deleted/dropped SQL database records
Now, restore your database from MDF file in SQL Server with Qiling MS SQL Recovery:
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.
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.
If you don't know the file location, click "Search" to search for the .mdf or .ndf file in.
Step 4. When it has done, you will see the recovered database objects listed in the left pane of the window.
Step 5. Click "Export" in the bottom right corner of the screen to save your database objects. Choose a preferred format, MDF, or SQL scripts.
On the "Export to database" window, choose "Create new database" or "Export to existing database" to save the repaired data. If you want "Create new database", enter the database name and choose an SQL location. If you select "Exporting to existing database", you need to select the existing database.
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