Move the database with merge replication configured

Hi,

I need to move a database to a different disk drive on the same server. It is the publisher database with the meger replication configured. I stopped all the merger replication jobs and tried to detached the database. I got the error below:

Cannot drop the database 'mydb' because it is being used for replication.

My question is if there is a way I can disable the merger replication and re enable it after I move the database or I have to completely remove the replication and rebuild it.

Thanks,

January 24th, 2014 4:10pm

There are two ways of doing this. I backup the database and restore it using the move command to move the files to the new location.

Put the database offline, alter the file locations, copy the files to the new location and put your database back online. Follow the instructions here to do this.

http://technet.microsoft.com/en-us/library/ms189133.aspx

Free Windows Admin Tool Kit Click here and download it now
January 24th, 2014 4:29pm


  1. Alter the data & log  file locations (Modify the metadata to new location)
  2.  stop the merge agent.
  3. Bring the database single_user
  4. Bring the database offline
  5. move the data file and log file to new location
  6. bring the database online
  7. set it to multi_user
  8. start the merge agent


--Prashanth

January 24th, 2014 4:33pm

Prashanth - this is good advice BUT - merge replication will pick up where it left off. So you can put it offline mid sync, and then make the modification (or make the modification and then put it offline), move, put it back off line.

The next time the sync occurs it will pick up where it left off.

With the backup, restore method the same thing applies, what may happen is that the sync will apply changes on the subscriber and then when you do the restore the changes are "lost" or overwritten. The next time the sync happens this missing transactions will be reapplied.

Free Windows Admin Tool Kit Click here and download it now
January 24th, 2014 4:37pm

I know how to move the database. My problem is I stopped all replication jobs, however I still can not detach the database. I got the error below.

Cannot drop the database 'mydb' because it is being used for replication.

The listed below are the agents used by the replicaion. I cannot find Merge Agent under replicaion monitor. I need to find a way to stop the merge replication, so the system can let me move the database.

  • The Snapshot Agent, which is used by all publications.

  • The Log Reader Agent, which is used by all transactional publications.

  • The Queue Reader Agent, which is used by transactional publications enabled for queued updating subscriptions.

  • The Distribution Agent, which synchronizes subscriptions to transactional and snapshot publications.

  • The Merge Agent, which synchronizes subscriptions to merge publications.

  • Replication maintenance jobs.

January 24th, 2014 5:03pm

You can't drop it. All you can do is set it offline, or do the backup restore method.
Free Windows Admin Tool Kit Click here and download it now
January 24th, 2014 5:07pm

Don't detach the database. if you want to detach, then you need to remove using 

sp_removedbreplication [ [ @dbname = ] 'dbname' ]

http://technet.microsoft.com/en-us/library/ms188734.aspx

and rebuilt it from the scratch.

Just issue Alter database databasename set single_user with rollback immediate and follow the steps.

-Prashanth


January 24th, 2014 5:16pm

Prashanth - using sp_removedbreplication is not a good idea as it will break replication and require the user to rebuild it and re-initialize all subscriptions.
Free Windows Admin Tool Kit Click here and download it now
January 24th, 2014 5:43pm

I Agree..even I don't go for it. I don't want him to break the replication.Thanks for your guidance -Prashanth
January 24th, 2014 6:23pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics