How to Take Backup of MDF and LDF files in SQL Server – Explore Here
In the present decade, the Relational database Management framework turned into the need for the associations whether they are little level or huge levels. In such a setting SQL Server is the best and fruitful RDBMS that offers exceptionally coordinated types of assistance to control the SQL database over the Server. As we talk about the SQL Server database the Server gives the primary storage MDF File, NDF file otherwise called a secondary file, LDF log transaction file.
All things considered, in this blog, we will examine How to Take Backup of MDF and LDF files in SQL Server to a database so how about we make a few requirements perform tasks with no difficulty.
Important Note: In case if you are facing corruption in the MDF file and the SQL database is inaccessible then try SQL Server Data Recovery. By using this application the user can easily export and recover the deleted SQL database objects also. This software supports Microsoft SQL Server 2019 and below versions.
Know-How to Take Backup of MDF and LDF files in SQL Server Server Management Studio Express
- Open SQL Server Management Studio Express and interface with the SQL server.
- Expand Databases.
- Right-click on the database you need to back up, at that point select Tasks > Back up.
- On the Back Up Database window, ensure the Database field contains the name of the database you need to back up.
- Select the Backup Type. As a matter of course, it is Full – leave it set to that.
- Click on the Remove to expel the default/last backup file name.
- Click on the Add to open the Select Backup Destination window.
- Click on the […] close to the File Name field.
- On the Locate Database Files window, select the folder where you need the backup file to go. As a matter of course, it is ..\Microsoft SQL Server\MSSQL.1\MSSQL\Backup.
- In the File Name field, type the name for this backup, with a .bak augmentation. For instance, xyz_20080221.bak for a backup of the XYZ database made on 21 February 2008.
- Click on the OK to close the Locate Database Files window.
- Click on the OK to close the Select Backup Destination window.
- Click on the OK to begin the backup. The progress icon shows in the lower-left corner, and a ‘finished effectively’ message shows when it’s set.
Also Read: Migration from SQL Server 2008 to 2016 Version
So far we have discussed how to take backup of MDF and LDF files in SQL . Now we will discuss how to restore data from .bak files.
There are two different ways to restore the database from MDF file in SQL Server:
1. With the assistance of SQL Server Management Studio
2. Utilizing T-SQL
1. Through SQL Server Management Studio
- Open SSMS and go to Object Explorer of the database engine instance
- At that point, right-click on the database and select To Attach Option of the list button
- Windows show up, at that point click on Add button
- Select the MDF file and click on the on OK Button
- Validate the chosen files and go to OK Button
- Presently check the attached database
2. On utilizing T-SQL
- Login to SQL Server database engine
- Go to New Query Window and use Create database with Attach close
CREATE DATABASE MyDatabase
ON (FILENAME = ‘C:\SQLServer\MyDatabase_Data.mdf’),
(FILENAME = ‘C:\SQLServer\ MyDatabase _Log.ldf’)
Now, by using these upper-mentioned techniques, you can easily restore the backup of the database. But, if your database backup files are corrupted then no need to worry, you can recover the corrupted BAK file format. Now, to do so, we suggest you use a third-party tool such as SQL Backup Restore tool. This tool is a vigorous and proficient program to fix.BAK file of SQL Version 2019, 2017, 2016, 2014, and all beneath versions. The interface of the tool is simple and easy to use and ready to recover corrupted backup files effortlessly. The tool doesn’t have any file size confinement to fix the corrupted BAK file.
In this blog, we figured out how to take backup of MDF and LDF files in SQL server. It is energetically prescribed that you need to disconnect the MDF file before connecting another one. This is an extremely accommodating technique to restore the database from MDF file in SQL Server. Moreover, if your backup file is corrupted or damaged, we suggested using a third-party backup recovery tool.