Microsoft SQL Server 2012 Bible

Detaching and Attaching

Although it is often overlooked, one of the easiest ways to move a database from one computer to another is to detach the database, copy the files, and attach the database to SQL Server on the destination computer.

For developers who frequently move databases between notebooks and servers, this is the recommended method. Detaching a database effectively deletes the database from SQL Server's awareness but leaves the files intact. The database must have no current connections, no snapshots, and not be replicated or mirrored if it is to be detached. Additionally, system databases or databases that are listed as being suspect may not be detached. In order to attach a database, you need at least CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permissions on the instance. To be able to detach a database, membership in the db_owner fixed database role is required.

Warning

If you detach a database from a lower level of SQL Server and attach it to a higher level one (for example, 2005 to 2012), the database automatically upgrades. After you attach a database to a higher-level version, you cannot detach and reattach to a lower level version.

For more details on the security roles, refer to Chapter 33, “Authorizing Securables.”

Detaching and attaching the database can carry with it any database users, security roles, and permissions, but it cannot ...