Know How To Attach MDF File Without LDF File
SQL Server uses two files to save its database – MDF files and LDF files.
MDF File: MDF file is also known as a primary data file that contains all the data and schema. The supported file extension for MDF file is .mdf.
LDF File: LDF stands for Log data file and contains all the database transaction logs. The file extension to save the LDF file of SQL Server is .ldf.
User can also create a secondary database file to store the data which is known as NDF file if the primary database file become large.
Need to Attach MDF file Without LDF File
If you are getting an error message while attempting to attach SQL Server database files, it means your SQL Server database files have surface corruption issues. You cannot take the log backup, if there is a corruption in your LDF file. In this situation you can attach your MDF file without LDF file and recreate your LDF file. This can also resolve your problem if your log files are deleted intentionally or intentionally.
How to Attach MDF file Without LDF File?
There are mainly two methods to attach SQL MDF file without LOG file.
- By using SQL Server Management Studio (SSMS)
- By using T-SQL script
Attach MDF File Without LDF file by Using SSMS: You can attach your SQL MDF file without LOG file by following underlying steps.
- Open MS SQL Management Studio
- In Object Explorer , Right Click on Databases and then Click on Attach from the drop down menu
- In Attach Databases Windows, Click on Add button
- Browse the location of your MDF File in Locate Database Files, select the file and click on OK
- You will see the database details in Attach Database dialog box; Select the LDF file and then Click Remove Button. Now Click on OK to attach MDF file without LDF file. While attaching the database, SQL Server will create a new LDF file.
- Now you can check your database in the databases folder.
Attach MDF File Without LDF file by using T-SQL script: You can also run a TSQL Script on SQL Query to attach your MDF file and recreate your transaction log file.
Where, testdb is the name of your database.
Now you can check your database in the database folder.
NOTE: These methods will work only if your database is cleanly shut down and you have a healthy MDF file database.
In this Blog you have seen two methods to attach MDF file without LDF file. One is by using SQL Server Management Studio and another by using SQL Script. Both the methods will works only for the healthy MDF file and in case on clean shut down of database. In case, if the database file you are trying to attach is corrupted, then first try to restore from a recent backup of your database file or there are some alternate solutions like SQL MDF recovery Software which is capable to repair corrupt .mdf file and after fixing the corruption issue, you can follow the above approach to attach .mdf in SQL Server.