SQL Database Repair

3 Methods to Attach SQL Database without Transaction Log File


Table of Content

    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.

    Read full summary

    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.

    1. Using SQL Server Management Studio in SQL Server
    2. Using T-SQL Script
    3. 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.

    Attach Option
    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.

    Add Database
    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.

    Select MDF File
    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.

    Remove Log File Option in SSMS
    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.

    Stellar

    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?

    No NO

    About The Author

    Bharat Bhushan linkdin

    Bharat Bhushan is an experienced technical Marketer working at Stellar Data Recovery - expertise in data care. He is skilled in Microsoft Exchange Database, MSSQL Database troubleshooting & data warehousing. He is a Management Post Graduate having a strong grip in Technology & certified in SAP-SD, Oracle 10g & Informatica Powercenter 9.1.

    24 comments

    1. 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.

    2. 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.

      1. 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.

    3. 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.

    4. I purchased your SQL recovery software after getting the complete preview of my database in demo version. Thanks you for this wonderful utility..

    Leave a comment

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

    Image Captcha
    Refresh Image Captcha

    Enter Captcha Here :

    Related Posts

    WHY STELLAR® IS GLOBAL LEADER

    Why Choose Stellar?

    • 0M+

      Customers

    • 0+

      Years of Excellence

    • 0+

      R&D Engineers

    • 0+

      Countries

    • 0+

      PARTNERS

    • 0+

      Awards Received