SQL Backups through Veeam *AND* SQL

[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]

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]

My short answer would be: no!

The longer one is: that depends!
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]

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]

Very helpful information, as always!

Thank you very much.