Introduction

In SQL Server, the transaction log file is essential for keeping your database reliable and durable. It tracks every change and transaction, which is crucial for recovering data if something goes wrong. Unfortunately, there are times when this log file might go missing due to accidental deletion, a sudden shutdown, or a system crash. Recently, a developer reached out to me with a tricky situation: they only had the master data file (MDF) of a database, without the transaction log or any backup. In this guide, I’ll walk you through how to attach a database in this situation, covering the necessary steps and important considerations.

Problem

A developer approached me with an MDF file of a SQL Server database which they wanted to restore. This person only had the MDF file for the database and no transaction log file, also they didn't have a backup of the database. In this tip, we will cover how to attach a database that does not have its transaction log files.

Solution

In this tip, we will go through the steps which need to be performed to recover a database when you only have the MDF file of a SQL Server database. The below steps work in for SQL Server 2005 and later.

Using SQL Server Management Studio - Attach a SQL Server Database without a Transactional Log File

1. Connect to SQL Server instance using SQL Server Management Studio

2. In Object Explorer, right click Databases node and select "Attach" option from the drop down list as shown in the snippet below.

3. This will open an Attach Databases window as shown in the below snippet.

4. Attach Databases window click the "Add" button as shown above to open the Locate Database Files window as shown below.

5. In Locate Databases Files window you need to browse and locate the MDF file which is relevant to the database you want to attach and then click OK.  In our example I am using the "ProductsDB.mdf" file.

6. In the Attach Databases window; you will see that SQL Server informs us that the log file is not found, in our case the missing file is "ProductsDB_Log.LDF". In order to attach the database without the ProductsDB_Log.LDF transaction log file you need to select the file as highlighted in the below snippet and click the "Remove" button. It is possible that the log file will not be listed with the "Not Found" message, so the remove step can be ignored. Finally click OK to attach the ProductsDB database without the transaction log file.

7. That's it you have successfully attached a database without using the transaction log file.  SQL Server will create a new transaction log file in the same directory as the MDF file. This new log file typically starts small, often with a default size of 1 MB, and can grow dynamically based on the volume of transactions performed on the database.

After successfully attaching the database, SQL Server automatically generates a new transaction log file. This log file is created in the default log file location.

The initial size is generally set to 1 MB, but it can grow dynamically based on database activity. This growth is crucial for maintaining database integrity and recovery capabilities moving forward.

Using T-SQL - Attach a SQL Server Database without a Transaction Log File

DBAs can also attach a SQL Server database without a transaction log file using the below T-SQL code.

In this first script we are using the CREATE DATABASE with ATTACH option and specifying the name and location of the mdf file that we have for this database.

USE [master]
GO
CREATE DATABASE [ProductsDB] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB.mdf' )
FOR ATTACH
GO

Example of a Log File Creation Message

Once the above T-SQL code has executed successfully, you will receive a message informing you that SQL Server has created a new transaction log file for the database. Below is an example of the message you might see:

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB_log.ldf" may be incorrect.

New log file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB_log.LDF' was created.

Checking the Logical and Physical Integrity of the Database

DBAs can check the logical and physical integrity of all objects in the database by running the DBCC CHECKDB command. This command verifies that the database is structurally sound, checking for issues such as corruption or inconsistencies in data. Running DBCC CHECKDB is essential for maintaining database integrity, as it helps identify and rectify potential problems before they lead to data loss or application failures. Here we are using the "ProductsDB" database:

DBCC CHECKDB ('ProductsDB')
GO

Next Steps

• If your database consists of multiple data files, this method can also be utilized to generate a new transaction log file. Additionally, if your database had multiple transaction log files, consider using the ATTACH_REBUILD_LOG option with the CREATE DATABASE command.
• If you loose one of your data files for a database you can not use this approach.  This will only rebuild a new transaction log file for you it does not recreate data files.