Presents your 
SQL SERVER E-NEWSLETTER for July 1, 2003

<------------------------------------------->

USE SP_DETACH_DB/SP_ATTACH_DB BEFORE MOVING DATABASE FILES

Databases that are updated frequently can grow at an enormous rate.
However, it isn't usually possible to move databases from a smaller drive
to one that is much larger and then be able to accommodate future growth.

Before moving a database, you need to take it offline. You can do that
by detaching the database from SQL Server 2000 by using the sp_detach_db
stored procedure, moving it to the other drive, and then reattaching it by
running another stored procedure, sp_attach_db.

Sp_detach_db is a powerful stored procedure that can only be run by a
system administrator. In addition to its "disconnect" quality, the
procedure also automatically runs UPDATE STATISTICS on all tables 
before detaching them.

The example below detaches MyDatabase:

EXEC sp_detach_db 'MyDatabase'

Now we can move our database to a new drive and reattach it. 
Reattaching the database is a little more complex. In addition to providing the
database name, you have to tell SQL Server where the database files are located.

In the example below, we'll reattach the database from a new location on
drive D:\AnyFolder\:

exec sp_attach_db 'MyDatabase',
@filename1='D:\AnyFolder\MyDatabase_Data.MDF',
@filename2='D:\AnyFolder\MyDatabase_Log.LDF'

Note that you should only use sp_attach_db on databases that were
previously taken offline with sp_detach_db.

J.E. Harvey, MCSD, MCDBA, has been tinkering with and writing about
technology for more than two decades.

----------------------------------------