How to Attach Database Without a Transaction Log File in SQL Server

Introduction

This article demonstrates how to attach a database in SQL Server when transaction log file is missing. The steps mentioned in this article are applicable on SQL Server 2005 and higher versions. To know more about how to Attach Database in SQL Server with transaction log file refer the following article “Attach Database in SQL Server”.

Different ways to Attach Database without a transaction log file in SQL Server

• Attach Database without a transaction log file in SQL Server Using SQL Server Management Studio (SSMS)
• Attach Database without a transaction log file in SQL Server Using TSQL Script

Permissions to Attach Database in SQL Server

You need membership in the db_owner fixed database role to Attach Database in SQL Server.

Important Notes:

You will not be able to detach and attach database from a higher version of SQL Server to a lower version of SQL Server this is by design from Microsoft. To know more read the following article “Why an SQL Server Database from a higher version cannot be attached onto a lower version of SQL Server?”

Downgrade of a database from higher version to lower version cannot be achieved using Database Backup and Restore Method and also by using Database Detach and Attach Method. However, you can downgrade a database to a lower version using SQL Server Integration Services. To know more read "How to Downgrade SQL Server Database Using SQL Server Integration Services".

Let us go through each of the above mentioned options in detail.

Attach Database without a transaction log file in SQL Server Using SQL Server Management Studio (SSMS)

1. In Object Explorer, right click the Databases node and then click Attach… from the drop down menu as shown in the clip below.


2. In Attach Databases dialog box, click Add… button.


3. In Locate Database Files dialog box, browse to the location where you database MDF file is available and then choose the file and click OK and to return to parent Attach Databases dialog box.


4. In Attach Database dialog box; you will see under database details as highlighted that SQL Server is unable to locate the LDF file. To attach the database without the LDF file select the LDF file and then click remove button. Finally, click OK to successfully attach the database. In this case SQL Server will create a new log file while attaching the database.

Important Note: -The above mentioned steps will work only when your database which was cleanly shutdown and you have the .MDF file of the database.

---

Attach Database without a transaction log file in SQL Server Using TSQL Script

Using the below script you can attach database in SQL Server which has a missing transaction log file.

USE [master]
GO

CREATE DATABASE [AdventureWorksDW2008R2] ON 
 ( FILENAME = N'D:\Downloads\SQL2008R2SampleDBs\AdventureWorksDW2008R2_Data.mdf' )
FOR ATTACH
GO

---

Execute DBCC CHECKDB

Once you have successfully attached a MDF file without a transactional log (LDF) file go ahead and execute DBCC CHECKDB to check the logical and physical integerity of all the objects in the specified database.

Use MyTechMantra
GO

DBCC CHECKDB
GO

Reference:- Database Detach and Attach (SQL Server)