In the event that I need to restore a database involved in replication, what considerations do I need to keep in mind?
- Do I need to keep backups of both the publisher and the subscriber?
- Can I restore only the publisher and have the subscriber automatically sync up?
- If the subscriber crashes, is it possible to restore from its back up and have it automatically sync up?
Best Answer
The answer depends on your type of replication. For example, merge replication allows you to update in more than one source DB, and then distribute those updates. With this topology, you would of course need backups everywhere data originates.
As for automatic syncing, I've seen that most of the time if there is any issue with repl on either end of publisher / subscriber, you will usually have to reinitialize the repl. An exception to this might be when you have a subscriber problem such that it is offline to the distributor. In that case the distributor would "catch up" the subscriber when it came back up IF there was no corruption (bad sequence) at the subscriber.
As a rule, back everything up, but don't count on being able to "jump-start" repl back to a functional state. Re-initializing it is common, and painful with large DBs.