1

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?

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
4
  • Thank you for your answer. Did you ever do a restore where the replication snapshot could/would have broken the chain? I believe that the only way to tell the backup chain is broken is by trying to do a restore and finding out you don't have the required backup.
    – James Jenkins
    Feb 26, 2019 at 14:45
  • 1
    The replication snapshot has no effect on backups. In fact there were a few times we had issues with Database Mirroring and/or AlwaysOn replication and that had an effect on replication where the replicas would be behind and people who used them complained. The log backups would also be close to 0 KB until mirroring or always on caught up and then we would get a 20GB or so log backup along with replication back log.The replication snapshot is not a backup and it's just dumping the data into a compressed file to be sent to the replicas.
    – Alen
    Feb 26, 2019 at 15:03
  • And in all my years there, miraculously we never had to do a production restore. We'd restore data for QA or some backups for select data that was damaged, but never an up to the minute restore for a production server. The biggest issue i've had with differential backups was someone adding new files midweek without performing a full backup afterwards.
    – Alen
    Feb 26, 2019 at 15:05
  • Replication snapshots do not use backups, so do not affect the restore chain. However restoring the publisher database will most likely break replication (committed transactions from after the diff backup was taken have already been sent to the distributor and subscriber). So make sure you're including rebuild replication as part of your DR plan.
    – Andrew Bickerton
    Feb 28, 2019 at 10:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.