gebeurtenis
31 mrt, 23 - 2 apr, 23
De grootste SQL-, Fabric- en Power BI-leerevenement. 31 maart – 2 april. Gebruik code FABINSIDER om $ 400 te besparen.
Zorg dat u zich vandaag nog registreertDeze browser wordt niet meer ondersteund.
Upgrade naar Microsoft Edge om te profiteren van de nieuwste functies, beveiligingsupdates en technische ondersteuning.
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.
Notitie
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)
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.
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.
Notitie
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).
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.
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.
This example returns the IsSyncWithBackup
property for the current database:
SELECT DATABASEPROPERTYEX(DB_NAME(),'IsSyncWithBackup')`
This example returns the IsSyncWithBackup
property for the database NameOfDatabaseToCheck
:
SELECT DATABASEPROPERTYEX('NameOfDatabaseToCheck','IsSyncWithBackup')`
gebeurtenis
31 mrt, 23 - 2 apr, 23
De grootste SQL-, Fabric- en Power BI-leerevenement. 31 maart – 2 april. Gebruik code FABINSIDER om $ 400 te besparen.
Zorg dat u zich vandaag nog registreertTraining
Module
Back-up en herstel implementeren voor Azure Cosmos DB for NoSQL - Training
Meer informatie over het gebruik van de twee back-upmodellen die Azure Cosmos DB biedt.
Certificering
Microsoft Certified: Azure Database Administrator Associate - Certifications
Beheer een SQL Server-databaseinfrastructuur voor cloud-, on-premises en hybride relationele databases met behulp van de relationele Microsoft PaaS-databaseaanbiedingen.
Documentatie
Back Up and Restore Replicated Databases - SQL Server
Review overview information and links to additional information about backup and restore strategies for each type of replication in SQL Server.
Strategies for Backing Up and Restoring Snapshot and Transactional Replication - SQL Server
Learn about considerations for designing a backup and restore strategy for snapshot and transactional replication in SQL Server.
Replication Log Reader Agent - SQL Server
The Replication Log Reader Agent monitors SQL Server databases configured for transactional replication and copies transactions to the distribution database.