Strategies for Restoring Snapshot or Transactional Replication

There are five basic strategies for recovering snapshot or transactional replication:

1. Reenable replication.
2. Restore Publisher database, and then reenable replication.
3. Restore Publisher(s) and Distributor, and then validate and reinitialize subscriptions.
4. Drop and re-create subscription(s).
5. Restore Subscriber database, and then validate subscriptions.

Note As part of any recovery strategy, always keep a current script of your replication settings in a safe location. In the event of catastrophic failure of a server or the need to set up a test environment, you can modify the script by changing a few server name references, and it can be used to help recover your replication settings.

In addition to scripting your current replication settings, you should also script the enabling and disabling of replication. These scripts are part of two recovery strategies for the Publisher or Distributor.

For information about mapping these five recovery strategies to backup strategies, see Matrix of Backup and Restore Strategies for Snapshot or Transactional Replication (by Topology). The number of each recovery strategy corresponds to the numbers in the matrix.

1. Reenable Replication

   If your Distributor has failed or if you need to recover your entire replication topology, an appropriate strategy for recovering is to use replication scripting to remove and reenable replication. This strategy requires you to:

   1. Run the scripts to disable or drop replication from each server.
   2. Run the scripts for creating or enabling replication on each server.

   One advantage of this strategy is that it removes the need to reconfigure each Publisher, Distributor, and Subscriber manually. Another advantage is that the same strategy can be used with merge replication, thereby providing a unified strategy in applications that use all three types of replication. The disadvantage of this strategy is that it takes longer for the publication to flow from Publisher to Subscriber.

2. Restore Publisher Database, and then Reenable Replication

   If your Publisher has failed and the backup of the publishing database is available, an appropriate strategy for recovering is to restore the publishing database and then reenable replication. This strategy requires you to:

   1. Restore the publishing database from the backup.
   2. Reenable replication by using the Configure Publishing and Distribution Wizard or programmatically.

   The advantage of this strategy is that only one server must be restored. The disadvantage of this strategy is that is relies heavily on regular backups of the Publisher.

3. Restore Publisher(s) and Distributors, and then
   Validate and Reinitialize Subscriptions

   If either your Publisher or Distributor has failed and you have a coordinated backup of the Publisher and Distributor, an appropriate strategy is to restore both the Publisher and Distributor, use data validation to verify the correctness of the data at Subscribers, and reinitialize, if necessary. This strategy requires you to:

   1. Restore the publishing database from the backup.
   2. Restore the distribution database and snapshot folder from the backup.
   3. Run data validation on the subscribing database to determine if the Subscriber should be reinitialized.
   4. If necessary, reinitialize the subscribing database.

   The advantage of this strategy is that it restores the Publisher or Distributor without having to reenable replication and without having to regenerate snapshots from Publisher. The disadvantage of this strategy is that it relies on a coordinated backup of the publishing and distribution databases.

4. Drop and Re-create Subscriptions

   If your Subscriber has failed and the backup of the subscribing database is not available, an appropriate strategy for recovering is to drop and re-create the subscription. The strategy requires you to:

   1. Drop the subscription at the Publisher (unless the subscription is anonymous).
   2. Re-create the subscription at the Subscriber.

   One advantage of this strategy is that it can be done easily. Another advantage is that the same strategy can be used with merge replication, thereby providing a unified strategy in applications that use all three types of replication. The disadvantage of this strategy is that it can require processing and network resources in an environment where network connections are slow or processing power limited.

5. Restore Subscribers, and then Validate Subscriptions

   If your Subscriber has failed and there is an available backup of the subscribing database, an appropriate strategy for recovering is to restore the Subscriber and validate the subscription database. The strategy requires:

   1. Restore the subscribing database from the backup.
   2. Run data validation on the subscribing database to determine if the Subscriber should be reinitialized.
   3. If necessary, reinitialize the subscribing database.

The advantage of this strategy is that it avoids having to apply an initial snapshot to the Subscriber if data validation succeeds. The disadvantage of this strategy is that if data validation fails, it requires reinitializing the subscribing database with a new snapshot.

See Also