SQL Server is a relational database management system designed for large-scale transactions, e-commerce applications, data mining, and so on. Moreover, it is widely used on Business platforms for data analysis, data integration and processing components as it keeps all records fast, flexible and secure. The SQL server database contains three files i.e.
- Primary Database File (MDF file)
- Secondary Database File (NDF file)
- Log file (LDF file)
First, database file is Primary File amongst the three, which consists all the data and schema and also has the file format as .mdf. The second data file is Log File that maintains all the database transactions logs so the desired information can be accessed later to recover SQL server database. There must exist a single log file for each database and it is possible that many log files can be created for an individual database. The file extension for saving the transaction log is .ldf format.
Why to Restore MDF File Without Log file
If there is any corruption happens in LDF file, users unable to take the backup of Log file. According to this case, users need to restore SQL Server database without LDF file and need to recreate the Log file as well.
Solutions to Recover SQL database from MDF file
There are two methods to restore .mdf database file without .ldf file those are mentioned below:
- Using SQL Server Management Studio
- Using Transaction-SQL Script
By Utilizing SQL Server Management Studio (SSMS)
Take a look at the steps mentioned below to restore the SQL .mdf file without Log file:
- First of all, open SQL Server Management Studio.
- Then, right-click on the databases > click on Attach from the drop-down list.
- Now, click on Add button > browse the location of database file (MDF) file > choose the file and click on OK button.
- Finally, display the details in attach dialog box and select LDf file and press Remove button. After that, click on OK button for restoring the MDF file without LDF file. During the restoration of database, SQL server will create a new Log file.
- At last, check the database in desired databases folder.
By Utilizing Transact-SQL Script
Restore SQL Server MDF file without LDF file using Transact SQL script:
- Click on “New Query” from the Server Management Studio toolbar
- Execute the following query
CREATE DATABASE DatabaseName ON
(FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DatabaseName.mdf')
FOR ATTACH_REBUILD_LOG
GO
Conclusion
Here, we end up with the procedure to restore SQL Server database from MDF file only (without LDF file) using SQL Server Management Studio and Transact-SQL script.
