move distribution database to other storage

I've got a couple of replicated databases and I've found myself in the
position of wanting to move my distribution database to a different storage
volume. Enterprise manager considers it replicated, so I can't just detach
it, copy and reattach. Anyone know of a good way to do this?
Thanks!
Patrick Peters
patrick.peters at orametrix dot com (fix the reply address for this message
if you're sending email to me)
Hi,
I think you need to completely remove replication and then start over from
start.
Thanks
Hari
SQL Server MVP
"Patrick Peters" <patrick.peters at orametrix dot com> wrote in message
news:uLz0vzFnFHA.1968@.TK2MSFTNGP14.phx.gbl...
> I've got a couple of replicated databases and I've found myself in the
> position of wanting to move my distribution database to a different
> storage volume. Enterprise manager considers it replicated, so I can't
> just detach it, copy and reattach. Anyone know of a good way to do this?
> Thanks!
> Patrick Peters
> patrick.peters at orametrix dot com (fix the reply address for this
> message if you're sending email to me)
>
|||I was hoping for something less painfull than that. This is, however, what
we ended up doing. Luckily the amount of data involved is small right now.
In a couple of months, this solution would have been unacceptable.
Pat
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OQ4ehNGnFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I think you need to completely remove replication and then start over from
> start.
> Thanks
> Hari
> SQL Server MVP
> "Patrick Peters" <patrick.peters at orametrix dot com> wrote in message
> news:uLz0vzFnFHA.1968@.TK2MSFTNGP14.phx.gbl...
>

Move distribution database to another drive?

Is it possible to move a distribution database to another drive without removing replication? I have done some research but I getting mixed answers from Google searches.

Thanks in advance

Dave

I don't know the specifics, but I believe it involves creating a new "data file" on the new drive, then telling SQL Server to decommission the original, moving data to the new file as it goes.

-Ryan / Kardax

|||

Have you tried looking in Books Online before google pages?

ALTER DATABASE...MODIFY FILE should work.

Move distribution data/log files to another drive

I haven't found the definitive answer on how or if this can be done without removing replication. I'm thinking ALTER DATABASE modify_file is the way to go. Anybody know if this will work or a better way to go about it?

Thanks

"Alter database" should work. There is a similar posting.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1619608&SiteID=1

Gary

|||I saw that one. The client ended up adding a file to the new drive and shrinking the original.

Thanks

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