Here, we bring some solutions for situations where you find an error while attaching the MDF file to SQL Server with log files in corrupt or missing state.
SQL Server, the most popular application for storing and managing large amounts of data, has database files to store its information. It has mainly two types of files. The MDF file, the Master Database File (also known as primary), stores SQL schema and data. Other than this database file, there is NDF file that is optional for use, in case MDF database file gets oversized. Lastly, the LDF file is Log Database File, which stores transactional or log information of the SQL database.
Under some situations, log files get missing while attaching the MDF file to the server, and the process fails due to this. In such situation, one thing can be done – attach the MDF file without log file. Once the MDF file is successfully added, log files would be created automatically.
Ways To Attach MDF Without LDF File
There are some manual methods through which the administrator can restore master database in SQL Server without LDF, the log file
Use T-SQL Script
Any SQL administrators with basic knowledge of SQL scripts can try to add the SQL database again without attaching the Log (.ldf) files. Follow the below instructions carefully.
- Launch the Microsoft SQL Server Management Studio. Right-click on the SQL Server directory node and select New Query option from the list.
- The SQL query box will get open. Use this T-SQL script to attach MDF without LDF file in SQL Server.
- The output results would be shown like this.
(FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Template Data\.mdf‘)
FOR ATTACH_REBUILD_LOG
GO
After this, click Execute at the top.
This method requires some technical skills and prior knowledge of running scripts. Therefore, let us move ahead with a simple way to attach the MDF file without LDF file in SQL Server.
Use SQL Server Management Studio
It is a simpler method and does not require technical skills for its execution. The interface allows SQL administrators and authorized users to attach MDF file and remove corrupt or missing (not found status) log files. Let us make it clearer with the steps and screenshots.
- Start the SQL Server Management Studio. On the left panel, in the Object Explorer, select Databases and right-click on it. Then click Attach.
- A new page named Attach Databases will get open. Click Add to add the SQL database file.
- Browse and select the .mdf file from the system folder location. Click OK.
- The MDF file will get added, and you can see the log file also along with but in “not found” state. Select the log file and click Remove option to delete this log file.
- The log file will get removed. Now click OK to attach the added MDF file only.
- The log file will get removed. Now click OK to attach the added MDF file only.
Do These Solutions Work For Any MDF File?
After discussing the methods for attaching the MDF file without LDF file, it is an important query of many users that if any MDF file can be attached, i.e. in any state. For successful execution of the above processes, the MDF file must be in a healthy state (not corrupted, damaged, or broken).
But what if you found that your MDF file is also corrupted? Then, we have the best solution for you. SQL Database Recovery tool is a one-stop solution for corrupt/damaged/inaccessible SQL Server database files – MDF, NDF, and LDF. It recovers complete SQL database objects like records, triggers, tables, functions, deleted records, etc. Users can make selection of the data for recovery after viewing its preview and save to Live SQL Server or a batch file. This reliable and secure tool supports almost all SQL Server versions including SQL 2019. Try its demo version available on the site to get a good idea of the tool.
