Host-based backup of VMware vSphere VMs.
Post Reply
backupquestions
Expert
Posts: 186
Liked: 21 times
Joined: Mar 13, 2019 2:30 pm
Full Name: Alabaster McJenkins
Contact:

Clarification on SQL backup workflow

Post by 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
Veeam Software
Posts: 3229
Liked: 520 times
Joined: Aug 28, 2013 8:23 am
Full Name: Petr Makarov
Location: Prague, Czech Republic
Contact:

Re: Clarification on SQL backup workflow

Post by 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
Expert
Posts: 186
Liked: 21 times
Joined: Mar 13, 2019 2:30 pm
Full Name: Alabaster McJenkins
Contact:

Re: Clarification on SQL backup workflow

Post by 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
Veteran
Posts: 3077
Liked: 453 times
Joined: Aug 07, 2018 3:11 pm
Full Name: Fedor Maslov
Contact:

Re: Clarification on SQL backup workflow

Post by 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
Veeam Software
Posts: 3229
Liked: 520 times
Joined: Aug 28, 2013 8:23 am
Full Name: Petr Makarov
Location: Prague, Czech Republic
Contact:

Re: Clarification on SQL backup workflow

Post by 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
Expert
Posts: 186
Liked: 21 times
Joined: Mar 13, 2019 2:30 pm
Full Name: Alabaster McJenkins
Contact:

Re: Clarification on SQL backup workflow

Post by 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
Veteran
Posts: 3077
Liked: 453 times
Joined: Aug 07, 2018 3:11 pm
Full Name: Fedor Maslov
Contact:

Re: Clarification on SQL backup workflow

Post by wishr » 1 person likes this post

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
Expert
Posts: 186
Liked: 21 times
Joined: Mar 13, 2019 2:30 pm
Full Name: Alabaster McJenkins
Contact:

Re: Clarification on SQL backup workflow

Post by 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
Veeam Software
Posts: 3229
Liked: 520 times
Joined: Aug 28, 2013 8:23 am
Full Name: Petr Makarov
Location: Prague, Czech Republic
Contact:

Re: Clarification on SQL backup workflow

Post by PetrM »

T-log backup job shows a corresponding message as well so that you're aware that the chain is corrupted. Thanks!
wishr
Veteran
Posts: 3077
Liked: 453 times
Joined: Aug 07, 2018 3:11 pm
Full Name: Fedor Maslov
Contact:

Re: Clarification on SQL backup workflow

Post by wishr »

backupquestions wrote: Aug 19, 2019 5:07 pm 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
Veeam Software
Posts: 3229
Liked: 520 times
Joined: Aug 28, 2013 8:23 am
Full Name: Petr Makarov
Location: Prague, Czech Republic
Contact:

Re: Clarification on SQL backup workflow

Post by PetrM » 1 person likes this post

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
Expert
Posts: 186
Liked: 21 times
Joined: Mar 13, 2019 2:30 pm
Full Name: Alabaster McJenkins
Contact:

Re: Clarification on SQL backup workflow

Post by 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.
Post Reply

Who is online

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