Attach MDF without LDF in SQL Server

You must have observed that the sample database adventureworks comes only with a mdf file as downloadable. Se let’s see how can we attach a data file without the log file/s? Mostly when you are on the latest version of SSMS and you try to attach a mdf then the “FOR ATTACH” command gets auto generated but anyway lets have a look at all possibilities. Just a note, if you have had multiple log files for a database then you won’t be able to rebuild all ldfs except one to start the database.


USE [master]
GO
CREATE DATABASE [AdventureWorks2012] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\AdventureWorks2012_Data.mdf' )
 FOR ATTACH
GO

USE [master] 
GO
EXEC sp_attach_single_file_db @dbname='AdventureWorks2012',
@physname=N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\AdventureWorks2012_Data.mdf'
GO

CREATE DATABASE AdventureWorks2012 ON
(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\AdventureWorks2012_Data.mdf')
FOR ATTACH_REBUILD_LOG
GO