Wednesday, 16 May 2018

Steps to Attach a SQL Server database without Transaction Log File

Simple approach to attach a SQL Server Database without transaction log file using either SQL Server Management Studio (SSMS) and T-SQL script or third party SQL Database Recovery tool.
Microsoft SQL Server is one of the widely used database platforms in the world. Master database file (MDF) and Log transaction files (LDF) are considered as the two main pillars files used by SQL Server for preserving the content of the database.

Microsoft SQL Server is also believed to be an extremely popular and affordable database engine which is very fast and stable. It has the ability to quickly create, edit, and delete all database objects, or execute SQL queries and scripts easily.

While working with SQL Server database if you are getting an error message while attempting to attach SQL Server database files, it means your SQL Server database files have some corruption issues. This article demonstrates how to attach a database to SQL Server when transaction log file is missing.

How to Attach MDF file Without LDF File?

Generally, there are three methods which are used to attach SQL MDF file without the LOG file. Let’s see some of these methods and how they work:

  1. By using SQL Server Management Studio (SSMS)
  2. By using T-SQL script
  3. Using Aryson SQL Database Repair Software

Method #1: Attach MDF File without LDF file by Using SSMS: You can attach your SQL MDF file without LOG file by following underlying steps.

Open MS SQL Management Studio

In Object Explorer, Click Right on Databases and then Press on Attach from the drop-down menu

In Attach Databases Windows, Tick on Add button

Browse the setting of your MDF File in Locate Database Files, then select the file and click on OK button.

In the dialogue box of the database you will see all the details of Attach Database; Choose the LDF file first and then Click Remove Button. Now Click on OK to attach MDF file without LDF file. While attaching the database, SQL Server will create a new LDF file.

Now the problem is resolved and you can check your files in the databases folder.

Method #2: Attach MDF File without LDF file by using T-SQL script: You can also run a TSQL Script on SQL Query to attach your MDF file and recreate your transaction log file.

  • Where, test DB is the name of your database.
  • Now you can check your database in the database folder.

NOTE:  All these methods will work only if all your database is effectively shut down and you have a proper MDF file setting in the database.

Method #3: Using SQL Database Repair After using the above-mentioned method if you still feel that your problems are not shorted out then SQL Database Recovery Software is the best option. Using this tool you can easily solve all the problem related to your Attach Database without a Transaction Log File in SQL Server.

Conclusion: In this Blog, I have explored three best methods that are available for resolving all your quarries related to your SQL SERVER. Additionally, with this third party SQL Database Recovery Tool you can easily and effectively solve you're attach MDF file without LDF file. All the methods are good but if you want faster result then you can go with third party application .