Strategies for Backing Up and Restoring Snapshot Replication

Snapshot replication is best used as a method for replicating data that changes infrequently or where the most up-to-date values (low latency) are not a requirement. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

Because snapshot replication propagates changes by generating and delivering a complete snapshot for the publication, it is not necessary to back up the publication database as frequently as it is backed up in transactional replication or merge replication. The publication database needs to be backed up when changes are made to existing publication properties or when new publications are added.

When you back up the Publisher, also back up the Distributor. While the backups are in progress, no new snapshot publications or subscriptions should be added. This ensures that when the Publisher and Distributor are restored, they both will both contain the same information.

Backing Up and Restoring the Publication Database

The Log Reader Agent is less important in back up and restoration than it is in transactional replication. The publication database needs to be backed up only when changes are made to existing publications (such as an article added or deleted, or schema changes on the publication database that affect the publication), or new publications are added.

Backing Up and Restoring the Distribution Database

Before backing up the distribution database, it is recommended that you run the Distribution Cleanup Task to make sure any unnecessary information is cleaned up and does not add to the time it takes to back up the distribution database.

The distribution database should be backed up at the same time as the publication database. During the back up, do not add new snapshot publications or subscriptions. 

Backing Up and Restoring the msdb Database

The msdb database contains the job definitions for replication agents that are run under the control of SQL Server Agent. To provide additional security against a total system failure, the msdb database on the Publisher, Distributor, and Subscribers (that use pull subscriptions) must be backed up periodically whenever a subscription is dropped, whenever a change is made to a replication agent, or when a new Publisher is added to the Distributor.

Backing Up and Restoring the master Database

When a new Subscriber is added, an entry for the Subscriber is added to the sysservers table in the master database on the Publisher. When a new Publisher is added to a Distributor, an entry for the Publisher is added to the sysservers table in the master database on the Distributor.

To restore replication after the loss of the Publisher or Distributor, back up the master database on the Publisher and Distributor each time a new Subscriber or Publisher is added (respectively).