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.
How to Attach an SQL Database without the Log File?
Following are 3 methods you can use to attach Microsoft SQL database without using the transaction log:
Tip: To attach the database in SQL Server, you will need membership of the db_owner role.
- Using SQL Server Management Studio in SQL Server
- Using T-SQL Script
- Using Stellar Repair for MS SQL software
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.
Method #1: Using SQL Server Management Studio (SSMS)
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.
Figure 4 – Remove Log File Option in SSMS 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.
Method #2: Using Transact-SQL
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.
Method #3: Using Stellar Repair for MS SQL
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?
Key Features of Stellar Repair for MS SQL Software
- Recovers all SQL database file components, including tables, keys, rules, indexes, defaults, stored procedures, etc.
- Recovers deleted records from an MDF file.
- Allows saving the repaired database components in multiple file formats, including XLS, HTML, CSV, and New Database or Live Database.
- Supports SQL Server 2019, 2017, 2016, 2014, 2012, 2008, 2008 (R2).
- The software is tested and recommended by Microsoft MVPs across the globe. The independent file formats allow accessing database contents without connecting to the Server environment. With the tool’s help, you can also gain access to database contents saved in the secondary database files.
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.
Was this article helpful?
