O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required