Two Ways to Attach SQL Server Database without Transaction Log

Procedure to Attach Database without Transaction Log

A few days back one of my colleagues contacted me in order to restore a SQL database on the server. The colleague was in possession of a MDF file, but unfortunately did not have a LDF file.The associated LDF file of the database was deleted accidentally during a disk cleanup process and hence the database was to be added without the LDF file.

In order to attach database without the transaction log file,we will follow the procedure mentioned in the next section.

Steps For Attaching The SQL Database In Absence Of LDF File

In this section, we will discuss all the steps that are required to perform the process of attaching a SQL database when you are in possession of only the MDF file and have no LDF file.The database that we need to attach to SQL server is Kacy1DB having KacyDB1.mdf file.The process can be performed by two methods: Using SQL Server Management Studio or by using T-SQL.

Using SQL Server Management Studio

Follow the below mentioned steps to attach the SQL server database:

1. Connect to SQL Server 2008 by SQL Server Management Studio.
2. In Object Explorer Tab, right click on Databases.
   
3. Select Attach from the drop down menu.
   
4. An Attach Databases window will open up in front of you.
5. Select Add option to open the Locate Database Files window.
   
6. In the Locate Database Files window, browse and locate the MDF file that belongs to the database i.e. KacyDB1.ldf that you want to attach to the database. Click OK.
   
7. In the Attach Databases window, information will be displayed that the LDF file has not been found. The missing file is KacyDB1.ldf in our case.To attach the database without the log file, select the KacyDB1.ldf file andclick on Remove option.
   

Thus, the database will be attached to the SQL server without the LDF file. The server will create a new LDF file and it will be stored in the same folder as the other database files.

Attach database with T-SQL

One can also add a SQL database to the server without the transaction file with the help of T-SQL codes.

1. The first step is to create a script in which we will use CREATE DATABASE with ATTACH option. The name and location of the MDF file that we have will also be specified.
   USE [master]
GO
CREATE DATABASE [KacyDB1] ON
( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\ProductsDB.mdf’)
FOR ATTACH
GO
2. As soon as the above code gets executed successfully, the following message will be displayed that shows that the server has created a new transaction file for KacyDB1 database.

File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\KacyDB1s_log.ldf” may be incorrect.
New log file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\KacyDB1.LDF’ was created.

You can check the logical and physical integrity of the objects by executing a DBCC CHECKDB script on the database.

DBCC CHSCKDB (‘KacyDB1’)
GO

Thus, you can attach the database to SQL server with the help of T-SQL scripting as well.

Note: The above method is applicable for SQL Server 2005 and SQL Server 2008 versions.