Detaching and Attaching Databases

The data and transaction log files of a database can be detached and then reattached to the same or another instance of SQL Server. Detaching and attaching a database is useful if you want to change the database to a different instance of SQL Server on the same computer or to move the database.

Note

The SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments. Therefore, attach works across 32-bit and 64-bit environments. A database detached from a server instance running in one environment can be attached on a server instance that runs in another environment.

Note

For information about file permissions that are set whenever a database is detached and attached, see Securing Data and Log Files.

Detaching a Database

Detaching a database removes it from the instance of SQL Server but leaves the database intact within its data files and transaction log files. These files can then be used to attach the database to any instance of SQL Server, including the server from which the database was detached.

You cannot detach a database if any of the following are true:

• The database is replicated and published. If replicated, the database must be unpublished. Before you can detach it, you must disable publishing by running sp_replicationdboption.

  Note

  If you cannot use sp_replicationdboption, you can remove replication by running sp_removedbreplication.

• A database snapshot exists on the database.
  Before you can detach the database, you must drop all of its snapshots. For more information, see How to: Drop a Database Snapshot (Transact-SQL).

  Note

  A database snapshot cannot be detached or attached.

• The database is being mirrored in a database mirroring session.
  The database cannot be detached unless the session is terminated. For more information, see Removing Database Mirroring.

• The database is suspect. In SQL Server 2005, a suspect database cannot be detached; before you can detach it, you must put it into emergency mode. For more information about how to put a database into emergency mode, see ALTER DATABASE (Transact-SQL).

• The database is a system database.

Detaching a database clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. In SQL Server 2005 Service Pack 2, for each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". This message is logged every five minutes as long as the cache is flushed within that time interval.

To detach a database

• sp_detach_db (Transact-SQL)
• SQL Server Management Studio

Backup and Restore and Detach

Detaching a read-only database loses information about the differential bases of differential backups. For more information, see Backing Up Read-Only Databases.

Responding to Detach Errors

Errors produced while detaching a database can prevent the database from closing cleanly and the transaction log from being rebuilt. If you receive an error message, perform the following corrective actions:

1. Reattach all files associated with the database, not just the primary file.
2. Resolve the problem that caused the error message.
3. Detach the database again.

Attaching a Database

You can attach a copied or detached SQL Server database. In SQL Server 2005, full-text files that are part of a database are attached with the database. For more information, see Attach and Detach Full-Text Catalogs.

Security 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.

On attach, database startup occurs. Generally, attaching a database places it in the same state that it was in when it was detached or copied. However, in SQL Server 2005, attach-and-detach operations both disable cross-database ownership chaining for the database. For information about how to enable chaining, see cross db ownership chaining Option. Also, TRUSTWORTHY is set to OFF whenever the database is attached. For information about how to set TRUSTWORTHY to ON, see ALTER DATABASE (Transact-SQL).

When you attach a database, all data files (MDF and NDF files) must be available. If any data file has a different path from when the database was first created or last attached, you must specify the current path of the file.

Note

If the primary data file being attached is read-only, the Database Engine assumes that the database is read-only.

When an encrypted database is first attached to an instance of SQL Server, the database owner must open the master key of the database by executing the following statement: OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'. We recommend that you enable automatic decryption of the master key by executing the following statement: ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY. For more information, see CREATE MASTER KEY (Transact-SQL) and ALTER MASTER KEY (Transact-SQL).

The requirement for attaching log files depends partly on whether the database is read-write or read-only, as follows:

• For a read-write database, you can usually attach a log file in a new location. However, in some cases, reattaching a database requires its existing log files. Therefore, it is important to always keep all the detached log files until the database has been successfully attached without them.
  If a read-write database has a single log file and you do not specify a new location for the log file, the attach operation looks in the old location for the file. If it is found, the old log file is used, regardless of whether the database was shut down cleanly. However, if the old log file is not found and if the database was shut down cleanly and has no active log chain, the attach operation attempts to build a new log file for the database. For more information, see Understanding Transaction Log Architecture.

• If the primary data file being attached is read-only, the Database Engine assumes that the database is read-only. For a read-only database, the log file or files must be available at the location specified in the primary file of the database. A new log file cannot be built because SQL Server cannot update the log location stored in the primary file.

  Important

  When a read-only database is detached and then reattached, the differential base information is lost. This causes the master database to become unsynchronized with the read-only database. Differential backups taken after this may provide unexpected results. Therefore, if you are using differential backups with a read-only database, after you reattach the database, you should establish a current differential base by taking a full backup.

Backup and Restore and Attach

Like any database that is fully or partially offline, a database with restoring files cannot be attached. If you stop the restore sequence, you can attach the database. Then, you can restart the restore sequence.

Attaching a Database to Another Server Instance

When you attach a database onto another server instance, to provide a consistent experience to users and applications, you might have to re-create some or all of the metadata for the database, such as logins and jobs, on the other server instance. For more information, see Managing Metadata When Making a Database Available on Another Server Instance.

Note

A database created by a more recent version of SQL Server cannot be attached in earlier versions.

Note

Attach works correctly with the vardecimal storage format. But each Database Engine must be upgraded to at least SQL Server 2005 Service Pack 2, and all related databases must be enabled for vardecimal storage format. For example, you cannot attach a service pack 2 database that has vardecimal storage format enabled to an earlier version of SQL Server. For more information about vardecimal storage format, see Storing Decimal Data As Variable Length.

To attach a database

• CREATE DATABASE (Transact-SQL)
• How to: Attach a Database (SQL Server Management Studio)

To Upgrade a Database from an Earlier Version of SQL Server

In SQL Server 2005, you can use detach and attach operations to upgrade a user database from SQL Server version 7.0 or SQL Server 2000. However, the following restrictions apply:

• Copies of the master, model or msdb database created using SQL Server 7.0 or SQL Server 2000 cannot be attached in SQL Server 2005.
• SQL Server 7.0 log files containing create-index operations cannot be attached in SQL Server 2000 or SQL Server 2005.
• If you attach a replicated database that was copied instead of detached:
  • If you attach the database to an upgraded version of the same server instance, you must execute sp_vupgrade_replication to upgrade replication after the attach operation is complete. For more information, see sp_vupgrade_replication (Transact-SQL).
  • If you attach the database to a different server instance, regardless of version, you must execute sp_removedbreplication to remove replication after the attach operation is complete. For more information, see sp_removedbreplication (Transact-SQL).

To upgrade a database using detach and attach operations

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

Moving a Database or Database File

Important

We recommend that you move databases by using the ALTER DATABASE planned relocation procedure instead of using detach and attach operations. For more information, see Moving Database Files.

Usually, you can use detach and attach operations to move a database. Typical scenarios include moving a database to one of the following locations:

• A different physical disk on the same computer. For example, when the disk that contains a data file has run out of disk space and you want to expand the existing file (instead of to expand the database by adding a new file on a different disk).
• A different computer, without having to re-create the database and restore the database backup.

Moving a database by using detach-and-attach operations involves the following stages:

1. Detaching the database.
2. Moving the database files to the other server or disk.
3. Attaching the database by specifying the new location of the moved file or files.

To move a database using detach and attach operations

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

See Also

Concepts

Detaching and Attaching Databases
Securing Data and Log Files
Understanding Files and Filegroups

Other Resources

CREATE DATABASE (Transact-SQL)
sp_detach_db (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
12 December 2006	New content: Added a note about the SQL Server on-disk storage format being the same in 64-bit and 32-bit environments. Added a note to the "Attaching a Database to Another Server Instance" section. Added note regarding vardecimal storage format.
17 July 2006	New content: Added information about clearing the plan cache to the "Detaching a Database" section.
17 July 2006	New content: Added restriction against detaching system databases. Added the "Backup and Restore and Detach" section. Added the "Backup and Restore and Attach" section.
5 December 2005	New content: Added the security note.