In my environment, there are servers running on native backup and Ola Hallengren plans. Our servers are a combination of 2008, 2012 and 2014. All the full backups are taken at 12am and log backups are taken every 15 minutes.

I have never accounted for Daylight Saving time before, so please tell me what adjustments I should make.

Will the 12am full backups be affected and what happens to the log backups?

  • 6
    Quite frankly I would run my servers in UTC. Oct 30, 2017 at 13:07
  • Ours are CST unfortunately
    – SqlNovice
    Oct 30, 2017 at 13:13
  • 1
    Sure, but to be fair, that's not hard-coded on the motherboard. Oct 30, 2017 at 13:33

1 Answer 1


SQL Server expert Paul Randal addresses this very topic in How does daylight savings time affect disaster recovery?. It's a relatively short post, so I'm quoting it in its entirety. Since you were concerned about your transaction log backups, I also highlighed a bit of the post to draw that to your attention.

It’s common knowledge that SQL Server copes with daylight savings time (DST) correctly so why should you care?

Well, it’s not so common knowledge that at the end of DST when the clocks go back an hour (always at 02:00 in the U.S.), SQL Agent essentially pauses for an hour (in at least SS2000 onwards). This means that if you have a job that’s doing something every 15 minutes, there will be a gap of 75 minutes between the job execution at 01:45 and the job execution at 02:00. This happens because at 02:00, the time is set back to 01:00 but the next run time of all the jobs remains the same – so your job cannot execute until it’s next scheduled time of 02:00. So, in the northern hemisphere every Fall, and in the southern hemisphere every Spring, you lose an hour’s worth of SQL Agent jobs. Still, why should you care?

Well, it depends what the jobs are that get delayed by an hour. If you have a job that takes a log backup every 15 mins then on the day DST ends, there’s actually a gap of 75 minutes between log backups. If you have a Service Level Agreement (SLA) that limits the maximum amount of lost work to 15 minutes in the event of a disaster, then for those 75 minutes you’re exposed to potentially not being able to meet that SLA!

That could be a pretty big deal, especially if something goes wrong during that hour (no more or less likely than something going wrong at any other time, but still possible). In that case, you need to come up with an alternative solution. A couple of ways to get around the problem I can think of:

  • Have someone stay up late during that hour and take manual log backups.
  • Switch over to database mirroring, which continually streams the log to the redundant server and so isn’t affected the DST issue.

Both of these are viable solutions but I think the best one is to create a SQL Agent job that runs at 01:59 and creates extra backup jobs to run at 01:00, 01:15, 01:30, and 01:45. I don’t see why this shouldn’t be possible. At 10:36 this morning I created a simple agent job to print the date to a file and set it to execute at 09:40 – in the past. I then set my system time back one hour and the job executed perfectly. The only downside of this solution is that you need to create and schedule the extra jobs using the T-SQL Agent SPs embedded in job steps for your 01:59 job – tedious but not hard. Maybe someone could send me a script and I’ll blog it as a follow-on?

So with DST coming to an end on November 4th this is definitely something for you to be aware of even if you don’t want to go to the trouble of coping with the extra hour’s exposure. As an aside – the dates when DST starts and ends changed this year. KB article 931975 discusses which parts of SQL Server aren’t aware of the changed dates and what you can do about it.

  • So I could see a potential data loss for 75 minutes...If I am ok with that do i need to change any setting sor I can leave it as is
    – SqlNovice
    Oct 30, 2017 at 13:20
  • If you're ok with the potential of losing the 75 minutes until the next regularly scheduled log backup, then I don't think you need to make any changes. Oct 30, 2017 at 13:21
  • Moreover the server that I am worried is an always on availability group so i guess if anything goes wrong i can switch over.
    – SqlNovice
    Oct 30, 2017 at 13:27
  • @SqlNovice assuming your availability group is on two servers that are not impacted by the same event, and that all events have committed to the other instance(s) = True. You might be taking more for granted that is safe to assume. PS servers are not in Availability Groups, databases are. (i.e. ""the server that I am worried is an always on availability group) Oct 30, 2017 at 14:09
  • Sorry my mistake the databases in availability group are one replica is syn mode and another in asyn mode
    – SqlNovice
    Oct 30, 2017 at 14:16

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.