Move Distribution

Hi All,
I have 1 server as publisher and distributor with SQL 2000 sp3a and I have about 100 subscribers.
I have to move my distribution database and my published database on another physical location on the same server.
I tried to use sp_detach_db stored procedure but an error occurred: Server: Msg 3724 (Cannot drop the database 'distribution' because it is being used for replication).
Somebody knows how can I move my databases without reconfigure all my merge replication.
thanks in advance,
You can't do this.
You must disable replication on your publisher, enable it on your distributor, and allow this publisher to publish to this distributor. Then reinstall replication on your publisher and have it use the distributors database.
Then rebuild your publications and susbcriptions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||I have a quicker way of moving the files of any database used for replication (including distribution database) :
Just backup the database, then restore it using SQLEM, with following subtelties :
- "Force restore over existing database" option ticked,
- and edit the paths of the .MDF and .LDF.
It keeps all replication stuff alive.
Only pay attention that no user is connected, of course (go to Management > Current Activity > Process Info to view active connections to the database you're backuping/restoring).
Note:
You may even use this procedure upon disaster recovery, when the database does not exist anymore :
- create a new database (with _same_ _name_ as the crashed database, of course)
- enable it for publication (even if it is the subscriber database), using Replication>Configure publishing etc.
- restore the backup, with "Force restore over existing database"
Hope this helps,
--Rico