Move distribution database to another drive

Question

Post reply

Move distribution database to another drive

Richard M.

SSCertifiable

Points: 7401
More actions
May 10, 2010 at 9:28 am

#224399

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]

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

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

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: 7401 More actions · Answer 2

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: 38780 More actions · Answer 3

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

-Roy

Richard M. SSCertifiable Points: 7401 More actions · Answer 4

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: 38780 More actions · Answer 5

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: 7401 More actions · Answer 6

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: 7401 More actions · Answer 7

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: 38780 More actions · Answer 8

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

-Roy

cjmorgan SSC Enthusiast Points: 181 More actions · Answer 9

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