Clarification on SQL backup workflow

[backupquestions]

From what I have read in the documentation, if I configure veeam to truncate SQL logs, then each time a backup runs on this VM the sql database backup will be viewed and treated as a FULL backup.

So, because of that I would expect that regardless of say a DBA taking their own SQL full and incremental chains with native tools, it would not cause an issue with Veeam's backup. Is this correct?

I do understand that the DBA's own full/incremental SQL native chains would be broken each time Veeam takes a backup, but since Veeam is not doing incremental SQL but FULL each time, it's SQL backups should always be intact regardless and allow me to restore any DB back to the point of time when the last Veeam backup was taken... Right?

Or do I really have to try to ensure that anyone else using native backups uses "copy only"? The company does not care about the DBA one off backups, they only care that Veeam's are valid and can be restored.

Our previous backup software took incremental sql database backups and it would fail and warn you that someone disrupted the chain, and Veeam does not warn you so I don't have a good way to know if people are taking their own native backups on all the SQL servers, but again I believe that is because from the view of SQL each Veeam truncation/backup is a FULL sql backup so it doesn't hurt Veeam. Is that wrong?

Thanks

[PetrM]

Hi Alabaster! The only requirement is to use copy only in Veeam job settings if you use native Microsoft SQL Server tools or 3rd party backup tools. I wouldn't expect any impact for Veeam chain as long as copy only option is enabled in Veeam job settings even if 3rd party tools didn't use the same option. Thanks!

[backupquestions]

Could you clarify a bit more?

My understanding was that the "copy only" is only so that Veeam won't hurt the native sql backup chains.

But let's say I don't care if those break. I only care that Veeam can reliably restore.

Now since Veeam is not doing any incremental sql, only full sql that there is no chain to be broken. Each SQL truncation is a full SQL backup from the perspective of SQL server.

So I would expect even with "truncate the logs" option I am still able to restore at any point regardless of what happens by native tools. The only downside is the native sql tools backups may be unusable but I don't care as i have told the DBAs that only Veeam backups will be worried about. :p

So am I wrong and I still need copy only? Then the logs would never truncate and I don't want that. The thing is they don't do native backups on a regular basis or even for all databases. Just random times that they feel like. So if I have veeam do copy only on all databases then I'll never get the logs cut down...

[wishr]

Hi Alabaster,

In your particular scenario, if you do not care about native SQL backups and would like to transition to Veeam SQL T-log backup, you should use the "Backup logs periodically" option. Your logs will be backed up and truncated, meeting the aforementioned requirements. You'll require to disable/reconfigure your native SQL backups to prevent LSN discrepancy.

"Copy only" is only suitable when you use some 3rd-party tools (non-Veeam tools) to perform SQL T-log backups, while the "Truncate logs" option does not imply T-log backup, but truncation.

You can find more details on these topics here.

Thanks

[PetrM]

Hi Alabaster! I would say the same rule works in the opposite direction as well. If you do backup/truncate of sql logs by 3rd party tool: lsn will be updated and our job will consider it as an invalid lsn during its next run. As result, chain is corrupted and you won't have point-in-time restore between 2 points which correspond to image-level backup sessions. I wouldn't expect any impact if 3rd party tools work in copy only mode. Please let me know if you have some questions or if you would like to clarify something. Thanks!

[backupquestions]

So it sounds like the only option to ensure viable restore if 3rd parties may take their own occasional backup is to use the "backup the logs every x min" option?

I don't need point in time restore between veeam backups. Just restore a sql database as of yesterday once a day restore point etc..

So would I just do something like backup every 24 hours in the SQL log area? Try to align it with the time the veeam job runs?

[wishr]

No, that's not correct in most cases and will only work if you perform a copy-only backup using your 3-rd party tool due to LSN dependencies, as Petr mentioned above.

Ideally, you'd like to use a single tool for handling t-log backups to avoid issues. If the use of two solutions is required, then you should configure copy-only mode in at least one of them.

You may want to take a look at that article and share it with your DB team.

Hope it helps.

[backupquestions]

Could there be a feature request to notify during image level backup that someone has taken a sql backup without using the copy logs option? Meaning if a Dba used native tools to do so..

Our previous backup software would take sql backups full and incremental and then it would warn you in the event something hurt the chain.

We have so many systems and different Dba that I can't do a lot besides email them all to not use sql backups etc.

[PetrM]

T-log backup job shows a corresponding message as well so that you're aware that the chain is corrupted. Thanks!

[wishr]

We have so many systems and different Dba that I can't do a lot besides email them all to not use sql backups etc.

In case of native SQL backup tools usage, I'd suggest using the "copy only" option in the job settings and leave the t-log backups on the DBA shoulders. Even in this case, It's recommended to have some universal SQL t-log backup approach developed, including log truncation, though.

Regards,
Fedor

[PetrM]

By the way, if there was no need to backup transaction logs and you're going to use just "truncate logs" option at the level of backup job settings to prevent logs from growing forever there wouldn't be impact for Veeam chain even if 3rd party tools don't use copy only mode. However, we recommend to process logs by single tool and all other tools should work in copy only mode to avoid any kind of discrepancy. Thanks!

[backupquestions]

PetrM,

That is what I initially thought and was looking for this whole thread. All I care about is that I can restore a sql database to the point of the image level veeam backup. So it is great to hear that there should be no impact even with third party tools not using copy only. I tell them all to use copy only, but people slip up sometimes and do it so this is great.