Moving of data/log files to another drive of SQL Server 2000 server.

This is quite old topic but we never know which task you have to face as a DBA as I did recently J. In case of latest versions we can use ‘ALTER DATABASE’ command to move the database as discussed in another article. But this bit different and easy in SQL Server 2000 here we need to follow below steps to move a database files either data file or log file from one drive to another drive.

1.    Detach the database

Command: sp_detach_db ‘database name’

2.    Copy the required file(Data or Log file) to new location manually.

3.    Attached the database with below command

Sp_attach_db ‘database name’, ‘new location of data file’, ‘new location of log file’

Eg: Let’s say we want to move the ndf file and log file of Test database from a different location to the new location ‘C:\SQL2K\’, so the command will be like below:

 sp_attach_db ‘TestDB’, ‘C:\SQL2K\Test.mdf’,’C:\SQL2K\Test.ndf’,’C:\SQL2K\Test.ldf’

Note: After attaching the database delete the file from old locations.

Incase if the database of SQL Server 2000 version is involved in replication as in my case we first have to disable the replication obviously. To disable replication SQL 2K version please follow below setps:

sp_removedbreplication 'database name'

Go

exec sp_configure 'allow updates',1

RECONFIGURE WITH OVERRIDE

Go

Update sysobjects set replinfo = 0 where replinfo != 0

UPDATE syscolumns SET colstat = colstat & ~4096 WHERE colstat & 4096 <>0

Go

exec sp_configure 'allow updates',0

RECONFIGURE WITH OVERRIDE

Incase if the sp_configure command throwing error please execute it in new query window. After executing above queries DB will be out of replication but to remove completely you have to disable it from the server properties and select ‘Disable All’ options, based on the size of the database this disabling will take time and as it is old version sometimes the session will hang but once entire replication set up is disabled session will be back to normal.

Thanks!!