Detaching/attaching a database v7.0

In version 7.0 of SQL Server, you have the ability to detach and attach existing databases. This comes in handy when you have a database that you want to move from serverX to serverY or if you want to relocate an existing database to a different physical location on the same server. Although this can also be accomplished via backup and restore operations, Microsoft has provided the system stored procedures of sp_detach_db and sp_attach_db as additional methods of moving databases. 

Sp_detach_db is used to sever the link between the target database and the SQL Server master database. Under other circumstances, severing this link requires the DROP DATABASE command, which removes all of the target database's physical files and data. Sp_detach_db preserves the database but removes the link. 

The example below from MSDN Books Online illustrates a typical detach statement. 

EXEC sp_detach_db 'pubs', 'true'

The parameters passed to the procedure are the name of the database to be detached and an update statistics flag. 'True' is used to skip update statistics and 'false' is used to run update statistics on the detached database. The 'false' option is primarily for moving a database to read-only media. 

The sp_attach_db procedure is executed as follows and requires you to specify the name of the database being attached as well as the physical filenames and locations. 

EXEC sp_attach_db @dbname = N'pubs', 
    @filename1 = N'c:\mssql7\data\pubs.mdf', 
    @filename2 = N'c:\mssql7\data\pubs_log.ldf'

However, MSDN Books Online warns, "sp_attach_db should only be executed on databases that were previously detached from the database server using an explicit sp_detach_db operation. If more than 16 files must be specified, use CREATE DATABASE with the FOR ATTACH clause."