How to Move a Publisher Database

Recently I faced an incident where I needs to MOVE a database which is a publisher, to a different drive. The reason was the insufficient storage. The challenge was to intact the replication settings of the database after the move. So that, I don't have to recreate the replication which is a tedious also a boring task.

As many of you aware, there are several methods can be applied to move a database. Such as, attach / detach method, move database files while the server is shutdown, etc. However none of these methods are a solution in my case, because of the replication factor.

The version of the SQL server I am using is 2008.

Solution

Took a full backup of the database and then restored with “WITH MOVE” option to the new drive.  

Identify out of synch subscriptions

One of the main tasks of DBA is to monitor replication status. There may be situations where subscriptions may out of synch due to various reasons. If this happened, DEV and QA will face issues and they will start communicate with DBAs to verify the replication status. If you’ve not setup proper monitoring mechanism to see replication staus this is the usual story.


However if you have already setup monitoring mechanism the DBAs will identify the issues upfront without making panic on DEVs and QAs.

This blog is on how to identify out of synch subscriptions especially in transactional replication.