Backing Up and Restoring Replication Databases
In addition to the regular backup and restore guidelines and procedures for Microsoft® SQL Server™ 2000, additional considerations for backing up and restoring the databases are involved in replication.
The considerations for backing up databases used in snapshot replication, transactional replication, or merge replication vary according to the role the server performs in replication and where the failure occurs in the replication topology.
To restore replication, back up some or all of the following regularly:
- Publisher
- Distributor
- Subscriber(s)
Your backup strategy will depend on your needs for restoring a replicated environment quickly, and on the degree of complexity you can tolerate in your backup plan. You only need to back up all databases if you want to restore any replica immediately from backup while minimizing the likelihood of data loss.
Maintaining a regular backup of the Publisher databases, and leveraging the SQL Server replication built-in ability to reinitialize one or more subscriptions on-demand provides a simple recovery strategy. This strategy can be used to support a large enterprise of mobile, occasionally connected Subscribers that otherwise would not typically participate in regular backup management at each node in the topology. You could further limit regular backups to your publication databases and rely on SQL Server replication scripting to provide a method for reestablishing replication if you need to restore the entire replication environment.
Another strategy includes backing up only the Publisher and the Distributor as long as the Publisher and Distributor are synchronized. This strategy allows you to restore a replication environment completely. Backing up a Subscriber is optional but can reduce the time it takes to recover from a failure of the Subscriber.
Basic backup plans can result in a longer time to restore the replication environment. If your application requires that you restore replication immediately, you may want to consider more complex backup and recovery strategies described later in this section.
In most situations, the publications and distribution databases should be backed up after adding or changing replication objects such as articles and subscriptions, or after a schema change is made that affects replication. If the distribution database is restored to a version that is before such a change, the publication database will have to be restored to a version before that change as well.
As part of any backup strategy, always keep a current script of your replication settings in a safe location. This should be done in addition to regular backups of the Publisher, Distributor and the Subscribers. In the event of a total server failure or the need to set up a test environment, you can modify the script by changing the server name references and using the script to help recover replication with the previous settings.
You should also script the enabling and disabling of replication. These scripts are part of the backup of the Publisher or Distributor.
For more information about generating SQL scripts for setting up or disabling replication, see Scripting Replication.
Backing Up the Publisher
Publication databases are the primary, or central source, of data in a replication topology; therefore, even the most basic recovery plan should include regular backups at the Publisher. Backing up the Publisher requires you to back up the publication database regularly on the server where the Publisher is located. Back up the publication database and then make transaction log backups and/or differential database backups. You can also back up the master and msdb system databases to protect against total loss of the system and not just the publication database. If you are shipping transaction logs to a warm standby server, back up the msdb system database regularly (which is required if log shipping is used).
Backing Up the Distributor
Backing up the Distributor involves backing up the distribution database, the msdb database, and the master system database. This allows you to recover from almost any type of failure without having to re-create publications or reconfigure replication.
Backing up the Distributor preserves the snapshot of the publication as well as the history, error, and replication agent information for your application. It allows you to recover faster in the event of a Publisher or Distributor failure because there is no need to re-establish replication. Particularly for transactional replication, this strategy requires coordination between backing up the publication database and the distribution database. SQL Server 2000 handles this coordination automatically. Back up the distribution database, and then make transaction log backups and differential database backups.
For more information, see Strategies for Backing Up and Restoring Transactional Replication.
Backing Up the Subscriber
A comprehensive backup recovery strategy may rely on reinitialization of subscriptions in the event that recovery is required, or may include regular backups of each subscription database and relevant system databases at the Subscriber. Backing up the Subscriber involves backing up the subscription database and, optionally, the msdb and master system databases. The msdb and master databases need to be backed up only if it is a Subscriber that uses pull subscriptions and only if there is a need to be able to restore after a total system loss.
Backup the subscriptions database and then make transaction log backups and incremental database backups.
Note Backing up each Subscriber is not required to reestablish replication after a failure. Under most circumstances, backing up the Publisher and Distributor regularly should be sufficient. If the cost of reinitializing a Subscriber is significantly greater than the cost of restoring it from a backup, and the complexity of managing backups among the replicas within the enterprise is manageable, you should consider backing up the Subscriber.