Strategies for Backing Up and Restoring Snapshot and Transactional Replication

Article
12/03/2008

There are three areas to consider when designing a backup and restore strategy for snapshot and transactional replication:

Which databases to backup.
Backup settings for transactional replication.
The steps required to restore a database, which depends on the type of replication and options chosen.

This topic covers each of these areas in the next three sections. For information about backup and restore for Oracle publishing, see Backup and Restore for Oracle Publishers.

Backing up Databases

For snapshot and transactional replication, you should back up the following databases regularly:

The publication database at the Publisher.
The distribution database at the Distributor.
The subscription database at each Subscriber.
The master and msdb system databases at the Publisher, Distributor and all Subscribers. These databases should be backed up at the same time as each other and the relevant replication database. For example, back up the master and msdb databases at the Publisher at the same time you back up the publication database. If the publication database is restored, ensure that the master and msdb databases are consistent with the publication database in terms of replication configuration and settings.

If you perform regular log backups, any replication-related changes should be captured in the log backups. If you don't perform log backups, a backup should be performed whenever a setting relevant to replication is changed. For more information, see Common Actions Requiring an Updated Backup.

Backup Settings for Transactional Replication

Transactional replication includes the sync with backup option, which can be set on the distribution database and the publication database:

It is recommended to set this option on the distribution database in all cases.
Setting this option on the distribution database ensures that transactions in the log of the publication database will not be truncated until they have been backed up at the distribution database. The distribution database can be restored to the last backup, and any missing transactions are delivered from the publication database to the distribution database; replication continues unaffected.
Setting this option on the distribution database has no effect on replication latency. However, it will delay the truncation of the log on the publication database until the corresponding transactions in the distribution database have been backed up (which can result in a larger transaction log in the publication database).
It is recommended to set this option on the publication database if your application can tolerate additional latency.
Setting this option on the publication database ensures that transactions are not delivered to the distribution database until they are backed up at the publication database. The last publication database backup can then be restored at the Publisher without any possibility of the distribution database having transactions that the restored publication database does not have.
Latency and throughput are affected because transactions cannot be delivered to the distribution database until they have been backed up at the Publisher. For example, if the transaction log is backed up every five minutes, there is an additional five minutes of latency between a transaction being committed at the Publisher and the transaction being delivered to the distribution database and subsequently the Subscriber.

Note

The sync with backup option ensures consistency between the publication database and the distribution database, but it does not guarantee against data loss. For example, if the transaction log is lost, transactions committed since the last transaction log backup will not be available in the publication database or the distribution database. This is the same behavior as a non-replicated database.

To set the sync with backup option

Replication Transact-SQL programming: How to: Enable Coordinated Backups for Transactional Replication (Replication Transact-SQL Programming)

Restoring Databases Involved in Replication

It is possible to restore all databases in a replication topology if recent backups are available and the proper steps are followed. The restore steps for the publication database depend on the type of replication and options used, but the restore steps for all other databases are independent of the type and options.

Replication supports restoring replicated databases to the same server and database from which the backup was created. If you restore a backup of a replicated database to another server or database, replication settings cannot be preserved. In this case, you must recreate all publications and subscriptions after backups are restored.

Publisher

There are restore steps provided for the following types of replication:

Snapshot replication
Read-only transactional replication
Transactional replication with updating subscriptions
Peer-to-peer transactional replication

The restore of the msdb and master databases, which are also covered in this section, is the same for all four types.

Publication Database: Snapshot Replication

Restore the latest backup of the publication database. Go to step 2.
Does the publication database backup contain the latest configuration for all publications and subscriptions? If yes, then the restore is complete. If no, go to step 3.
Remove the replication configuration from the Publisher, Distributor and Subscribers, and then recreate the configuration. Restore is complete.
For more information about removing replication, see Removing Replication and sp_removedbreplication (Transact-SQL).

Publication Database: Read-Only Transactional Replication

Restore the latest backup of the publication database. Go to step 2.
Was the sync with backup setting enabled on the publication database prior to the failure? If yes, go to step 3, if no go to step 5.
If the setting is enabled, the query SELECT DATABASEPROPERTYEX('<PublicationDatabaseName>', 'IsSyncWithBackup'); returns '1'.
Is the restored backup complete and up-to-date; does it contain the latest configuration for all publications and subscriptions? If yes, then the restore is complete. If no, go to step 4.
The configuration information in the restored publication database is not up-to-date, so you must ensure that the Subscribers have all outstanding commands in the distribution database, and then drop and recreate the replication configuration:
1. Run the Distribution Agent until all Subscribers are synchronized with the outstanding commands in the distribution database. Verify that all commands are delivered to Subscribers by using the Undistributed Commands tab in Replication Monitor or by querying the MSdistribution_status view in the distribution database. Go to step b.
  For more information about running the Distribution Agent, see How to: Start and Stop a Replication Agent (SQL Server Management Studio) and Programming Replication Agent Executables.
  For more information on verifying commands, see How to: View Replicated Commands and Other Information in the Distribution Database (Replication Transact-SQL Programming) and How to: View Information and Perform Tasks for the Agents Associated With a Subscription (Replication Monitor).
2. Remove the replication configuration from the Publisher, Distributor and Subscribers, and then recreate the configuration. When you recreate subscriptions, specify that the Subscriber already has the data. The restore is complete.
  For more information about removing replication, see Removing Replication and sp_removedbreplication (Transact-SQL).
  For more information about specifying that the Subscriber already has the data, see How to: Initialize a Subscription Manually (SQL Server Management Studio) and How to: Initialize a Subscription Manually (Replication Transact-SQL Programming).
The sync with backup option was not set on the publication database, so transactions that were not included in the restored backup might have been delivered to the Distributor and Subscribers. You must now ensure that Subscribers have all outstanding commands in the distribution database, and then manually apply to the publication database any transactions not included in the restored backup:

Important

Performing this process can result in published tables being restored to a point in time that is more recent than the point in time of other non-published tables restored from the backup.
1. Run the Distribution Agent until all Subscribers are synchronized with the outstanding commands in the distribution database. Verify that all commands are delivered to Subscribers by using the Undistributed Commands tab in Replication Monitor or by querying the MSdistribution_status view in the distribution database. Go to step b.
  For more information about running the Distribution Agent, see How to: Start and Stop a Replication Agent (SQL Server Management Studio) and Programming Replication Agent Executables.
  For more information on verifying commands, see How to: View Replicated Commands and Other Information in the Distribution Database (Replication Transact-SQL Programming) and How to: View Information and Perform Tasks for the Agents Associated With a Subscription (Replication Monitor).
2. Use the tablediff Utility or another tool to manually synchronize the Publisher with the Subscriber, which allows you to recover data from the subscription database that was not contained in the publication database backup. Go to step c.
  For more information about the tablediff utility, see How to: Compare Replicated Tables for Differences (Replication Programming).
3. Is the restored backup complete and up-to-date; does it contain the latest configuration for all publications and subscriptions? If yes, execute the stored procedure sp_replrestart (Transact-SQL) to resynchronize the Publisher metadata with the Distributor metadata. The restore is complete. If no, go to step d.
4. Remove the replication configuration from the Publisher, Distributor and Subscribers, and then recreate the configuration. When you recreate subscriptions, specify that the Subscriber already has the data. The restore is complete.
  For more information about removing replication, see Removing Replication and sp_removedbreplication (Transact-SQL).
  For more information about specifying that the Subscriber already has the data, see How to: Initialize a Subscription Manually (SQL Server Management Studio) and How to: Initialize a Subscription Manually (Replication Transact-SQL Programming).

Publication Database: Transactional Replication with Updating Subscriptions

Restore the latest backup of the publication database. Go to step 2.
Run the Distribution Agent until all Subscribers are synchronized with the outstanding commands in the distribution database. Verify that all commands are delivered to Subscribers by using the Undistributed Commands tab in Replication Monitor or by querying the MSdistribution_status view in the distribution database. Go to step 3.
For more information about running the Distribution Agent, see How to: Start and Stop a Replication Agent (SQL Server Management Studio) and Programming Replication Agent Executables.
For more information on verifying commands, see How to: View Replicated Commands and Other Information in the Distribution Database (Replication Transact-SQL Programming) and How to: View Information and Perform Tasks for the Agents Associated With a Subscription (Replication Monitor).
If you are using queued updating subscriptions, connect to each Subscriber and delete all rows from the table MSreplication_queue in the subscription database. Go to step 4.

Note

If you are using queued updating subscriptions and any tables contain identity columns, you must ensure that the correct identity ranges are assigned after a restore. For more information, see Replicating Identity Columns.
You must now ensure that Subscribers have all outstanding commands in the distribution database, and then manually apply to the publication database any transactions not included in the restored backup:

Important

Performing this process can result in published tables being restored to a point in time that is more recent than the point in time of other non-published tables restored from the backup.
1. Run the Distribution Agent until all Subscribers are synchronized with the outstanding commands in the distribution database. Verify that all commands are delivered to Subscribers by using Replication Monitor or by querying the MSdistribution_status view in the distribution database. Go to step b.
2. Use the tablediff Utility or another tool to manually synchronize the Publisher with the Subscriber, which allows you to recover data from the subscription database that was not contained in the publication database backup. Go to step c.
  For more information about the tablediff utility, see How to: Compare Replicated Tables for Differences (Replication Programming).
3. Is the restored backup complete and up-to-date; does it contain the latest configuration for all publications and subscriptions? If yes, execute the stored procedure sp_replrestart (Transact-SQL) to resynchronize the Publisher metadata with the Distributor metadata. The restore is complete. If no, go to step d.
4. Remove the replication configuration from the Publisher, Distributor and Subscribers, and then recreate the configuration. When you recreate subscriptions, specify that the Subscriber already has the data. The restore is complete.
  For more information about removing replication, see Removing Replication and sp_removedbreplication (Transact-SQL).
  For more information about specifying that the Subscriber already has the data, see How to: Initialize a Subscription Manually (SQL Server Management Studio) and How to: Initialize a Subscription Manually (Replication Transact-SQL Programming).

Publication Database: Peer-to-Peer Transactional Replication

In the following steps, publication databases A, B, and C are in a peer-to-peer transactional replication topology. Databases A and C are online and functioning properly; Database B is the database to be restored.

Run the Distribution Agents to synchronize the subscriptions at databases A and C. Go to step 2.
For more information about running the Distribution Agent, see How to: Start and Stop a Replication Agent (SQL Server Management Studio) and Programming Replication Agent Executables.
If the distribution database that B uses is still available, run Distribution Agents to synchronize subscriptions between databases B and A and databases and B and C. Go to step 3.
Remove metadata from the distribution database that B uses by executing sp_removedistpublisherdbreplication (Transact-SQL) at the distribution database for B. Go to step 4.
At databases A and C, drop the subscriptions to the publication at database B. Go to step 5.
For more information about dropping subscriptions, see Subscribing to Publications.
Perform a log backup or full backup of database A. Go to step 6.
Restore the backup of database A at database B. Database B now has the data from database A, but not the replication configuration. When you restore a backup to another server, replication is removed, so replication has been removed from database B. Go to step 7.
Recreate the publication at database B, and then recreate subscriptions between databases A and B (subscriptions involving database C are handled at a later stage):
1. Recreate the publication at database B. Go to step b.
2. Recreate the subscription at database B to the publication at database A, specifying that the subscription should be initialized with a backup (a value of initialize with backup for the @sync_type parameter of sp_addsubscription (Transact-SQL)). Go to step c.
3. Recreate the subscription at database A to the publication at database B, specifying that the Subscriber already has the data (a value of replication support only for the @sync_type parameter of sp_addsubscription (Transact-SQL)). Go to step 8.
  The most straightforward way to perform steps a-c is to use the Configure Peer-to-Peer Topology Wizard. For more information, see How to: Configure Peer-to-Peer Transactional Replication (SQL Server Management Studio). You can also use stored procedures; for more information, see How to: Configure Peer-to-Peer Transactional Replication (Replication Transact-SQL Programming).
Run the Distribution Agents to synchronize the subscriptions at databases A and B. If there are any identity columns in published tables, go to step 9. If not, go to step 10.
After the restore, the identity range you assigned for each table in database A would also be used in database B. Ensure that the restored database B has received all changes from the failed database B that were propagated to database A and database C; and then reseed the identity range for each table.
1. Execute sp_requestpeerresponse (Transact-SQL) at database B and retrieve the output parameter @request_id. Go to step b.
2. By default the Distribution Agent is set to run continuously, so tokens should be sent to all nodes automatically. If the Distribution Agent is not running in continuous mode, run the agent. For more information, see Programming Replication Agent Executables or How to: Start and Stop a Replication Agent (SQL Server Management Studio). Go to step c.
3. Execute sp_helppeerresponses (Transact-SQL), providing the @request_id value retrieved in step b. Wait until all nodes indicate they have received the peer request. Go to step d.
4. Use DBCC CHECKIDENT to reseed each table in database B to ensure that an appropriate range is used. Go to step 10.
  For more information about managing identity ranges, see the "Assigning ranges for manual identity range management" section of Replicating Identity Columns.
At this point, database B and database C are not directly connected, but they will receive changes through database A. To connect database B and database C, complete steps 11-13.
Quiesce the system. Quiescing a system involves stopping activity on published tables at all nodes and ensuring that each node has received all changes from all other nodes:
1. Stop all activity on published tables in the peer-to-peer topology. Go to step b.
2. Execute sp_requestpeerresponse (Transact-SQL) at database B and retrieve the output parameter @request_id. Go to step c.
3. By default the Distribution Agent is set to run continuously, so tokens should be sent to all nodes automatically. If the Distribution Agent is not running in continuous mode, run the agent. Go to step d.
4. Execute sp_helppeerresponses (Transact-SQL), providing the @request_id value retrieved in step b. Wait until all nodes indicate they have received the peer request. Go to step 12.
Recreate the subscription between databases B and C:
1. Recreate the subscription at database B to the publication at database C, specifying that the subscription should be initialized from a backup. Go to step b.
2. Recreate the subscription at database C to the publication at database B, specifying that the Subscriber already has the data. Go to step 13.
Run the Distribution Agents to synchronize the subscriptions at databases B and C. The restore is complete.

msdb Database (Publisher)

Restore the latest backup of the msdb database.
Is the restored backup complete and up-to-date; does it contain the latest configuration for all publications and subscriptions? If yes, recovery is complete. If no, go to step 3.
Recreate the subscription cleanup job from your replication scripts. Recovery is complete.

master Database (Publisher)

Restore the latest backup of the master database.
Ensure that the database is consistent with the publication database in terms of replication configuration and settings.

Databases at the Distributor

Distribution Database

Restore the latest backup of the distribution database.
Was the sync with backup setting enabled on the distribution database prior to the failure? If yes, go to step 3, if no go to step 4.
If the setting is enabled, the query SELECT DATABASEPROPERTYEX('<DistributionDatabaseName>', 'IsSyncWithBackup'); returns '1'.
Is the restored backup complete and up-to-date; does it contain the latest configuration for all publications and subscriptions? If yes, recovery is complete. If no, go to step 4.
Either the configuration information in the restored distribution database is not up-to-date, or the sync with backup option was not set on the distribution database (after restore, the distribution database might be missing transactions that were committed at the Publisher but have not yet been delivered to Subscribers). Drop and recreate replication, and then run validation:
1. Remove the replication configuration from the Publisher, Distributor and Subscribers, and then recreate the configuration. When you recreate subscriptions, specify that the Subscriber already has the data. Go to step b.
  For more information about removing replication, see Removing Replication and sp_removedbreplication (Transact-SQL).
  For more information about specifying that the Subscriber already has the data, see How to: Initialize a Subscription Manually (SQL Server Management Studio) and How to: Initialize a Subscription Manually (Replication Transact-SQL Programming).
2. Mark all publications for validation. Reinitialize any subscriptions that fail validation. Recovery is complete.
  For more information about validation, see Validating Replicated Data.
  For more information about reinitialization, see Reinitializing a Subscription.

msdb Database (Distributor)

Restore the latest backup of the msdb database.
Is the restored backup complete and up-to-date; does it contain the latest configuration for all publications and subscriptions? If yes, recovery is complete. If no, go to step 3.
Remove the replication configuration from the Publisher, Distributor and Subscribers, and then recreate the configuration. When you recreate subscriptions, specify that the Subscriber already has the data. Go to step 4.
For more information about removing replication, see Removing Replication and sp_removedbreplication (Transact-SQL).
For more information about specifying that the Subscriber already has the data, see How to: Initialize a Subscription Manually (SQL Server Management Studio) and How to: Initialize a Subscription Manually (Replication Transact-SQL Programming).
Mark all publications for validation. Reinitialize any subscriptions that fail validation. Recovery is complete.
For more information about validation, see Validating Replicated Data.
For more information about reinitialization, see Reinitializing a Subscription.

master Database (Distributor)

Restore the latest backup of the master database.
Ensure that the database is consistent with the publication database in terms of replication configuration and settings.

Databases at the Subscriber

Subscription Database

Is the latest subscription database backup more recent than the maximum distribution retention setting on the distribution database (this determines whether the Distributor still has all the commands necessary to bring the Subscriber up-to-date)? If yes, go to step 2. If no, reinitialize the subscription; recovery is complete.
To determine the maximum distribution retention setting, execute sp_helpdistributiondb (Transact-SQL) and retrieve the value from the max_distretention column (this value is in hours).
For more information about reinitializing a subscription, How to: Reinitialize a Subscription (SQL Server Management Studio) and How to: Reinitialize a Subscription (Replication Transact-SQL Programming).
Restore the latest subscription database backup. Go to step 3.
If the subscription database contains only push subscriptions, go to step 4. If the subscription database contains any pull subscriptions: is the subscription information current; does it include all tables and options that were set at the time of failure. If yes, go to step 4. If no, reinitialize the subscription; recovery is complete.
Run the Distribution Agent to synchronize the Subscriber. Recovery is complete.
For more information about running the Distribution Agent, see How to: Start and Stop a Replication Agent (SQL Server Management Studio) and Programming Replication Agent Executables.

msdb Database (Subscriber)

Restore the latest backup of the msdb database. Are pull subscriptions used at this Subscriber. If no, the restore is complete. If yes, go to step 2.
Is the restored backup complete and up-to-date; does it contain the latest configuration for all pull subscriptions? If yes, recovery is complete. If no, go to step 3.
Drop and recreate the pull subscriptions. When you recreate the subscriptions, specify that the Subscriber already has the data. The restore is complete.
For more information about dropping subscriptions, see Subscribing to Publications.
For more information about specifying that the Subscriber already has the data, see How to: Initialize a Subscription Manually (SQL Server Management Studio) and How to: Initialize a Subscription Manually (Replication Transact-SQL Programming).

master Database (Subscriber)

Restore the latest backup of the master database.
Ensure that the database is consistent with the publication database in terms of replication configuration and settings.