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
January 24th, 2014 4:29pm
- Alter the data & log file locations (Modify the metadata to new location)
- stop the merge agent.
- Bring the database single_user
- Bring the database offline
- move the data file and log file to new location
- bring the database online
- set it to multi_user
- 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.
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.
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
- Edited by
Prashanth Jayaram
13 hours 16 minutes ago
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.
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
- Edited by
Prashanth Jayaram
12 hours 9 minutes ago
January 24th, 2014 6:23pm