weekly backups roll up on sql

[seapro220]

Hello all.
I'm racking my brain trying to find something simple, and because it's simple - I just can't see it. For the sql server, I've actually got 2 processes working. The sql person does a weekly backup, and multiple transaction logs during the day. She tried to use the veeam-backup, but was having issues doing daily transactional restores/searches so I want to go to a weekly veeam job, and keep 4 weeks for Murphy's sake.

Here's what I want to do - please suggest any viable options ..

1. create a new job for sql.
2. don't truncate the log files, so I can use sql-tools to do restores. The sql job truncates w/o issues.
3. run the job 1 time a week, and keep 4 weeks of 'full' restores in case something goes wrong. I don't want daily incremental jobs - just a 1x-week job and another reason to keep multiple weeks is for service-patches that are applied.
4. run a 'once a quarter' backup and keep that off-site.
5. is forward or reversed backups the best method - and why?

I have the truncate-log feature down (not selecting truncate the logs on advanced options) but am struggling on the 1x-week and creating the 'quarterly job'. Do I have to select a periodic monthly job and specify the month's I want a quarterly backup or create another quarterly-job? What I'm concerned about is space - mostly. If I have to create a 2nd job, to handle the quarterly backups job - can I specify that I only want to keep 1 quarter?

thoughts and suggestions?

[dellock6]

Hi Mark, a quick suggestion, see if sounds good:
- create a dedicated job
- forward incremental or reverse is the same, since you want to create full backups at every execution without incrementals, but let's say forward
- schedule the backup to run once per week on the day you want
- program the job to do active full on the exact same day you schedule it. In this way, every backup execution will create a full VBK file, as you wanted
- set retention points to 4
- for quarterly backup, simply copy the last vbk file in a secondary position, you can now use Veeam Cloud Edition to do so

About space consumption, for sure having 4 full backups will eat more space than usual full+incremental rotation, do some calculations to better size your backup storage.

Luca.

[seapro220]

Hello.
I'd thought of 'ticking' the option, "perform active full backups periodically" and trying to set it for the same day - but thought that with the 'normal' weekly running, and then the 'active full' also scheduled that I'd end up with 2 backup's for that day. Is this not correct, or will the normal 'weekly' just get rolled up/transformed into the weekly backup job/file? Using, or taking the last 'quarter' file and moving it off to another location seems like the best option instead of creating another 'job' just for that. Is there any preferred method of managing those extra files? Never mind - as I think about it, the job is scheduled to automatically prune to only 4 copies...and I've just taken 1 of them out of the 'mix'. Good Idea.

mark

[veremin]

If you follow Luca’s advice, only an active full backup will be created on this day.

Hope this helps.
Thanks.

[seapro220]

Another question, similarly related

If I have a current job running - which is a weekly with daily incrementals - and am going to replace it with this job, what's the best method of removing all the data associated with this current (and soon to be old job). I believe the answer is to continue to run this job, until the 'new' job has completed and then at that time (or whenever i want to free up the space) I can disable the current (soon to be old) job and just remove the files from the 'disk'. This seems the safest doesn't it?

[veremin]

Yep. If you don’t need these backup files any longer, you can follow the steps you’ve mentioned.

Another possible option is to make necessary changes to the existing job, such as schedule, etc., and specify 4 as a number of restore points you want to keep on disk. In this case retention policy will do the work for you.

Hope this helps.
Thanks.

[seapro220]

Ok - I'll make some changes and I appreciate the helpful suggestions. Here's a question - If I change my 'job name', like from "sql daily backups" to "sqlweekly" will this confuse Veeam? I know, now - that Veeam creates the directories based on the 'job name' but didn't know if it was going to be 'smart enough' to realilze the change - of if I'd have to create the directory manually, move the files into the new directory-location, and remap the job ... or not.

[Vitaliy S.]

If I change my 'job name', like from "sql daily backups" to "sqlweekly" will this confuse Veeam?

No, it shouldn't have any effect on your existing jobs. If you decide to rename the jobs, the target folders on the repository will keep the previous job name.

[seapro220]

Hi.
I've reconfigured the job, and all seems to be working w/o any issues. I didn't change the job 'location', but will probably do that next week after I have a couple of backups 'under my belt'.

thanks ....

[seapro220]

A quick question to confirm exactly 'what' is needed for quarter backups.
if i take my latest "full" xx.vbk file and put it 'away/off-site' as the specific quarter backup file - Do I only need the xx.vbk file, and not any other files? I believe this is correct as the last full.vbk file has all of my 'roll-ups' into it - but wanted to be sure. I am doing 'forward incrementals' as my normal process of backups.

thanks,

[Vitaliy S.]

Hi Mark, yes, latest full backup (VBK) is all you need to run the restore process if the disaster strikes.