Your SQL Server may break down due to some unexpected reason, such as MDF, NDF, or LDF file corruption, system failure, malware attack, etc. At this point, you can make your SQL Server run again by restoring database from MDF and LDF files. In this article, we will show you how to manually restore SQL Server database from MDF and LDF files through SQL Server Management Studio and T-SQL. Or using Qiling MS SQL Recovery toolto restore SQL database with simple clicks insteadof manually entering the command lines.
There are two ways to restore database from MDF and LDF filesin SQL Server2017/2014 either with SQL Server Management Studio or SQL Server itself. Whichever method you will use, you need to make the following prerequisites.
1.Open SSMSand go to "Object Explorer".
2.Right-click the database you need to restore and select "Attach". Click the "Add" button when "Attach Database" Windows appears.
3. Browse the location of MDFfileand selectit.Then, click "OK". And SQL Server Management Studio will restore the database from the MDF file.
Login to SQL Server database, go to New Query Windowand run the following T-SQL script to attach MDF file in SQL Server.
Create database dbname
On
(
Filename= 'path where you copied files',
Filename ='path where you copied log'
)
For attach;
If the above two solutions can't restore SQL Server database from MDF and NDF files, don't get discouraged. You can still use the following error-free way to do it with ease.
When your SQL Server database becomes corrupt, usually the MDF files are affected. You can restore your SQL database by repairing the corrupted MDF file with a professional SQL recovery program. And Qiling MS SQL Recoveryis such software that enables you to restore database from MDF file in SQL Server with a few clicks. Meanwhile, it can automatically fix a corrupted log file that can result in SQL database errors while it repairs.
Now, follow the step by step guide given below to restore database from MDF and LDF files in SQL Server 2017, 2014, or 2012.
Step 1. Stop MS SQL Server service via services.msc or Management Studio.
Step 2. Run Qiling SQL Recovery. In the main interface, choose the MDF/NDF file of the database you want to restore. Then click "Repair" to start repairing your MDF/NDFfile.
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 3. When it has done, you will see the recovered database objects listed in the left pane of the window.
Step 4. 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 5. Now restart the SQL Server.