Opening a database only using the mdf file

I love to improve my knowledge and skills, especially in the profession I work in. In every day work, we come across to some problems and solve them. Therefore, I thought it would be worthwhile to share my experiences with you, hope you find them helpful.
This is my first post. I’m going to talk about attaching a database using only the .mdf file.

Attach mdf file without ldf file in Database
When you have both mdf and ldf files of a database, attaching the database would be very easy, but if we have only the mdf file and the log file is missing, it would not be as straight forward as using the SQL Server management studio visual interface. You need to write SQL script to do that.
First let see what would happen if we try to attach the database by mdf file from SQL Server Management studio:

After selecting the file, it opens two entries in the grid below, one for mdf file and the other ldf file that is missing:



After clicking ok, the error below would be displayed:



By clicking on the referred hyperlink the error details shows the issue as expected. The ldf file is missing:


Solution:
The script below will do the job easily, only you need to copy the mdf file to the DATA folder that the other SQL Server db files are stored. In my case the database name AdventureWorks2008R2 and it is stored in the following folder:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA

We use CREATE DATABASE syntax using “FOR ATTACH_REBUILD_LOG” option. This command will create the database based on the mdf file and will rebuild the missing ldf file.


CREATE DATABASE AdventureWorks2008R2 ON
(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2.mdf')
FOR ATTACH_REBUILD_LOG
GO


Pressing F5 and the database will be created easily :)