How to Take Backup of MDF and LDF Files in SQL Server

Often, database administrators need to take backup of MDF and LDF files. They can backup and restore MDF and LDF files in SQL Server when needed. But the main concern is that they do not know a viable solution to backup and restore their SQL Database items. This article explains different techniques to back up, restore your SQL Databases. Furthermore, we also suggest a professional SQL Database Recovery utility to repair corrupted SQL database MDF and LDF files. Read the article till the end.

What are MDF and LDF files?

MDF files are the Master Database files and contain the entire database items. An MDF file is mostly required during the database restoration. Moreover, you can even create a new database using these MDF files.

An LDF file is associated with its corresponding MDF file and stores the log data or history of every transaction in the database. Therefore, while recovering SQL databases, LDF files help maintain the data integrity and correct order.

How to Take Backup of MDF and LDF files in SQL Server

When you back up the SQL Server, it will get saved with the .bak file extension. Both .mdf and .ldf files are the SQL database files. You do not need to backup SQL server database as .mdf file manually. However, when you extract a BAK file, it will also result in .mdf and .ldf files. Follow the below procedure to back up the SQL server.

• Launch SQL Server Management Studio.
• Open the database you want to backup and right-click on it.
• Choose the Tasks option and then select the Backup option.
• It will open the Backup Database Select the database, backup type, destination, etc.
• After selecting all the features, click OK to initiate the SQL database backup.

By performing this procedure, you can easily backup SQL server database files in SQL server. Now, if you have lost your data by any reason, we explain how you can restore your SQL server database from .mdf and .ldf files.

How to Restore MDF and LDF files in SQL Server?

MDF files are the Master Database Files. So, if you have an MDF file, you can easily recover the desired database. Moreover, you can restore SQL databases without Transaction Log files. SQL Server Management Studio offers two different functionalities that enable you to restore a database from a .mdf file.

#1. Restore MDF and SQL Data using SSMS

Follow the below procedure to recover SQL data from corrupted damage.

• First, launch SSMS and connect to the SQL Server
• Right-click on the Database option and choose the Attach
• In the Attach Databases wizard, press the Add
• After that, select the desired database file and press
• Now, the selected database file (.mdf) has been added to the Attach database wizard.
• Click on the OK button to complete the procedure and refresh the database.

#2. Use Transact SQL to Backup MDF and LDF file

Another approach to copy MDF and LDF files in SQL server is via the Transact-SQL or T-SQL query. Below is the procedure to recover your SQL server database via the Transact SQL or T-SQL Query.

• First, launch the SQL Server Management Studio and connect to the server.
• After that, click on the New Query option from the top menu bar.
• Now, run the following query:

CREATE DATABASE MDF_FILE_NAME

ON (FILENAME = ‘C:\FilePath\File_name.mdf’)

FOR ATTACH_REBUILD_LOG

GO

• Running this query will update the desired database on the server.

What If the SQL Database File is Corrupted?

The above techniques to restore MDF and LDF files in SQL Server are feasible only when the MDF file is in a healthy state. You can not restore the database when the database files are corrupted. In such a scenario, you will be required to repair the damaged/corrupted SQL database. The professional SQL Database Recovery Tool is the best approach to restoring database MDF and LDF files.

Automated Solution to Restore MDF and LDF Files

DRS SQL Recovery Tool is one of the finest utilities for repairing damaged/corrupted SQL database files (.mdf and .ldf). So that you can easily and efficiently restore SQL database items. It provides two distinct recovery modes to recover data from any level of file corruption. Moreover, saving recovered data as script and restoring to database options are available.

Steps to Repair SQL Database Files

• Download and launch the DRS SQL Database Recovery Tool.
• Press the Open button to add the desired SQL database file.
• Click Browse and choose the MDF file. Select recovery modes and other options. Hit Next.
• Check and select the recovered items. Click on the Save
• Choose authentication mode: Windows Authentication and Server Authentication.
• Select the remaining tool options as per your preference.
• Finally, click the OK button to save the recovered MDF file content.

Also Read: Top 5 SQL BAK File Recovery Tool Recommended by DBA’s

Conclusion

I hope this above write-up helped you to learn how to back up and restore MDF and LDF files in SQL Server. We discussed the method of backing up your SQL server database. We also explained how you can restore databases through MDF and LDF files. The SQL Server Management Studio provides two approaches. You can directly restore the MDF file or can run the T-SQL Command. At last, we shared the professional solution to repair the corrupted/damaged SQL database files. If the MDF files are corrupted, you must use this automated tool to recover the inaccessible data.

Frequently Asked Questions