I am pretty sure that Differential Backups with Replication is ok, but I am not finding anything that clearly says it. Nor am I finding anything indicating issues with it. Just looking for validation that two are completely compatible.
I have a server that has transaction replication and am considering a change to start using differential backups.
If the replication has to be rebuilt, a snapshot "copy" of the database is taken. I am not seeing any way to ensure that happens with COPY_ONLY.
Transactional replication is implemented by the SQL Server Snapshot Agent, Log Reader Agent, and Distribution Agent. The Snapshot Agent prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder, and records synchronization jobs in the distribution database on the Distributor.
I know that a full back up that does not use COPY_ONLY will distroy the backup chain if using Differential Backups.
A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored.
Question
Is there anything I need to address or adjust for if using Differential Backups and Replication?
Best Answer
I used to manage an environment where we had transactional replication and the backup schedule was Weekly Full, Daily Diff and log backups several times per day.
The backups didn't have any effect on replication. We had a few dozen publications consisting of hundreds of articles. If we had an issue with one, we would reset it and it would simply snapshot the tables in that publication.
The way it worked was that SQL did something similar to a select * from the tables involved along with reading their schema and uploading them to snapshot files on the distributor. Then it dropped or truncated the destination tables on the replica server and used the files to rebuild them.