• Article

How to: Move a Database Using Detach and Attach (Transact-SQL)

Important

We recommend that you move databases by using the ALTER DATABASE planned relocation procedure, instead of using detach and attach. For more information, see ALTER DATABASE (Transact-SQL).

You can move a detached database to another location and re-attach it to the same or a different server instance.

Security noteSecurity Note

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.

When you attach a SQL Server 2005 database that contains full-text catalog files onto a SQL Server 2008 server instance, the catalog files are attached from their previous location along with the other database files, the same as in SQL Server 2005. For more information, see Full-Text Search Upgrade. When you detach an upgraded SQL Server 2005 database that contains full-text catalog files from a SQL Server 2008 server instance, the catalog files are kept with the other database files, the same as in SQL Server 2005. 

Note

You can also use detach and attach to upgrade a SQL Server 2000 or SQL Server 2005 database to SQL Server 2008. For more information, see How to: Upgrade a Database Using Detach and Attach (Transact-SQL).

Procedure

To copy a database using detach and attach

  1. Detach the database by using the sp_detach_db stored procedure.

  2. In a Windows Explorer or Windows Command Prompt window, move the detached database file or files and log file or files to the new location.

    Note

    To move a single-file database, you can use email if the file size is small enough for email to accommodate.

    You should move the log files, even if you intend to create new log files. In some cases, reattaching a database requires its existing log files. Therefore, always keep all the detached log files until the database has been successfully attached without them.

    Note

    If you try to attach the database without specifying the log file, the attach operation will look for the log file in its original location. If a copy of the log still exists in the original location, that copy is attached. To avoid using the original log file, either specify the path of the new log file or remove the original copy of the log file (after copying it to the new location).

  3. Attach the copied files using the CREATE DATABASE Transact-SQL statement with a FOR ATTACH [ WITH <service_broker_option> ] clause or a FOR ATTACH_REBUILD_LOG clause (for more information, see CREATE DATABASE (Transact-SQL)). Each database contains a unique identifier used for routing Service Broker messages to that database. If the database uses Service Broker, see Managing Service Broker Identities.

    Note

    For information on attaching a database by using Object Explorer, see How to: Attach a Database (SQL Server Management Studio).

Example

The following example creates a copy of the AdventureWorks2008R2 database named MyAdventureWorks. The Transact-SQL statements are executed in a Query Editor window that is connected to the server instance to which AdventureWorks2008R2 is attached.

  1. Detach the AdventureWorks2008R2 database by executing the following Transact-SQL statements:

    USE master;
GO
EXEC sp_detach_db @dbname = N'AdventureWorks2008R2';
GO

  2. Using the method of your choice, copy the database files (AdventureWorks2008R2_Data.mdf and AdventureWorks2008R2_log) to: C:\MySQLServer\AdventureWorks2008R2_Data.mdf and C:\MySQLServer\AdventureWorks2008R2_Log.ldf, respectively.

    Important

    For a production database, place the database and transaction log on separate disks.

    To copy files over the network to a disk on a remote computer, use the universal naming convention (UNC) name of the remote location. A UNC name takes the form **\\Servername\Sharename\Path\**Filename. As with writing files to the local hard disk, the appropriate permissions that are required to read or write to a file on the remote disk must be granted to the user account used by the instance of SQL Server.

  3. Attach the moved database and, optionally, its log by executing the following Transact-SQL statements:

    USE master;
GO
CREATE DATABASE MyAdventureWorks 
    ON (FILENAME = 'C:\MySQLServer\AdventureWorks2008R2_Data.mdf'),
    (FILENAME = 'C:\MySQLServer\AdventureWorks2008R2_Log.ldf')
    FOR ATTACH;
GO

    Note

    In SQL Server Management Studio, a newly attached database is not immediately visible in Object Explorer. To view the database, in Object Explorer, click View, and then Refresh. When the Databases node is expanded in Object Explorer, the newly attached database now appears in the list of databases.