SQL Script to move distribution database from one drive to another drive

First we need to stop the Log Reader Agent and Distribution Agent.For stopping the log reader agent, expand publisher and right click on the publication and view log reader agent status and click stop. Take the distribution database offline from ssms. For stopping the distribution agent, go to sql server agent and expand jobs identify the distribution agent job and right click stop.

 ALTER DATABASE distribution SET OFFLINE
 

Copy the data and log file of the distribution database to the new location.

 ALTER DATABASE distribution MODIFY FILE ( NAME = distribution , FILENAME = 'T:\distribution\distribution.mdf')
 ALTER DATABASE distribution MODIFY FILE ( NAME = distribution_log , FILENAME = 'T:\distribution\distribution.ldf')
 --Get the database online
 ALTER DATABASE distribution SET ONLINE
 

Start the Log Reader Agent and Distribution Agent.

Hope this helps!

Advertisements
This entry was posted in SQL Server DBA Stuff and tagged , , , , , , , , . Bookmark the permalink.

One Response to SQL Script to move distribution database from one drive to another drive

  1. Joe Beatty says:

    Excellent article and just in the ‘nick of time. My drive was filling up from unreplicated transactions, thank you!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s