How to attach a database without log file (.ldf)?

Earily this morning, I was asked about how to attach a database without the lof file (.ldf). This may happen if someone drop the (.ldf) file incorrectly. For SQL Server 2005 or later, this can be eaisly done via both T-SQL or SQL Server Management Studio (SSMS).

T-SQL

USE master;
GO
-- Create a dummy database for testing
IF (DB_ID('TestDB')) IS NOT NULL
	DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB ON PRIMARY
(
	NAME = 'TestDB',
	FILENAME = 'E:\Workspace\Data\TestDB.mdf'
)
LOG ON
(
	NAME = 'TestDB_Log',
	FILENAME = 'E:\Workspace\Data\TestDB_log.ldf'
);
GO

-- Create a dummy table and populate some test data
USE TestDB;
GO
SELECT * INTO dbo.dbs FROM master.sys.databases
-- Check the table data
SELECT * FROM dbo.dbs

-- Set the database offline and delete the ldf file manually from the OS
USE master;
ALTER DATABASE TestDB SET OFFLINE;
GO

-- In a real world, we may start from here (only mdf file left)
IF (DB_ID('TestDB')) IS NOT NULL
	DROP DATABASE TestDB;
GO
-- This is how it works - use CREATE DATABASE ... FOR ATTACH or ATTACH_REBUILD_LOG
-- For more information, check: http://msdn.microsoft.com/en-us/library/ms176061.aspx
CREATE DATABASE TestDB ON
(FILENAME = N'E:\Workspace\Data\TestDB.mdf')
FOR ATTACH
GO
-- OR
--CREATE DATABASE TestDB ON
--(FILENAME = N'E:\Workspace\Data\TestDB.mdf')
--FOR ATTACH_REBUILD_LOG
--GO

-- After attached, check the table data again
USE TestDB;
SELECT * FROM dbo.dbs;

-- Drop the dummy database
USE master;
DROP DATABASE TestDB;
GO

SSMS

image

Update: As a side note, if the database is not clean shundown, and the ldf file is lost, the Database Engine may not be able to re-create the log file in this case. In that case, there is a undocument option may work (ATTACH_FORCE_REBUILD_LOG). However, you may have data lost after the database created.

Hope it helps.

Advertisements

About Alex Feng

SQL Server DBA in Alibaba Cloud Computing
This entry was posted in Database Engine, Detach & Attach and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s