SQL SERVER – Attach mdf file without ldf file in Database

Posted on Tuesday, October 13, 2015 | 2 Comments

USE [master]
GO
-- Method 1: I use this method
EXEC sp_attach_single_file_db @dbname='TestDb',
@physname=N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf'
GO

-- Method 2:

CREATE DATABASE TestDb ON
(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf')
FOR ATTACH_REBUILD_LOG
GO

-- Method 3:
CREATE DATABASE TestDb ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf')
FOR ATTACH
GO

Source : http://blog.sqlauthority.com/2010/04/26/sql-server-attach-mdf-file-without-ldf-file-in-database/

Exception case when fail to attach database and get such error like

"The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure."

Attaching the Damaged SQL Server Database

USE [master]
GO

CREATE DATABASE [TestDB_Repair]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'TestDB_Repair_file1',
   FILENAME = N'E:\MSSQL\TestDB_Repair_1.mdf',
   SIZE = 8MB ,
   MAXSIZE = UNLIMITED,
   FILEGROWTH = 64MB)
LOG ON
( NAME = N'TestDB_Repair_log_file1',
    FILENAME = N'E:\MSSQL\TestDB_Repair_1.ldf',
    SIZE = 8MB,
    MAXSIZE = 2048GB,
    FILEGROWTH = 32MB)

Now we set the database offline.

USE master
GO

ALTER DATABASE [TestDB_Repair] SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

At this point we can change the file location of our new database to point to our orphaned mdf file and set the location of the log file to a non-existent file.

USE master
GO

ALTER DATABASE [TestDB_Repair] MODIFY FILE(NAME='TestDB_Repair_file1', FILENAME= 'E:\MSSQL\TestDBCopy.mdf')
ALTER DATABASE [TestDB_Repair] MODIFY FILE(NAME='TestDB_Repair_log_file1', FILENAME= 'E:\MSSQL\TestDBCopy.ldf')
GO
Let’s bring the database back online.

USE master
GO

ALTER DATABASE [TestDB_Repair] SET ONLINE
GO

Rebuilding the SQL Server Transaction Log

USE master
GO

DBCC TRACEON(3604)
GO

ALTER DATABASE TestDB_Repair SET EMERGENCY
GO

ALTER DATABASE TestDB_Repair SET SINGLE_USER
GO

DBCC CHECKDB('TestDB_Repair', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS
GO

ALTER DATABASE TestDB_Repair SET MULTI_USER
GO