0

Suppose I have configured full backup for every Sunday 1AM, differential backup every day at 1AM and log backups every hours from 12AM.

So, every day the differential and log backups collide at 1AM.

On Sunday all 3 backups collide at 1AM.

How does SQL server backups work under this scenario?

1
  • Why don't you test yourself and see? It will run normally - only thing which could/would happen is - it will run slower when running all at the same time. Apr 25, 2022 at 6:17

1 Answer 1

0

You could easily schedule your diffs to run Mon - Sat and that will potentially make the full backups faster. Diffs don't break the backup chain so in the case of restores you would still apply your full backup, then the last diff backup taken after the full you are applying (I call this the base), then any log backups taken after the last diff. You can also always skip restoring any diff at all and just restore the full and a whole lot of logs.

The reason cancelling the Sunday diff will make the Sunday full faster is it will reduce the extra load on the disk(s) and CPU (assuming you are compressing) from both backups occurring at the same time. Making database backups as fast as possible will benefit the database transaction log file as it can't be truncated until the full is finished.

It's normal for log backups to occur during full backups because full backups can take hours depending on the database size, disk speed, and network connection to the backup disk. Be sure to stripe them to make them even faster! You want your hourly log backup to continue to run during full backups so that in the case your full backup fails you can still meet your RPO.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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