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!!
