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.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.