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?
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:
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.
