Edit

Share via

Enable Coordinated Backups for Transactional Replication

  • Article

Applies to: SQL Server

When enabling a database for transactional replication, you can specify that all transactions must be backed up before being delivered to the distribution database. You can also enable coordinated backup on the distribution database so that the transaction log for the publication database is not truncated until transactions that have been propagated to the Distributor have been backed up. For more information, see Strategies for Backing Up and Restoring Snapshot and Transactional Replication.

Note

Using the sync with backup option on the distribution database is not compatible when the publisher database is part of an availability group and could lead to the following error: The process could not execute 'sp_repldone/sp_replcounters' on 'machinename\instance', Possible inconsistent state in the distribution database, Get help: http://help/MSSQL_REPL20011 (Source: MSSQLServer, Error number: 18846)

To enable coordinated backups for a database published with transactional replication

  1. At the Publisher, use the SELECT DATABASEPROPERTYEX(DB_NAME(),'IsSyncWithBackup') DATABASEPROPERTYEX (Transact-SQL) function to return the IsSyncWithBackup property of the publication database. If the function returns 1, coordinated backups are already enabled for the published database.

  2. If the function in step 1 returns 0, execute sp_replicationdboption (Transact-SQL) at the Publisher on the publication database. Specify a value of sync with backup for @optname, and true for @value.

    Note

    If you change the sync with backup option to false, the truncation point of the publication database will be updated after the Log Reader Agent runs, or after an interval if the Log Reader Agent is running continuously. The maximum interval is controlled by the –MessageInterval agent parameter (which has a default of 30 seconds).

To enable coordinated backups for a distribution database

  1. At the Distributor, use the DATABASEPROPERTYEX (Transact-SQL) function to return the IsSyncWithBackup property of the distribution database. If the function returns 1, coordinated backups are already enabled for the distribution database.

  2. If the function in step 1 returns 0, execute sp_replicationdboption (Transact-SQL) at the Distributor on the distribution database. Specify a value of sync with backup for @optname and true for @value.

To disable coordinated backups

  1. At either the Publisher on the publication database or at the Distributor on the distribution database, execute sp_replicationdboption (Transact-SQL). Specify a value of sync with backup for @optname and false for @value.

Examples

A. Retrieve the IsSyncWithBackup property for the current database

This example returns the IsSyncWithBackup property for the current database:

SQL 
SELECT DATABASEPROPERTYEX(DB_NAME(),'IsSyncWithBackup')`

B. Retrieve the IsSyncWithBackup property for a specific database

This example returns the IsSyncWithBackup property for the database NameOfDatabaseToCheck:

SQL 
SELECT DATABASEPROPERTYEX('NameOfDatabaseToCheck','IsSyncWithBackup')`