Moving replicated databases

I have to move a SQL Server 2000 database from drive to another on the
same server because we are running out of space. I know the easiest
way to accomplish this is to detach the database, move the .mdf & .ldf
files, and reattach the database. However, this database is being
replicated, which is creating a problem. Due to both the size of the
database, and multiple off site locations, and replication takes
approximately 2-3 days, which is not an option... Does anyone know of
a easier way to restart replication without starting it all from
scratch? Thanks in advance for you help...
I think the easiest way is to script out the replication setup, prevent
access to the system, synchronize, drop the publications, move the databases
then do a nosync initialization to reinitialize.
HTH,
Paul Ibison
|||use master
go
sp_detach_db 'myDB'
go
-- Server: Msg 3724, Level 16, State 1, Line 1
-- Cannot drop the database 'mydb' because it is being used for replication.
-- STEP 1
-- find your database like 'myDB' with value 4
SELECT name, category
FROM master..sysdatabases
GO
-- Output
NAME Category
distribution16
master0
model0
msdb0
Northwind0
pubs0
myDB4 -- remember to note this value of your replicated database
tempdb0
-- STEP 2
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
-- STEP 3
-- Update replication bitmap to remove Distributor flag for that DB
-- current database mydb category value is 4 ,it means replicated
-- Reset with Zero (0) to
UPDATE master.dbo.sysdatabases
SET category = 0
WHERE name = 'myDB'
GO
-- STEP 4
use master
go
sp_detach_db 'myDB'
go
-- Move the files MDF/NDF/LDF
-- STEP 5
sp_attach_db myDB
'D:\MSSQL\Data\myDB_Data.MDF',
'E:\MSSQL\Data\myDB_Log.LDF' ,
'E:\MSSQL\Data\FGmsmerge_genhistory.ndf',
'E:\MSSQL\Data\myDB_Data2.NDF' , -- moved from D: to E:
'D:\MSSQL\Data\myDB_Data3.NDF'
go
-- STEP 6
UPDATE master.dbo.sysdatabases
SET category = 4
WHERE name = 'myDB'
GO
-- STEP 6
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
Good luck
Cheers
Ponnu MCDBA