We know that the Distribution databases can be backed up. But when we want to restore a distribution database to live environment from a backup, the distribution database might be missing transactions that were committed at the Publisher but have not yet been delivered to Subscribers. What happens then? Remove the replication configuration from the Publisher, Distributor and Subscribers, re-create the configuration and reinitialize the subscriptions? Things get complex if we have more data and multiple subscribers.
So here I discuss a simple and useful backup strategy which is not so familiar to the general SQL server community, the sync with backup setting.
The sync with backup setting on the distribution database ensures that the transactions in the publication database will not be truncated until they have been moved to Distribution database and backed up at the Distribution database. The distribution database can be restored to the latest backup, and any missing transactions will be delivered from the publication database to the distribution database and replication continues unaffected. A live backup of the Distribution database can be created and this change will help us to recover the database with no data loss and also avoid re-creating the replication.
Steps to implement:
1. Stop the Log reader agent and Distribution agents
2. Change the Recovery model of the distribution database to Full using following query:
USE MASTER ALTER DATABASE <Name of the Distribution DB> SET RECOVERY FULL;
3. Execute a Full backup for the Distribution database
4. Create a Transaction Log backup plan for the Distribution database or configure Log shipping for the Distribution database:
5. Make sure the Transaction log backup or Log shipping works fine
6. Enable ‘sync with backup’ on the distribution database as follows:
USE MASTER sp_replicationdboption '<Name of the Distribution DB>', 'Sync with backup', True
7. Start the Log reader agent and Distribution agents
8. Monitor the growth of the Log file on the publisher and distributor
9. Run the following command on the distribution server to make sure that the sync with backup is enabled for distribution database
SELECT DATABASEPROPERTYEX('Distribution', 'IsSyncWithBackup')
Notes: The Log reader agent and Distribution agents must be stopped, which will halt the replication during the course of this implementation.