Move user databases

SQL server 2000:
1.backup database and restore with MOVE satement.
2. detach and attach

sp_detach_db 'db'
--move db files to target locations
EXEC sp_attach_db @dbname = N'db',
   @filename1 = N'G:\database\db.mdf',
   @filename2 = N'G:\database\log.LDF'

SQL 2005 or above:
besides the way used for SQL server 2000, 'alter database' can also be used for this purpose.

alter database db set offline;
go
--Move the file or files to the new location.
alter database db modify file (name='db',filename='g:\database\db\db.mdf')
go
alter database db modify file (name='db_log',filename='g:\database\db\db_log.LDF')
go
alter database db set online;
go