Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Posts
    3

    Question Unanswered: How to Move Distribution DB to another disk (Replication)

    Somebody now what is the correct process to move the Distribution BD to another Disk with out affect the defined replication

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    come again?

    are you trying to "move" or "copy" your database to another server? An exact copy?

    More info please. I don't know what the defined replication has to do with moving a DB in this context.

    ???
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    I've never tried to change distributors before, but it might be possible by adding a new distribution database and then dropping the original database,

    Look up "Distributors" in BOL ... more specifically read the "modifying properties" and "disabling" sub-topics.

    Or you could just drop and then re-add using the new distributor if they are fairly small databases.

    BTW Paul ... the distribution database is used to collect transactions from a publisher and send them to a subscriber. It's not your everyday database that can be moved with backup/restore or detach/reattach. It has to be identified when setting up a publisher for replication.

    -- This is all just a Figment of my Imagination --

  4. #4
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    Here you go!
    CAUTION! updating system tables is NOT recommended!
    -- Replicate all pending transactions
    use master
    go
    sp_configure 'allow updates' ,1
    go
    reconfigure with override
    go
    update master.dbo.sysdatabases set status=16, category=0
    where name='distribution' and status=64 and category=16
    go
    -- NOW YOU COULD EASILY DETACH THE DISTRIBUTION DB FROM THE ENTERPRISE MANAGER.
    -- detach distirbution db
    -- TAKE YOUR DATA AND LOG FILES TO THE NEW DISK AND , THEN RE-ATTACH THE DATABASE USING ENTERPRISEMANAGER, (use sp_attach_db and sp_detach_db otherwise).
    --after attaching the db, run this.
    update master.dbo.sysdatabases set status=64, category=16
    where name='distribution' and status=16 and category=0
    go
    sp_configure 'allow updates' ,0
    go
    reconfigure with override
    go


    NOTE! do it on your test environment first, and better disable replication and reconfigure replication and give the new path for distribution db UNLESS disabling replication is not bearable to you.
    When u setup replication again, you can go ahead WITHOUT snapshot by selecting the option SUBSCRIBER already has the data.
    Cheers!





    !Direction is More Important than Speed!

    http://talatnauman.blogspot.com/

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by tomh53
    BTW Paul ... the distribution database is used to collect transactions from a publisher and send them to a subscriber. It's not your everyday database that can be moved with backup/restore or detach/reattach. It has to be identified when setting up a publisher for replication.
    Thanks Tom, I appreciate the info...guess I have a bit-o-reading to do also, though we don't use replication on our production systems, so I have yet to run across it other than near-brushes with the setup on dev systems.

    I suspect in our system it would be useful, though because I am the resident "expert" *shudder* we haven't gotten to the point of investigation yet.

    Hmmmm...I was just wondering what to do with the current "slow period" I am having while my boss is concentrating on a new product release in-house.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •