Move distribution database to another drive

  • Richard M.

    SSCertifiable

    Points: 7287

    We enabled Replication over the weekend for one of our main prod servers. All went fine, except that the distribution database was created on the wrong drive......

    Is there any way to move it without having to redo the whole replication process?

    I searched and didn't find any conclusive response on how to do it, if possible.

    Could I issue the ALTER DATABASE command to move the file pointers and then restart the distributor/subscriber SQL server (same server), move the files and then all would resync ?

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • Roy Ernest

    SSC-Dedicated

    Points: 38707

    You can use Alter database to move it to a different drive. The usual method is to

    Run ALTER DATABASE NameOfDB SET OFFLINE.

    Move the file to the new location.

    Run ALTER DATABASE nameOfDB MODIFY FILE ( NAME = TheActualName,

    FILENAME = 'NewPath\FileName'.

    Run ALTER DATABASE database_name SET ONLINE.

    But test this scenario in your test environment before trying it out on production

    -Roy

  • Richard M.

    SSCertifiable

    Points: 7287

    Roy,

    thanks for your answer.

    I already tried that. Trying to set the distribution database offline will hang forever, because it is being blocked by some of the replication processes (not sure if from the subscriber side or the publisher side) and was not able to pinpoint the process in order to stop it.

    The ideas is to be able to do this (moving the distribution DB) without having to redo the whole replication configuration...

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • Roy Ernest

    SSC-Dedicated

    Points: 38707

    Did you try stopping the log reader agent and distribution agent?

    -Roy

  • Richard M.

    SSCertifiable

    Points: 7287

    Roy,

    just to make sure. On the Publisher, I would stop the Log Reader Agent and on the Distributor/Subscriber I would stop the Synchronization ?

    After I move the distribution DB, how would I get it going again? Just Start them, Reinitialize, or what must I do?

    Thanks!

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • Roy Ernest

    SSC-Dedicated

    Points: 38707

    You can start them back. It is just like rebooting your servers. Since you publisher is in Full mode (It has to be in full mode since it is replicating) all the commands will still be there in the transaction log. There for when you restart the services the log reader will continue from where it left off. Till now I have never had trouble to stop log reader agents and restart them.

    But test it on QA environment. You never know what could happen.

    -Roy

  • Richard M.

    SSCertifiable

    Points: 7287

    OK... Will give it a try and see how it goes. Thanks again.

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • Richard M.

    SSCertifiable

    Points: 7287

    Report back: All went well 🙂

    For the benefit of other readers:

    * Stop the Log Reader Agent and Distribution Agent

    * ALTER DATABASE distribution SET OFFLINE

    * Move the data and log file to the new location

    * ALTER DATABASE distribution MODIFY FILE ( NAME = distribution , FILENAME = 'NewPath\distribution.mdf'

    * ALTER DATABASE distribution MODIFY FILE ( NAME = distribution_log , FILENAME = 'NewPath\distribution.ldf'

    * ALTER DATABASE distribution SET ONLINE

    * Started the Log Reader Agent and Distribution Agent

    ... and it synced

    Thanks!

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • Roy Ernest

    SSC-Dedicated

    Points: 38707

    Thanks for the update. Others might find it useful. Glad things went OK.

    -Roy

  • cjmorgan

    SSC Enthusiast

    Points: 181

    Richard M. - Tuesday, May 11, 2010 9:43 AM

    Report back: All went well :)For the benefit of other readers:* Stop the Log Reader Agent and Distribution Agent* ALTER DATABASE distribution SET OFFLINE* Move the data and log file to the new location* ALTER DATABASE distribution MODIFY FILE ( NAME = distribution , FILENAME = 'NewPath\distribution.mdf'* ALTER DATABASE distribution MODIFY FILE ( NAME = distribution_log , FILENAME = 'NewPath\distribution.ldf'* ALTER DATABASE distribution SET ONLINE* Started the Log Reader Agent and Distribution Agent... and it syncedThanks!

    Thank you for the step by step.  I was a bit surprised not to see the Detach option, but then again, it is a "System Database" so I'll be trying this during the customer's next outage window.  
    CJ Morgan

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply