Do we *really* have to always have a full backup as part of a backup job?

[Rem700]

We're trying to backup our SQL server 2008 DBs.  I got frustrated with the lack of options in this new version and called tech support have them create the backup jobs for me for the SQL DBs.  The first call the tech tried telling me what to do but it just didn't make sense (part my fault for being unable to understand what he was saying with his heavy accent, and the other part being he was giving me directions for some over version that wasn't 2012).  So I called back a second time to get it working and after the tech spoke to another tech on how to schedule jobs for SQL he created a plan.

The plan isn't fitting our needs.  What I would like is the following:

Daily incremental backups of SQL starting at 6AM then repeating again every two hours till 4PM on Monday - Friday.  Then at 5PM (or anytime really after 4P) we'd run a full SQL DB backup.  Doesn't seem hard to build this but it's a different story in 2012.

The attached pic shows how the tech support agent configured this portion of BE.  He said it has to do a full backup daily in order for the differentials to work -- ok, fine, but why only a single differential and why does it run after the full?  Then it has to run another full on the weekend so it can truncate the logs.  This schedule doens't seem to fit our needs yet the scheduler doens't have the options we need.  It doens't make sense to run a full backup then a differential right after.  (does it?!)

[Colin_Weaver]

Hi there - just for info although I don't currently have a solution for that kind of schedule (which I see you have also posted a query on here https://www-secure.symantec.com/connect/forums/unable-restart-job-during-time-interval) , as part of general feedback on comments abouth 2012, I have a previously advised our engineering teams that a sequence similar to this is no longer possible when it was in 2010 R3.

Obviously I can't confirm any actions, this is really just to say we are aware of it

With regards the SQL Full backup comment in order to do transaction log, incremental or differential SQL backups you should only have to do a full backup after any interaction with SQL that might look like a backup by a different product. (for instance a VMware agent backup if the SQL server was virtual would need a full SQL backup after it to allow the other job types to run.) We obviously would recommend that you do run full backups periodically for other reasons.

[pkh]

I have offered a workaround here

https://www-secure.symantec.com/connect/forums/unable-restart-job-during-time-interval#comment-71522...

[GregOfBE]

You can't conveniently schedule your incrementals bi-hourly or hourly just during a range of time as you describe. But you can schedule mutiple incrementals in the same backup definition. So you could create and incremental for 6AM, another for 10AM, etc. through 4PM. Each one would use a "Days" recurrence pattern with the "Weekdays" pattern set.

So you'd have a FULL at 5pm on weekdays and 6 incrementals from 6am through 4pm.

This is not very convenient - but possible. This  recurrence pattern in the scheduler is something we are looking at improving for scenarios like this.

Also, one other note about SQL. SQL is treated special with regard to diff and incrementals. You can intermix differentials and log backups by adding multiple incrementals and in the SQL options, setting the method to either log or differential.

[pkh]

The user does not want to create multiple incremental jobs which is why I proposed that he use the hourly recurrance.  See the link I post earlier.

[Rem700]

pkh: thank you for the "how to" on using PowerShell.  I've setup two jobs, one with your implementation and the other with GregOfBE's.  I'll try them both and see which works best for me.

GregOfBE: Thank you for you explination and browsing of the forum.  When I was talking to the BE tech, this is *originally* how I setup the SQL backups (one job: 1 X full day's end, 6 X incremental), he said this wouldn't keep the DBs trimmed/compacted and would result in unnecessary, large DB growth.  Maybe he was off his game that day, or something.  I don't know.  I'll try this for a week and see how it works out. 

Thank you for your assistance.

[JuergenB]

Hi,

i am looking for a similar solution.

I would like to run a SQL Dump (with BE 2012 at the remote server) at 6:00 PM, this till take 10 Minutes.

Then after the SQL Dump, BE2012 should take a Full Backup at 6:30 PM with a schedule every day.
So i added a Full Backup Job at 6:30 PM with a recurrence Pattern of 1 day..

Then after the BE Full, i want to get Incremental backups from 8:00 AM till 5:45 PM ever 15 minutes (about 9 hours and 45 hours ).

I add a job ... Incremental, Recurrence Pattern Hours 15 minutes.

And i set the Start Time to 8:00 AM AND (now for the complex) i set the Option "Keep the job scheduled for 585 Minutes".

I think this will mean, that the job will run for the next 585 minutes time frame. After this time frame ended, the job will be reschuled to 8:00 AM next morning?

Is this correct?