How to force attach SQL Server 2014 Database without Transaction Log

6:54 AM 0 Comments

You are unable to attach an SQL Server Database without the transaction log via SQL Server Management Studio. SQL is looking for the transaction log and fail with the following error

File activation failure. The physical file name "E:\MSSQL\Log\PNRDataFeed_log.LDF" may be incorrect.
New log file 'E:\MSSQL\Log\PNRDataFeed_log.ldf' was created.

To force the creation of the transaction, there is an undocumented option ATTACH_FORCE_REBUILD_LOG

 

Attach the database via Management Studio and script the command. You will get something like this. If there is any ndf, SQL will include them

 

USE [master]
GO
CREATE DATABASE [PNRDataFeed] ON
( FILENAME = N'E:\MSSQL\Data\PNRData.mdf' ),
( FILENAME = N'E:\MSSQL\Data\eTicketArchives.ndf' )
  FOR ATTACH
GO

 

Change FOR ATTACH to FOR ATTACH_FORCE_REBUILD_LOG

 

USE [master]
GO
CREATE DATABASE [PNRDataFeed] ON
( FILENAME = N'E:\MSSQL\Data\PNRData.mdf' ),
( FILENAME = N'E:\MSSQL\Data\eTicketArchives.ndf' )
  FOR ATTACH_FORCE_REBUILD_LOG

 

Hope it helps

0 comments: