Full, Differential, and Log backups run at same time. Which runs first?

Question

Full, Differential, and Log backups run at same time. Which runs first?

SQLChao

Mr or Mrs. 500

Points: 534
More actions
March 7, 2016 at 1:23 pm

#311878

I saw a question today on SO asking if all 3 types of backups are scheduled to run at the same time which one will actually run first. From my testing the Full ran first regardless of which schedule was created first. I mention that because I found this in a related question https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0948f4a7-ff06-4c1e-8518-bb5afd3b28e2/differential-backup-and-transaction-log-at-the-same-time?forum=sqldisasterrecovery. In short it says if two schedules have the same time the schedule that was created first will run first.
Can someone else who has more experience confirm or explain how this is supposed to work?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

Logs don't matter. They are independent of the fulls and diffs.

In terms of scheduling them at the same time, not sure here. I would think that they would go based on either job creation time or luck of the draw. Which Agent process spins up and goes.

The diff should complete first, which makes sense. It should be less data.

If they kick off at the same time, I'd guess the diff isn't affected by the full's process, which would need to reset the baseline for the diff. Until the full is complete, the diff still has to look at the previous full for a list of changed pages.

SQLChao Mr or Mrs. 500 Points: 534 More actions · Answer 2

I've tried creating the jobs and schedules in different orders and the full appears to run first in my testing. I just wasn't sure if this was a documented thing. I guess it doesn't matter as long as that full works.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

I think that's true. Perhaps the full has priority for some reason.

It shouldn't really matter. SQL Server tracks what has changed and what logs are needed to ensure that recovery can run.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Full and log can run at the same time, so can diff and log. Diff and full block each other, so which ever starts first will run to completion first.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass