Summary: Are you looking for a way to attach an SQL Server .MDF (primary database) file without transaction log file? This blog demonstrates different methods to attach a SQL db without the log file. If the MDF file you want to attach is corrupt or inaccessible, try using Stellar Repair for MS SQL software to repair the file.
When the transaction log file (.ldf) is missing or corrupted, and you only have the .MDF file to restore SQL database; moreover, when there’s no backup available, you can attempt to attach the database file (.MDF) to SQL Server.
Following are 3 methods you can use to attach Microsoft SQL database without using the transaction log:
Note: To attach the database in SQL Server, you will need membership of the db_owner role.
Note: Method 1 and Method 2 will only work if the database had a clean shutdown (i.e., there were no active transactions while the database is shut down) and the MDF file is in a healthy state.
Step 1: Open SSMS, right-click on the Database node in the Object Explorer window and then click Attach.
Figure 1 – Attach Option in SSMS
Step 2: In the Attach Databases window, click the Add button to specify the database you want to attach.
Figure 2 – Add a SQL Database in SSMS
Step 3: When the ‘Locate Database Files’ dialog box opens, browse through the path where .mdf database file is located, select the file, and then click on OK. A new LDF file will be created by SQL Server while attaching the SQL database. The database will now appear in Databases.
Figure 3 – Select MDF File in SSMS
Step 4: Now, return to the Attach Databases window. Under ‘Database details’, you will notice that the SQL Server cannot find the LDF file.
Step 5: To attach MDF without LDF file, select the transaction log file (.ldf), and then click on Remove.
Executing these steps will attach an SQL database without the log file.
You can also attach the database to SQL Server using T-SQL when the transaction log is missing. This procedure can be accomplished by using the following defined script where you need to mention the database’s name:
CREATE DATABASE testdb ON
(FILENAME = ‘C:\Program Files\Microsoft SQL Server..\MSSQL\DATA\testdb.mdf’)
For ATTACH_REBUILD_LOG
GO
Note: Replace the ‘testdb.mdf’ in the above example with your MDF file.
Execute DBCC CHECKDB
When the MDF file is successfully attached to the SQL Server with a missing transaction log file (LDF), you must execute DBCC CHECKDB for a consistency check, i.e., for checking logical and physical integrity or errors in all the database components.
The above methods won’t work if the MDF file you want to attach is corrupted. In that case, having an MDF database file repair tool, such as Stellar Repair for MS SQL at your disposal can help you scan and repair the MDF file.
Once the master database file is repaired, you can use it to attach the SQL database without transaction log-file using either SSMS or executing a query in Transact-SQL.
Read this: How to Repair MDF File?
Hear what SQL MVP ‘Daniel Calbimonte’ has to say about Stellar SQL Database Repair Software.
Conclusion
This blog discussed several different ways to attach the MS SQL Server database without log file’. When connected to an instance of SQL Server, attach database without transaction log file using SQL Server Management Studio (SSMS). Or, you can use Transact-SQL (T-SQL) to attach the db to the server. But, if the MDF file is lost or corrupt, try using Stellar Repair for MS SQL software to repair the file and restore SQL database.
Problem solver and Data recovery specialist. Usually share informative articles on data recovery, database corruption and ways to recover lost data.
Hi Jyoti,
I tried all methods as you have mentioned and the last solution helped me. I do not know why solution one did not work even I have admin rights. Well, thanks for the article.
Hi Jason,
We are happy to help you.
Hi,
this is a great help I must say, thanks guys really I mean it. I mean everybody is looking for money these days while we still have a few good people like you 🙂
Question please: What is the better way to deploy DB into SQL server for an application: is it, by attaching the MDF file and the log file, or by executing the SQL script? I mean what are the advantages and disadvantages of both ways commercially and practically?
Your support is truly highly appreciated.
But, I have limited rights in the SQL server database.
Hello Patricia,
Any bad move can make your situation worst. To avoid this situation, you should execute manual steps under the observation of an experienced administrator.
Great!
Amazing to see that you guys are providing free tips for SQL DBAs without any hidden charges. Your efforts will encourage other technology providers also.
Thanks for your motivational words!
I purchased your SQL recovery software after getting the complete preview of my database in demo version. Thanks you for this wonderful utility..
Hello Satya,
Thanks for your valuable comment.