Does Veeam change SQL LSN number?

[tom11011]

Currently, we use Veeam to do backups and replications of MSSQL database servers. The backup job is set for Reverse Incremental. These jobs run about every hour and there is no issue here.

But we also have a need to do SQL dumps to a BAK file. This is done to move data offsite for archive purposes. The way it has been done is to have the SQL server dump a BAK file to a virtual file server. That virtual file server is backed up with Veeam and then a copy job runs to move this file server backup from location A to B. Each day, this dump is Full.

But we started to run out of space at location B because each job is a full. When we changed the job to One Full and 6 differentials, that solved the space issue but we are unable to restore any of the differentials, Microsoft complains that the LSN number isn't correct. *I think* the LSN number gets updated each hour the veeam backup job runs rendering our dumps unusable.

Looking to confirm this functionality is the cause and also, looking for maybe a better way to do sql backups with veeam. The requirement is to get the data offsite away from production.

Looks like this right now-
1.) Backups run each hour into a reverse incremental, we always have a current full backup. We over write at 48 hours. Data stays onsite at location A.
2.) Replications run each hour. Servers replicated to Location B.
3.) Dumps as described above for archival purposes. Veeam manages the archive at location B, 12 monthlys and 3 annuals.

[Natalia Lupacheva]

Hi Tom,

Just to clarify - were you able to restore from incrementals before the changes in your retention policy?
We need to realize if the problem is in the backups themselves of in the changed retention policy.

Also, do you back up SLQ server with or without logs?
Do all restore procedures from incrementals fail for now?

Thanks!

[tom11011]

I don't really know, we have only ever done full MSSQL dumps to bak file, those don't have a problem restoring. Now that we are trying 1 full plus 6 differentials, the restore of the bak diff fails.

The veeam jobs are set to process transaction logs.

[Gostev]

You can't backup SQL Server transaction logs with more than one solution. Only one backup product must manage the logs.

[Mildur]

Try this option. Copy Only Backups.
This should help you to use veeam for "Image Backups" and another tool or built-in functionality for "SQL Log Backups".

https://helpcenter.veeam.com/docs/backu ... ml?ver=110

The Perform copy only option indicates that a chain of database backups is created with native Microsoft SQL Server means or by a 3rd party tool, and instructs Veeam to preserve this chain (backup history). Veeam Backup & Replication backs up the Microsoft SQL Server VM using the VSS_BS_COPY method for snapshot creation. The VSS_BT_COPY method produces a copy-only backup — the backup that is independent of the existing chain of database backups and does not contain transaction logs data. As a result, the copy-only backup does not change the log sequence number and transaction log backup time.

[Natalia Lupacheva]

Just to add,

I don't really know, we have only ever done full MSSQL dumps to bak file, those don't have a problem restoring. Now that we are trying 1 full plus 6 differentials, the restore of the bak diff fails.

If any backup happened between this full and incrementals - LSN will be changed.

But the main point - you need to analyze your approach to SQL backups.

Also, please make sure you've configured your replicas with "copy only" mode (which is set by default).

Thanks!

[tom11011]

Thank you everyone.

Natalia, since you mentioned it, I hate to say it but I think we might be coming to the end of using veeam for sql backups. One other issue I notice is that as more and more db's get added to a sql server, the application aware beginning process is brutal on the databases, what started off as a 1 or 2 ms i/o freeze is turned into 10 to 15 the more db's we add.

Sure would like to hear how large database organizations and admins are handling their enterprise RPO and RTO?

[Mildur]

If you use Log Backup with Veeam, you don‘t have todo vm snapshots. No freeze of the database with this function.

Overview
https://helpcenter.veeam.com/docs/backu ... ml?ver=110

Backup Job configuration
https://helpcenter.veeam.com/docs/backu ... ml?ver=110

[Gostev]

Right, with transaction log backup we merely call native SQL Server log backup API, then pick up the native log backup SQL Server creates and ship it into our repository. So 100% SQL-native functionality, with no snapshots or freezes involved whatsoever (except whatever SQL Server itself needs to do internally to produce its native log backup).