Moving User Database Files in SQL Server 2000

0. Take Full backup before moving files

1. Set database in single user mode
use master
go

exec sp_dboption testdb2000,'single user','true'
go

2. Get path of database files

Exec sp_helpdb testdb2000
go

3. Save location of all files

C:\Program Files\Microsoft SQL Server\MSSQL$INS2000\data\TestDB2000_Data.MDF
C:\Program Files\Microsoft SQL Server\MSSQL$INS2000\data\TestDB2000_Log.LDF

4. De-attach this database

Exec sp_detach_db @dbname = 'testdb2000','true'
go

5. Copy all files available in step 2 and paste them to a new location
6. Rename them on their old location, just in case of any issue to revert back changes
7. After copying them to a new location, attach these files from new location to this database, you can attach upto 16 files through this procedure.

Exec sp_attach_db @dbname = 'testdb2000', @filename1 = 'd:\testdb2000\TestDB2000_Data.MDF', @filename2 = 'd:\testdb2000\TestDB2000_Log.LDF'
go

8. Connect to user database and verify changes, after verification, remove old file from old location