Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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.
