Your SQL Server may break down due to unexpected reasons like MDF, NDF, or LDF file corruption, system failure, or malware attacks. To get it running again, you can restore the database from MDF and LDF files. This can be done manually through SQL Server Management Studio and T-SQL, or you can use Qiling MS SQL Recovery tool to restore the database with simple clicks.
To restore a database from MDF and LDF files in SQL Server 2017/2014, you can use either SQL Server Management Studio or SQL Server itself. Regardless of the method you choose, you'll need to meet certain prerequisites. These include having the MDF and LDF files available, ensuring the SQL Server service is running, and possibly attaching the MDF file to the server if it's not already.
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. To restore the database from an MDF file, select the MDF file location, click "OK", and SQL Server Management Studio will handle the database restoration.
To attach an MDF file in SQL Server, navigate to the New Query Window and execute the following T-SQL script: `ALTER DATABASE [DatabaseName] SET OFFLINE WITH ROLLBACK IMMEDIATE;` followed by `ALTER DATABASE [DatabaseName] SET ONLINE;` where [DatabaseName] is the name of the database containing the MDF file. This script first sets the database offline, then rolls back any ongoing transactions, and finally brings the database back online.
Create database dbname On ( Filename= 'path where you copied files', Filename ='path where you copied log' ) For attach;
To restore a SQL Server database from MDF and NDF files, attach the MDF file to SQL Server using the command "ALTER
When a SQL Server database becomes corrupt, the MDF files are often affected. To restore the database, you can repair the corrupted MDF file using a professional SQL recovery program, such as Qiling MS SQL Recovery, which enables you to restore the database from the MDF file in SQL Server with ease. Additionally, the software can automatically fix a corrupted log file, which can result in SQL database errors during the repair process.
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/NDF file.
If you know the exact location of the file, click "Browse" to locate the database. This will allow you to select the file from your computer and upload it to the system.
If you don't know the file location, click "Search" to search for the .mdf or .ndf file in your system, which will help you locate the file even if you're unsure where it's saved.
Step 3. When the process is complete, the recovered database objects will be listed in the left pane of the window.
Step 4. To export your database objects, click the "Export" button located in the bottom right corner of the screen. This will allow you to save your database objects in a preferred format. You can choose between MDF and SQL scripts as the export format.
To save the repaired data, select "Create new database" or "Export to existing database" on the "Export to database" window. If choosing "Create new database", enter the database name and select an SQL location. Alternatively, select "Export to existing database" and choose an existing database.
Step 5. Now restart the SQL Server.