Attaching and Detaching a Database

In Microsoft® SQL Server™ 2000, the data and transaction log files of a database can 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 instance of 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 you attach a database, the name and physical location of the primary data file must be specified. The primary file contains the information needed to find the other files comprising the database unless one or more of those files have changed location since the database was detached. Any files that have changed location must be specified in addition to the primary file. Otherwise, SQL Server tries to attach the files based on incorrect file location information stored in the primary file, and the database will not be successfully attached.

If you attach a database to a server other than the server from which the database was detached, and the detached database was enabled for replication, you should run sp_removedbreplication to remove replication from the database. Alternatively, you can remove replication from the database prior to detaching it.

Errors produced while detaching a database may prevent both the database from closing cleanly and the transaction log from being rebuilt. If you receive an error message, perform these 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.

To attach a database

Transact-SQL