Saturday16 December 2017

 

I have lost the transaction log file. How can I attach the database?

 

More often than not, this has been a case with a vast majority of SQL Server users. Sometimes, you need to take your database offline for reasons, such as disk clean up and other type of maintenance. At that point of time, if you accidentally end up deleting your transaction log for the database and have no valid backups in place, you cannot attach your database simply as before. The transaction log files may also go missing due to hardware failures and similar reasons. To attach your MDF file to the server and retrieve back everything intact, you can follow the below mentioned procedure.

NOTE: The procedure won't work if you are downgrading to a lower version of SQL Server. You can use this workaround for SQL Server 2005 and higher versions.

  • Open SQL Server Management Studio and connect to the SQL Server instance to which you need to attach the MDF file.
  • Navigate to your Object Explorer on the left, right-click 'Databases', and then click 'Attach' in the dropdown menu. You will see an 'Attach Databases' dialog on your screen.
  • In the displayed dialog box, click 'Add'. This will open up the 'Locate Database Files' dialog. In this dialog box, browse to locate the MDF file that corresponds to your database and click 'OK'.
  • In the 'Attach Databases' dialog box, you will see the message 'Not Found' for the respective LDF file entry shown under 'database details' section.
  • Select the LDF file in the dialog and then click 'Remove'. Finally, click 'OK' for attaching the database without the log file.

After successful completion of the process, SQL Server will create a new transaction log and store it inside the same folder where your data file is kept. Once you are finished with this, check the integrity of your database by running DBCC CHECKDB. You can also use this procedure, if your database has more than one data file.

An alternative way to do so is using the CREATE DATABASE with ATTACH option as follows:

USE [master]
GO
CREATE DATABASE [Databasename] ON
( FILENAME = N'MDFFilePath' )
FOR ATTACH
GO

Here, 'MDFFilePath' is the full path of the MDF file that you need to attach.