MS SQL Server 2008 Attach Database .MDF File

In this tutorial I would like to summarize the steps a SQL Server administrator or a SQL developer should do in order to attach database file.
This step-by-step article provides screenshots which will help you to visualize the attach files tasks that you should work on.
I hope, you will find this SQL Server attach database guide helpful.

Create new or Restore SQL Server Database by Attaching MDF File

For the sample case, I would like to attach files which are the following SQL Server 2008 database files.
I have placed the .mdf files in a folder that I want to keep the SQL Server database files in it.
Later on this guide, we will point to those SQL Server databases files as source files for attach db.

SQL2008 Database Files

The simpliest method to attach a SQL Server database file, I suggest you if you can, attach the mdf file using the Microsoft SQL Server Management Studio.
First run the SQL Server Management Studio, then if it is not open, open the Object Explorer window by using the F8 short-cut and connect to the SQL Server instance that you want to attach the database files.
After the connection to the SQL Server database instance is set, expand the instance and right click on the Databases node.
On the context menu, select Attach....

Attach database file

The Attach Databases wizard dialog screen is displayed as shown below.
On the upper section which is the Databases to attach: section, click the Add button to add the database files to the list.

Attach databases screen - SQL Server attach database

Now as sql administrators or as sql programmers, you are ready to choose the required database files, .mdf data files to attach to the SQL Server instance as a new sql database.
In the Locate Database Files screen browse the folders that the database files are copied and select the .mdf file for the database to attach.

Locate database files

In this step, you have the chance to change the database file name and the owner of the database you are attaching to the related SQL Server instance.
In order to alter the database name, you should click on the "Attach As" column.
When you click on the column, the text identifying the database name becomes editable and you can give a name you wish for the database we are attaching to the database server.
As a similar process, if you click on the Owner column, a list of possible owners are listed in a dropdown where you can select one as the database owner.

Also you can see the database details, like primary, secondary or the log data files, names and file paths for the related database.

selected database files

After you have made your checks, you can click on the "OK" button to run the attach database process.
Or you can click on the Script button on the top of the Attach Databases wizard screen and export the t-sql script which can be used for attaching database files manually later.

Attach database t-sql script

Here is the AttachDB command which is used in MS SQL Server 2008.
As you see attaching a database file is implemented by running a Create Database t-sql command using the For Attach optional argument.
You can use the below t-sql script as a template for your case and run the script to attach database files to a SQL Server instance.


USE [master]

GO

CREATE DATABASE [eralperyilmaz] ON

( FILENAME = N'C:\SQLDatabases\eralperyilmaz.mdf' ),

( FILENAME = N'C:\SQLDatabases\eralperyilmazLOG.mdf' )

FOR ATTACH

GO

Code

After you have made your checks, if you are still using the SSMS you can click on the "OK" button to run the attach database process.
You can track the status of the process on the bottom-left section of the dialog screen.
You will see that the contents of the databases node on the Object Explorer window is refreshed automatically to reflect the change or the addition of the new database.

I hope this SQL Server attach database article will help you to attach database files to a SQL Server instance as a new database.