Scheduling Backups and Schedule Conflicts

JasonHumberstone

Afternoon,

We are looking to do full backups once a day (say, at 1:00 am), differential backups hourly throughout the day and transaction log backups as often as possible (ideally, every 15 minutes or less).

Can you please confirm what is the simplest, most efficient way to accommodate this requirement whilst avoiding scheduling conflicts on the SQL Backup Pro timeline, please? Is it creating multiple disjoint schedules for the differential and transaction log backups? For example, for the transaction log backups, creating a different schedule for each hour in the day, leaving gaps in each to accommodate the differentials and full backups

Thanks,
Jason.

Dan B

Hi Jason,

You should be able to achieve this with minimal effort.

You can setup the FULL to run daily at 1:00am easily enough.

You can then set the differentials to run hourly 2am-12am by selecting hourly and setting the start and end times in the schedule GUI to exclude 1am.

You can then setup the transaction log schedule to run in 2 jobs, with 1 being every 30 minutes starting at 00:15:00 so it will run at quarter past and quarter to with a second job to run every hour starting at half past.

please do let me know if you need any further help setting this up.

JasonHumberstone

Hi Dan,

Thank you, that is very helpful.

I've set the jobs up as you say and I only get two conflicts; one at 01:15 and one at 01:30. These conflicts are between the full backups and the transaction log backups. I think I'm correct in saying the software is estimating how long the full backup of each database will take and it is determining (correctly) that the full backups and transaction log backups will overlap at those times.

I'm thinking these conflicts probably aren't that big of a deal and are probably unavoidable in certain cases anyway. Would you agree with that? 

Finally, could you confirm my understanding of the conflicts, please? My understanding is that there is fundamentally no problem at the SQL level with running a full backup and a transaction log backup at the same time, and that the conflicts in the SQL Backup Pro software are simply saying I won't be able to use the SQL Backup Pro timeline to restore the backups to the times where the conflicts occurred, but I will still be able to restore the backup manually via SQL to those time points. Is that correct?

Thanks,
Jason.

Dan B

Hi Jason,

I would agree with the conflicts "probably aren't that big of a deal".

You should also be able to use the GUI where the conflicts are FULL/DIFF/TRANSACTION Log backups. The conflicts option is really to show where you are running two fulls at the same time.

Given the setup you described daily FULL, HOURLY DIFF and QUARTERLY TRANS logs If you wanted to restore to 4 o clock the GUI would detect the FULL and then the DIFF needed. If You wanted to restore to 3:45 the GUI should correctly detect the FULL/DIFF and T-LOGS needed as well as restoring to 4:15.

You are also correct that at a SQL level running backups at the same time is not problematic at all.

JasonHumberstone

Hi Dan,

Ok that makes sense. Thanks for your help.

Regards,
Jason.