3 Methods to Attach SQL Database without Transaction Log File


A developer approached our team who wanted to restore the SQL database. He had only an MDF file present in the system. We suggested him to restore his database from the updated backup copy but, he didn’t have the backup.

This blog demonstrates the effective solution to attach Microsoft SQL database without log files when  SQL database gets corrupted. Just follow these steps here below that include different methods by using SQL Server Management Studio, T-SQL, and SQL Database Repair software to attach MDF without LDF.

MDF and LDF Files

Master database file (MDF) and Log transaction files (LDF) are two main files used by SQL Server for saving the database contents. If a database appears abnormal or turns corrupt, you can check transaction logs to determine the cause of the error.

Due to corruption in SQL database, you will find the transaction log file to appear missing, and you will be left with the master database file only.

Note: As per Microsoft guidelines, you cannot restore the database backup from the higher release of the SQL Server to its lower version as by using the Database Backup and Restore strategy, the downgrade is not supported.

It is important to know that a database downgrade from a higher version of the SQL Server to a lower version cannot be accomplished using either the ‘Database Backup & Restore’ technique or by following the ‘Database Detach and Attach’ method. Downgrading a Database to its lower version can only be done by using SQL Server Integration Services.


Ways to Attach Microsoft SQL Server Database Without Log File

There are several ways to attach Microsoft SQL database without using the transaction log:

Permissions Required: To attach the database in the SQL Server, you need to possess membership in db_owner.

Let’s look at these methods: 

Method #1: Using SQL Server Management Studio (SSMS)

Here, it is important to mention that the given steps will work only if the database has been clean shutdown and MDF file is available.

  1. Right click on the Database node in the Object Explorer window and then click on Attach.
attach Microsoft SQL database
Attach Option
  1. In the Attach Databases, dialog box that appears click on the Add.
attach Microsoft SQL database
Add Database
  1. Locate Database Files dialog box will appear, browse through the path where MDF database file is located, click on file to select and then click on OK to exit. A new LDF file will be created by SQL Server while attaching the SQL database. The database will now appear in Databases
attach Microsoft SQL database
Select MDF File
  1. Now, return to the Attach Database dialog box. Under database details, you will notice that the SQL Server is unable to find the LDF file.
attach Microsoft SQL database
Remove Log File Option
  1. To attach MDF without LDF file, select the transaction log file and then click on the Remove

Method #2: Using Transact-SQL

You can attach the database to SQL Server using T-SQL when the transaction log is missing. This procedure can be accomplished by using a defined script where you need to mention the name of the database. The T-SQL execution process guarantees a successful attach instance in SQL Server database. The process is given below:

(FILENAME = ‘C:\Program Files\Microsoft SQL Server\..\MSSQL\DATA\testdb.mdf’)

Note: testdb.mdf is the name of my database file. You need to select your file.


When MDF file is successfully attached to the SQL Server with missing transaction log file (LDF), you need to execute DBCC CHECKDB for a consistency check, i.e., for checking logical and physical integrity or errors in all the database components.

Likewise, the process to attach the database without transaction log using SQL Server Management Studio works only if the database is cleanly shutdown and healthy MDF exists.

Method #3: Using Stellar Repair for MS SQL

Since both the above-mentioned procedures work only if the master database file is in a healthy state, it is vital to Repair MDF file if an error occurs. Although, a missing log cannot affect the database to a large extent, yet if MDF becomes corrupt, then you will not be able to attach the database.

Stellar SQL Recovery Software processes the master database file and grants access to incorporated data. All the components of the SQL database can be repaired including tables, keys, rules, indexes, defaults, stored procedures, etc. The extensive recovery features include advanced algorithms that allow recovering deleted records from the uploaded file.

Apart from this, the SQL database repair tool allows saving the repaired database components in multiple file formats including XLS, HTML CSV as well as New Database or Live Database. 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 help of the tool, you can also gain access to database contents saved in the secondary database files.

Software testimonial by Daniel Calbimonte(SQL MVP)

Microsoft MVP Testimonial

The corrupt MDF can be repaired using the application by following three simple steps: Upload, Repair, and Save. Stellar Repair for MS SQL application supports all versions of the application, i.e., SQL Server 2019, 2017, 2016, 2014, 2012, 2008, 2008 (R2), etc. Once the master database file is repaired, you can use it to attach SQL database without transaction log-file using either SQL Server Management Studio or by executing a query in Transact-SQL.

  1. Ayman Malkawi May 26, 2019
  2. Patricia G. Torres July 19, 2018
    • Eric Simson July 21, 2018
  3. Karissa June 27, 2018
    • Eric Simson June 29, 2018
  4. Satya April 5, 2018
    • Priyanka Chauhan April 5, 2018
  5. Aaron Jillian January 11, 2018
    • Eric Simson January 12, 2018
  6. Jesse October 30, 2017
    • Eric Simson October 31, 2017
  7. Earl October 26, 2017
    • Eric Simson October 27, 2017
  8. Adam March 28, 2017
  9. Noah February 9, 2017
    • Jyoti Prakash February 9, 2017
  10. Jason January 12, 2017
  11. Hemant December 28, 2016
    • Jyoti Prakash December 30, 2016
  12. Nikolas December 8, 2016
  13. Sophia November 21, 2016
  14. Rob November 17, 2016
  15. Paula Diaz October 27, 2016

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.