Move Publisher database to another drive without affecting replication - SQL Server by serverku

A month ago, I wrote some articles related to replication configuration which are following,

Skip distributor agent error in sql server transactional replication - How to
Configuration Replication failure and retry alert-SQL Server

Hope you read and liked it. Now moving one another article and it is about to move the publisher database to another drive in the same server without breaking whole replication. So let me share some steps and continue with same as below,

1. Go to Replication Monitor from Replication tab under default instance the option with right click.

2. Make sure all undistributed command processed and should be 0 for all subscriber.

3. Go to agent tab (to disable)
         a. Go to snapshot agent option in combo box, right click and stop agent (to disable) or start agent (to enable) if it is running.
         b. Go to Log reader agent option in combo box, right click and stop agent (to disable) or start agent (to enable) if it is running.  
         c. Go to Queue reader agent option in combo box, right click and stop agent (to disable) or start agent (to enable) if it is running.

4. Making database restricted and offline it.

 ALTER DATABASE PublisherDB SET restricted_user with rollback immediate;
 ALTER DATABASE PublisherDB SET OFFLINE;

5. Mapping database with new file location.

ALTER DATABASE PublisherDB MODIFY FILE ( NAME = pdb_Data , FILENAME = 'D:\data\pdb_data.mdf' );
ALTER DATABASE PublisherDB MODIFY FILE ( NAME = pdb_log , FILENAME = 'D:\data\pdb_log.ldf' );

6. Stop SQL Database service and copy database files to new mapped location.

7. Making database online and make available with multi user.

ALTER DATABASE PublisherDB SET ONLINE;
ALTER DATABASE PublisherDB SET multi_user;

8. Repeat step 3 with “Go to agent tab (to enable)” and finish this task.

Please note these scripts run against publisher server. These are just steps and hope you found it useful. Thanks for reading.