March 23, 2017 at 9:02 am
I have a backup of database used for transaction replication. It has a single table data.
I am reinstalling my system and recreating publication/subscription. is there a way to just restore the data in the replication database? as the publication/subscription are already existing.
Using "restore without KEEP_Replication still overwrites the existing publication/replication and replication does not work.
March 23, 2017 at 2:21 pm
padma.kuruganti - Thursday, March 23, 2017 9:02 AMI have a backup of database used for transaction replication. It has a single table data.
I am reinstalling my system and recreating publication/subscription. is there a way to just restore the data in the replication database? as the publication/subscription are already existing.
Using "restore without KEEP_Replication still overwrites the existing publication/replication and replication does not work.
You would normally have to do a regular restore. But I may not be following what you are asking. Publications, subscriptions, articles are all objects in replication.
The databases would be publisher, subscriber, distribution so I'm not sure what you are restoring. But you restore and then redo the publication and subscriptions whether you use the keep_replication switch or not. You can find recommended strategies for backup and restore for replication in this article:
Strategies for Backing Up and Restoring Snapshot and Transactional Replication
Sue
March 24, 2017 at 7:34 am
Sue,
I have DB1 with table1 on System 1 whose data is replicated to System2 - DB2. Publisher and distributor are on the same system (System 1).
I took a back up of DB1. I had to reinstall system1 starting with OS. (windows 7) and installed my application which created the DB1, table 1 (no entries in the table) and publication/subscription. All that I want to do is to get the data into table 1 since all publication/subscription is already created.
When I did a restore of DB1, I noticed that it brings in replication meta data which is causing following issues:
View synchronize status - ' Anerror occurred while attempting to access the subscription
To synchronize, you must first create anagent job for this subscription.'
Ownerfor DB1 is blank - this was fixed by using SP to change dbowner
Viewlog reader agent status – The process could not execute‘sp_repldone/sp_replcounters’ on W7S001 - this was fixed by running SP_replrestart
My QS - Is there a way to bring in just the data content? or take a backup of data content as a separate file? How to identify and fix the error in View synchronize status.
March 24, 2017 at 7:53 am
padma.kuruganti - Friday, March 24, 2017 7:34 AMSue,
I have DB1 with table1 on System 1 whose data is replicated to System2 - DB2. Publisher and distributor are on the same system (System 1).
I took a back up of DB1. I had to reinstall system1 starting with OS. (windows 7) and installed my application which created the DB1, table 1 (no entries in the table) and publication/subscription. All that I want to do is to get the data into table 1 since all publication/subscription is already created.
When I did a restore of DB1, I noticed that it brings in replication meta data which is causing following issues:
View synchronize status - ' Anerror occurred while attempting to access the subscription
To synchronize, you must first create anagent job for this subscription.'
Ownerfor DB1 is blank - this was fixed by using SP to change dbowner
Viewlog reader agent status – The process could not execute‘sp_repldone/sp_replcounters’ on W7S001 - this was fixed by running SP_replrestart
My QS - Is there a way to bring in just the data content? or take a backup of data content as a separate file? How to identify and fix the error in View synchronize status.
It looks like your problem is from using the keep_replication switch on the restores. That is what retains the metadata and it applies to both the publisher and subscriber. But all it adds is the metadata, you still have to create the publication and subscriptions. So to restore without the metadata, don't use the keep_replication switch.
Sue
March 24, 2017 at 8:07 am
Sue,
I restored without KEEP_REPLICATION option on my system when publication/subscription was not existing on my system. After restore I saw the publication/subscritption under Replication in the Sq lmanagement studio, though there were no agents for publication/subscription. Someone else told me that KEEP Replication option was to preserve replication settings on the target system to which restore was being invoked.
What is correct?
Padma
March 24, 2017 at 8:44 am
padma.kuruganti - Friday, March 24, 2017 8:07 AMSue,
I restored without KEEP_REPLICATION option on my system when publication/subscription was not existing on my system. After restore I saw the publication/subscritption under Replication in the Sq lmanagement studio, though there were no agents for publication/subscription. Someone else told me that KEEP Replication option was to preserve replication settings on the target system to which restore was being invoked.
What is correct?
Padma
The metadata is the settings for replication. The metadata/settings would include replication tables and the data triggers and stored procedures. This is not replicated objects but the replication objects used for tracking/monitoring/configuring replication. You would still need to recreate the publications, subscriptions.
You would typically synchronize, remove pieces, backup and then restore just as the steps in the article I posted in the earlier post on Backup and Restore strategies.
For the most part, if you don't follow those steps you have to manually correct and fix different pieces. It just doesn't work like typical backup and restores. One of the most important lines in the article on Backup and restore strategies is: "You can restore all databases in a replication topology if recent backups are available and the appropriate steps are followed"
That really is the only way it's supported. Other than that, your doing piecemeal fixes. Master and msdb also have pieces related to replication that are often forgotten. If you go through that article now, even though it's after the fact, you can often figure out what pieces you would have missed and clean things up from there. I would really really really recommend you go through that article.
Sue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply