Comprehensive data protection for all workloads
Post Reply
SteeleTek
Service Provider
Posts: 19
Liked: 3 times
Joined: Jan 19, 2016 4:51 pm
Contact:

SQL Backups through Veeam *AND* SQL

Post by SteeleTek »

Hello,

We do Veeam backups and replication of our primary SQL database server. We leverage the Windows VSS feature for guest OS quiescence and have log truncating enabled.

In addition to this, we also have a "SQL Maintenance Plan" within SQL Management Studio which also backs up each SQL database/logs to .BAK files locally. Do you all feel these are necessary? Do you still do backups through SQL itself in addition to your Veeam backups?

Thanks
alanbolte
Veteran
Posts: 635
Liked: 174 times
Joined: Jun 18, 2012 8:58 pm
Full Name: Alan Bolte
Contact:

Re: SQL Backups through Veeam *AND* SQL

Post by alanbolte »

I can't comment on whether or not there's any use to it, but two separate backup methods requires that one of them is set to 'copy only'. Truncating logs by two separate methods is counterproductive.
jmeisel
Influencer
Posts: 11
Liked: 2 times
Joined: Mar 05, 2015 1:06 pm
Contact:

Re: SQL Backups through Veeam *AND* SQL

Post by jmeisel »

My short answer would be: no!

The longer one is: that depends! :D
It all depends on what recovery model you are using. If you are using "simple" then you can do both backups (Veeam and Backups running in SQL Maintenance-Plans). If your recovery model is full, then you should only use one method.
Otherwise you will break your backup-chain, because transaction-logs depend on the last full backup.

If you are using Veeam B&R the right way, there is no need for SQL / Maintenance Plan Backups. Thanks to Veeam SQL-Explorer the Restore Capabilities are much easier!
alexander sh
Veeam Software
Posts: 41
Liked: 28 times
Joined: Dec 13, 2012 8:52 am
Full Name: Alexander Shelemin
Location: Prague
Contact:

Re: SQL Backups through Veeam *AND* SQL

Post by alexander sh » 2 people like this post

Hi guys!
Pragmatically speaking, you should be fine with getting rid of Maintenance plans, unless there's something very specific you need that Veeam doesn't provide.
However, this area seems to be a common source of some confusion and gets raised on the forums pretty often, so here's my shot at making things a bit clearer.

1) Transaction logs chain does not in fact depend on the last full backup (see for example here: https://blogs.technet.microsoft.com/bea ... gs-backup/). It can be replayed back to any full backup, provided the log chain is not broken. So keeping log backup chain intact is the main priority.
What it means in terms of concurrent usage of Veeam and SQL Maintenance plans: if you do transaction log backups, you can only interact with transaction logs from one product. The way you describe your setup today, each Veeam job run actually truncates the log and thus breaks the log chain created by your SQL Maintenance plan, which is likely to lead to issues with point in time restores. So if you want to back up logs using Maintenance plans, you should set Veeam job to do Copy Only processing, preventing Veeam from doing log truncation.
Alternatively, just use Veeam to do transaction log backups and Veeam SQL Explorer for point-in-time restores and get rid of Maintenance plans altogether.

2) For the sake of completeness, I want to mention that if you only do Full database backups and don't back up the logs, but want to truncate them so they don't grow over time, it's ok to use both Veeam and Maintenance plans at the same time.

3) One last thing that could trip you off is Differential database backups in your Maintenance plans. Be aware that Differential chains do depend on corresponding full database backups, so if you use those, make sure to set Veeam job to Copy only.
SteeleTek
Service Provider
Posts: 19
Liked: 3 times
Joined: Jan 19, 2016 4:51 pm
Contact:

Re: SQL Backups through Veeam *AND* SQL

Post by SteeleTek »

Very helpful information, as always!

Thank you very much.
Post Reply

Who is online

Users browsing this forum: Google [Bot] and 50 guests