Attach MDF file without LDF file in SQL Server

attachAttach MDF file without LDF file in SQL Server

I mostly use sample database AdventureWorks to demonstrate my articles which is freely provided by Micrsoft, you can have it from CodePlex site. CodePlex site provides only .MDF file for database without transaction log file .LDF.

Recently one of the regular blog reader asked me in chat that how he can attach .MDF file since he doesn’t have .LDF file, is it bug in CodePlex site or what?

Well this is not a bug or issue in CodePlex site, you can surely attach MDF file without LDF file. I sent him following command which can create database for you without LDF file.

USE [MASTER]
GO

CREATE DATABASE [AdventureWorksDW2012] ON
( FILENAME = N'D:\Databases\AdventureWorksDW2012_Data.mdf' )
FOR ATTACH_REBUILD_LOG
GO

As soon as I sent him above command, he came to me again with error. Here is the error he has encountered.

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2012_log.ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'AdventureWorksDW2012'. CREATE DATABASE is aborted.

Well this is really misleading error message. He has kept his MDF file in “D:\Databases” folder so I told him to check whether SQL Server Service login has permission to access “Databases” folder or not as this is permission issue. As soon as he has provided proper permission on Databases folder, he was able to execute “CREATE DATABASE” command I have provided. He got succeed and got the following success message.

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2012_log.ldf" may be incorrect.
New log file 'D:\Databases\AdventureWorksDW2012_log.ldf' was created.

This is how you can attach MDF file without LDF file in SQL Server.

If you like this article, do like “Extreme-Advicepage in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

2 thoughts on “Attach MDF file without LDF file in SQL Server

  1. markwillium

    Hi Ritesh,

    We can also attach mdf file without ldf file using SQL server management studio (SSMS). To do this, we have to follow below steps:

    1. Connect to the SQL server instance using SSMS
    2. From the Object Explorer, right click the Databases node and a drop-down menu will be appeared.
    3. Now click on the Attach tab and then Add button.
    4. Now a Locate Database Files dialog box will be appeared.
    5. Click on the browse button to select your database MDF file after choosing .mdf file click on the OK button.
    6. Now you will see your mdf file is selected but SQL server is unable to select your transaction log file because transaction log file was deleted. To attach .mdf file without transaction log file; select the log file and click on the remove button to remove it. Now click on the OK button to attach the database file. SQL server will create a transaction log file for you while attaching the database.

    Complete article with screenshots is available here: Article link

Comments are closed.