Search This Blog

Monday, October 11, 2010

Moving User Database Files in SQL Server 2000

0. Take Full backup before moving files

1. Set database in single user mode
use master

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

2. Get path of database files

Exec sp_helpdb testdb2000

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'

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'

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

No comments: