Common Actions Requiring an Updated Backup
If you perform regular log backups, any replication-related changes should be captured in the log backups. If you don't perform log backups, perform a backup of the publication, distribution, subscription, msdb, and master databases after making modifications to your replication schema or topology.
Publication Database
Backup the publication database after:
- Creating new publications.
- Changing any publication property, including filtering.
- Adding articles to an existing publication.
- Performing a publication-wide reinitialization of subscriptions.
- Making a schema change on a published table.
- Performing on-demand script execution with sp_addscriptexec (Transact-SQL).
- Changing any article property.
- Dropping any publications.
- Dropping any articles.
- Disabling replication.
Distribution Database
Backup the distribution database after:
- Creating or modifying replication agent profiles.
- Modifying replication agent profile parameters.
- Changing the replication agent properties (including schedules) for any push subscriptions.
- A new range of identities is assigned by the automatic identity range management feature.
Subscription Database
Backup the subscription database after:
- Changing any subscription property.
- Changing the priority for a merge subscription at the Publisher.
- Dropping any subscriptions.
- Disabling replication.
msdb Database
Backup the msdb system database at the appropriate node after:
- Enabling or disabling replication.
- Adding or dropping a distribution database (at the Distributor).
- Enabling or disabling a database for publishing (at the Publisher).
- Creating or modifying replication agent profiles (at the Distributor).
- Modifying any replication agent profile parameters (at the Distributor).
- Changing the replication agent properties (including schedules) for any push subscriptions (at the Distributor).
- Changing the replication agent properties (including schedules) for any pull subscriptions (at the Subscriber).
- Creating a DTS package associated with a transactional publication that uses transformable subscriptions (at the Distributor and Subscriber).
- Adding or dropping a transformable subscription (at the Distributor and Subscriber).
master Database
Backup the master system database at the appropriate node after:
- Enabling or disabling replication.
- Adding or dropping a distribution database (at the Distributor).
- Enabling or disabling a database for publishing (at the Publisher).
- Adding the first or dropping the last publication in any database (at the Publisher).
- Adding the first or dropping the last subscription in any database (at the Subscriber).
- Enabling or disabling a Publisher at a Distribution Publisher (at the Publisher and Distributor).
See Also
Concepts
Backing Up and Restoring Replicated Databases
Other Resources
Backing Up and Restoring Databases in SQL Server