How to attach MS SQL Server database (mdf file) without corresponding log (ldf) file

When attaching SQL Server database, you should have database file (mdf) and transaction log file (ldf). If transaction log file is missing, you won’t be able to attach database through SQL Server Management Studio.

You can, however, attach database with mdf only file using a system stored procedure. This will only work if database you are trying to attach has been shut down gracefully, without an abrupt shutdown (power outage, server crashes, etc.). In this case SQL Server will try to recreate ldf file, and if successful it will attach the database.

Use the following system stored procedure call to attach mdf file.

USE [master]
GO
EXEC sp_attach_single_file_db @dbname='MyDatabase',
@physname=N'C:\FileLocationPath\MyDatabase.mdf'
GO

If SQL Server was successful in attaching the database, you should see the following message in messages output tab:

File activation failure. The physical file name "C:\OriginalLDFFilePath\MyDatabase.ldf" may be incorrect.
New log file 'C:\FileLocationPath\MyDatabase.ldf' was created.

Refresh databases list in SQL Server Management Studio, and newly attached database will be on the list.

Advertisements

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