Comprehensive data protection for all workloads
Post Reply
IT_Vision
Influencer
Posts: 23
Liked: 1 time
Joined: Jun 24, 2013 6:49 pm
Contact:

SQL Server - Backup Every 2 hrs

Post by IT_Vision »

Greetings,

I am trying to figure out the best way to configure a backup job for a SQL Server. The SQL server hosts only a handful of databases, however I would like to be able to recover the entire VM in the event of a disaster. We are looking to utilize VEEAM completely and not rely on SQL Manager to do any of the backup processes. We are wanting to perform a backup every 2hrs and be able to perform an instant recovery from that 2hr window if need be. What is the best way to create this backup job to run every two hours, everyday?

Do I setup the job to perform incremental with synthetic fulls everyday at 2hr intervals? Even with that how would the restore points to keep on disk be affected?

Any assistance is configuring this job would be appreciated.

Cheers
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Server - Backup Every 2 hrs

Post by Vitaliy S. » 1 person likes this post

Hello,

How many restore points do you want to keep on the disk? If you need to run backups every two hours, then it doesn't matter what backup mode you choose (forward or reversed), target storage is what really matters here.

Given that you're going to backup SQL Server, you should enable application-aware image processing and let it do the job (create application-consistent backups).

Thank you!
Gostev
Chief Product Officer
Posts: 31455
Liked: 6646 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: SQL Server - Backup Every 2 hrs

Post by Gostev » 1 person likes this post

For this use case, I would use reversed incremental backup mode.

This one is slower and heavier on backup repository I/O, but:
1. With 2 hours cycles, the amount of changed data to process will not be too large for this to be an issue.
2. Unlike with other backup modes, the time each backup cycle takes will be consistent with this mode, which guarantees you meeting the RPO.
3. Minimal possible backup repository disk space usage due to no periodic fulls.

Thanks!
IT_Vision
Influencer
Posts: 23
Liked: 1 time
Joined: Jun 24, 2013 6:49 pm
Contact:

Re: SQL Server - Backup Every 2 hrs

Post by IT_Vision »

Thank you Vitaliy and Gostev for the quick responses.

I am looking at keeping 12 restore points, however depending on available storage that may change. Since I have not ran the job yet, I am not sure what the backup file sizes will look like once they run.

So if the backup job runs every 2hrs, and I have "Enable Synthetic Fulls" check, would it be necessary to have the days options set to m/t/w/th/f/sat/sun so I could perform a full restore within that two hr window?

Cheers.
Gostev
Chief Product Officer
Posts: 31455
Liked: 6646 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: SQL Server - Backup Every 2 hrs

Post by Gostev »

Reversed incremental backup does not have this option.
IT_Vision
Influencer
Posts: 23
Liked: 1 time
Joined: Jun 24, 2013 6:49 pm
Contact:

Re: SQL Server - Backup Every 2 hrs

Post by IT_Vision »

Hello,

looking at the Reversed incremental backup details/information, that looks like a great option for my situation. I will proceed with this option and see how it goes.

Thank you both for the quick response and support.
Post Reply

Who is online

Users browsing this forum: bct44, Ivan239, Mircea Dragomir, nikita.kozlenko, tomo yasu and 166 guests