Skip to content

Latest commit

 

History

History
63 lines (45 loc) · 4.67 KB

enable-coordinated-backups-for-transactional-replication.md

File metadata and controls

63 lines (45 loc) · 4.67 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom helpviewer_keywords dev_langs
Enable coordinated backups (Transactional)
Learn to enable coordinated backups on the distribution database so that the transaction log for the Transactional Replication publication database is not truncated until transactions that have been propagated to the Distributor have been backed up.
MashaMSFT
mathoma
03/07/2017
sql
replication
how-to
updatefrequency5
transactional replication, backup and restore
sp_replicationdboption
sync with backup [SQL Server replication]
coordinated backups [SQL Server replication]
backups [SQL Server replication], transactional replication
TSQL

Enable Coordinated Backups for Transactional Replication

[!INCLUDE 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:

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

B. Retrieve the IsSyncWithBackup property for a specific database

This example returns the IsSyncWithBackup property for the database NameOfDatabaseToCheck:

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