• Article

How to: Attach a Database (SQL Server Management Studio)

Attaching a database places it in exactly the same state that it was in when it was detached.

This topic contains the following sections:

  • Security

  • Permissions

  • Procedure (SQL Server Management Studio)

Security

File access permissions are set during a number of database operations, including detaching or attaching a database. For information about file permissions that are set whenever a database is detached and attached, see Securing Data and Log Files.

We recommend that you do not attach or restore databases from unknown or untrusted sources. Such databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database.

Important

For more information about attaching databases and information about changes that are made to metadata when you attach a database, see Detaching and Attaching Databases.

Permissions

For information about the permissions required for attaching a database, see CREATE DATABASE (Transact-SQL).

[Top]

Procedure (SQL Server Management Studio)

To attach a database

  1. In SQL Server Management Studio Object Explorer, connect to an instance of the Microsoft SQL Server Database Engine, and then expand that instance.

  2. Right-click Databases and click Attach.

  3. In the Attach Databases dialog box, to specify the database to be attached, click Add; and in the Locate Database Files dialog box, select the disk drive where the database resides and expand the directory tree to find and select the .mdf file of the database; for example:

    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Data.mdf

    Important

    Trying to select a database that is already attached generates an error.

  4. Optionally, to specify a different name for the database to attach as, enter the name in the Attach as column of the Attach Databases dialog box.

  5. Optionally, change the owner of the database by selecting a different entry in the Owner column.

  6. When you are ready to attach the database, click OK.

    Note

    A newly attached database is not displayed in the Databases node of Object Explorer until the view is refreshed. To refresh the view at any time, click in Object Explorer, and then click Refresh on the View menu.