Attaching and Detaching Databases

Microsoft® SQL Server™ allows the data and transaction log files of a database to be detached and then reattached to another server, or even to the same server. Detaching a database removes the database from SQL Server, but leaves the database intact within the data and transaction log files that compose the database. These data and transaction log files can then be used to attach the database to any computer running SQL Server, including the server from which the database was detached. This makes the database available in exactly the same state it was in when it was detached.

Detaching and attaching databases is useful if you want to move a database:

• From one computer to another without having to re-create the database and then restore the database backup manually.
• To a different physical disk, for example, when the disk containing the database file has run out of disk space and you want to expand the existing file rather than add a new file to the database on the other disk.

To move a database, or database file, to another server or disk:

1. Detach the database.
2. Move the database file(s) to the other server or disk.
3. Attach the database specifying the new location of the moved file(s).

When attaching a database, the name and physical location of the primary file must be specified. Because the primary file contains the information needed to find the other files comprising the database, it is necessary to specify only the location of the files that have changed location, for example, files originally located on the C:\Mssql7\Data subdirectory that are now located on the D:\Mssql7\Data subdirectory. Any files that have changed location must be specified. Otherwise, SQL Server will try to attach the files based on the file location information stored in the primary file. If the files have changed location, they will not be found, and the database will not be successfully attached.

To attach a database

To detach a database