How to Create a SQL server database with .MDF file only?

Are you new in SQL server and trying to create a new database for test or study purpose? Then you can do this with the help of mdf file only. MDF file is the primary database file of the SQL server. It stores all the user data & files. MS SQL server offers sample databases for the test purpose which available at MicrosoftSQL Server Product Samples &Database website.

For your referrence I have downloaded the AdventureWorks2008R2database from the codeplex website. This database has mdf file only.

Attach the database

You can attach the mdf file without ldf file by two methods:

1. Through SQL command
2. Through SQL server management studio

I will show you both the methods; firstly through SQL command.

Run below SQL command to attach the mdf file:

CREATE DATABASE YourDatabaseName ON

( FILENAME = N'C:\DATA\YourDatabaseName.mdf')

FOR ATTACH_REBUILD LOG;

GO

Above command will attempt to attach the database file and create an empty log file while attaching the database.

Note: Create a backup of your database to minimize the unavailability time of the database. As you are new in the SQL server so it may possible that you will perform a wrong command that makes your database unavailable.

Attach through SQL Server Management Studio: Perform below steps to attach a mdf file without ldf file using SQL server management studio.

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.
4. Click on the Add button from appeared dialog box.
5. Now a Locate Database Files dialog box will be appeared.
6. Click on the browse button to select your database MDF file after choosing .mdf file click on the OK button.
7. Now you will see your mdf file is selected but SQL server is unable to select your transaction log file because transaction log file is missing.
8. To attach .mdf file without transaction log file; select the log file and click on the remove button to remove it.
9. Now click on the OK button to attach the database file.
10. SQL server will create a transaction log file for you while attaching the database.

Suggestion: Make a habit to run DBCC CHECKDB command on your database. It will check and report the error message. If it reports any error message then re-run it with proper repair clause to repair the database.