I just found out script to help on refresh DB from production to test platform where replication is set on production, we have script to:
1) restore the DB from production subscriber to test platform.
2) exec sp_removedbreplication to remove replication from restored test platform.
3) run sp_change_users_login to remove orphaned-users from tested platform as the user right of production and test platform is not going to be the same.
but by this link:
the order seems to be :
1)Backup and restore the publisher DB on our dev/test environments. If we have replication setup in these environments we first drop the existing replication setup before doing this. When we drop the existing replication setup we take care of removing everything - all publications/subscriptions
are removed, distribution DB is dropped and distributor is removed.
2)Run sp_removedbreplication to get rid of all the replication related settings on the restored publisher DB
3) Configure replication in our dev/test environments using the restored publisher DB as the publisher.
and the detail steps on remove the replication setup are :
a) Run sp_droppullsubscription on each of the subscribers for each of the subscriptions (for pull subscriptions - this is not needed for a push subscription)
b) Run sp_dropsubscription on the publisher for each of the subscribers and each of the publications
c) Run sp_droppublication on the publisher for each of the publications
d) Run sp_replicationdboption on the publisher to set the publish option to false
e) Remove the distributor setup - run sp_dropdistributor, sp_dropdistributiondb and sp_dropdistpublisher on the distributor
so the order should be
1) restore the subscriber DB to the TEST DB server first.
2) run exec sp_removedbreplication on the TEST DB server, which is the restored subscriber
Am I right? but the links above seems remove the replication before restore ,which seems doesn't make any REAL sense at all, as it will kill production replication which is running, right?
So just one command : exec sp_removedbreplication, on restored test DB can do all removal ?
how about we need to create the same thing back to the test platform, I mean setup the same replication?