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
--------------------------
distribution 16
master 0
model 0
msdb 0
Northwind 0
pubs 0
myDB 4 -- remember to note this value of your replicated database
tempdb 0




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

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
--------------------------
distribution 16
master 0
model 0
msdb 0
Northwind 0
pubs 0
myDB 4 -- remember to note this value of your replicated database
tempdb 0




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

This is getting into very unsupported territory here, and it's worth pointing
this out. If this is a production box then PSS will not investigate or help
if it goes pear-shaped.
If you try this, consider backing up the database first and script out the
replication setup and stop all replication-related jobs on publisher and
subscriber.
Rgds,

Paul,

Before I post my last message I completed testing of moving database File
(NDF) from one drive another drive on my Test Replication server and
Production Server nearly TWO months ago. For me it is working very well
without error till now.

Type of replication: Merge Replication
More than 27 subscribers and 70GB of Database size.

Regards
Ponnu


[quoted text, click to view]