How to Attach Only MDF File in SQL Server 2008
3 min read
Do you want a quick solution to attach only MDF file in SQL server? If yes, then you are on the right page, here we are going to explain the complete procedure for the same. Before proceeding further first, let’s get a brief idea about SQL Database Files.
Get an Overview of MDF and L DF files
MDF File is also known as primary database file, it contains start-up information for the database to run and tracks all the databases on the SQL server. Every database in SQL Server has only one primary data file. The supported file extension for the primary data file is .mdf.
LDF or Transaction log file holds the database information in addition to keeping a record of all the actions and changes made on the data on the server. It includes; date, time, details of changes made, user information regarding who made the changes. The supported file extension for the transaction log file is .ldf.
Why users need to attach MDF file without LDF?
If there is corruption in LDF file and the transaction log files are deleted intentionally or intentionally. In this situation, you can attach only MDF file in sql server. Once the database file is attached successfully, SQL Server will create a new LDF file.
How to attach only MDF file in SQL Server 2019, 2017, 2016, 2014, 2012?
In the subsequent section, you will get the different methods to connect MDF file to MS SQL Server in the absence of log file.
Method 1: Using SSMS
1. Launch SQL server management studio. Under the Object Explorer section, right-click on the Databases and then choose the Attach from the menu.
2. Under Attach Databases Wizard, click on the Add button.
3. Browse the location of MDF file, select the desired file that you want to attach, and then hit the OK.
4. Afterward, an Attach Database screen will appear where you see the database details, it shows the Log file is not found or missing.
5. Select the LDF and press the Remove button. At last, click the OK to attach only MDF file in the SQL server.
Method 2: Using T-SQL Query
To execute this method you need to run a T-SQL Script on SQL Query page. Use the script mentioned below:
USE Master
Go
CREATE DATABASE Database_name ON
(FILENAME = ‘C:\Program Files\Microsoft SQL Server\..\MSSQL\DATA\’Database_name.mdf’)
For ATTACH_REBUILD_LOG
GO
What Will happen if the MDF file is corrupt?
In case, your MDF file is corrupted or inaccessible then the above-mentioned methods do not work. In such a situation first, you need to repair corrupted MDF file and then attach the healthy database file to SQL Server. For this, It is recommended to use SQL Server Database Recovery tool. This solution can fixes the corrupted, damaged, inaccessible MDF and NDF database files of SQL Server and allow users to export recovered data directly to the live SQL Serer database or as SQL Scripts. With this tool, users can efficiently recover the SQL database files without compromising the data integrity or loss.
Steps to attach only MDF file in SQL server 2019, 2017, 2016, 2014, 2012
1. Download and run the SQL Recovery tool in your system, after that click the Open to add your MDF file.
2. Select a scan option (as per the level of corruption) and also select the version of MDF file.
3. Once the scanning process is completed, preview the complete database and click the Export button.
4. To attach the MDF file in SQL Server, select the export to SQL Server database option.
5. Now, Enter the export detail and finally, click the Export button.
Done, this is how you can attach the healthy, corrupt, or inaccessible MDF file to SQL Server in the absence of log file.
Time to Wind Up
In this write-up, we have mentioned the best solution to attach only MDF file in SQL Server. The mentioned manual methods only work if the database is cleanly shut down and the required database file is corruption-free. In case of corrupt database files, it is suggested to take the help of an alternate solution. The software recommended here is completely secure and gives accurate results in all cases.
About Author
