strange MSSQL transactionlog backup behaviour

[sandsturm]

Hi

We're currently planning to migrate all our MSSQL Servers from our legacy backup solution to Veeam. One strange thing we're facing now is the following:

Veeam starts a transactionlog backup, just a moment before the first Veeam fullbackup for an MSSQL server will be executed. The log of the MSSQL server shows that this transactionlog backup has NULL as backup destination. What is the reason, that Veeam executes this transactionlog backup before the first fullbackup? If this transaction logs are sent to dev0, we will loose the possibility to restore data between the last transactionlog backup of the legacy backup solution and the first fullbackup with veeam. Is there a reason behind this special behavior?

thx,
sandsturm

[HannesK]

Hello,
I cannot follow your observations. Veeam starts transaction log backup after the first image backup. The image backup is always a database full backup, although it might be an incremental VM backup.

We don't send anything to /dev/null

Best regards,
Hannes

[sandsturm]

Hi

I cannot understand why a transaction log backup is started just before the fullbackup will be executed. Have a look at the following printscreen: https://ibb.co/nnQwskF, the yellow marked line (backup_set_id=933434) is the one with transactionlog backup executed by veeam. The transactionlogs before this one are from the legacy backup solution. If you have a look at the following table: https://ibb.co/QXGv9vN, you see the task with the backup-set_id 933434 has NUL as physical device location. Meaning that Veeam executes a transactionlog backup just before the fullbackup and sends it to NUL.... Therefore we lose the data between the last successful transactionlogbackup from the legacy backup system and the first fullbackup on Veeam due to this strange transactionlogbackup.

thx
sandsturm

[HannesK]

Hello,
hmm, I looked in my backupset and backupfile table but could not find any NUL value. Which table to you show in your screenshots?

Best regards,
Hannes

[sandsturm]

In the screenshot are shown the tables msdb.dbo.backupset and msdb.dbo.backupmediafamily, here is the query I used:

Code: Select all

select
      s.backup_set_id,
      s.name,
      s.description,
      s.type,     
      f.physical_device_name,
      s.backup_start_date,
      s.backup_finish_date,
      s.user_name,
      s.is_copy_only,
      s.first_lsn,
      s.last_lsn
from
      msdb.dbo.backupset s
inner join 
      msdb.dbo.backupmediafamily f on s.media_set_id = f.media_set_id 
where
      database_name = 'dbname' 
      and backup_finish_date between 'yyyy-mm-dd hh:mm.000' and 'yyyy-mm-dd hh:mm.000'
order by
      backup_finish_date desc

modify 'dbname' and start and enddate to the run the query in your enviroment
thx,
sandsturm

[HannesK]

Hello,
thanks, that script made it easier.

When I run that on a SQL server that has never seen other backup agents, then I could find two results with NUL for f.physical_device_name from some weeks ago. I guess that was at a time when I changed something in my lab. Every Veeam backup (I run logbackup every 5min) has as name "Veeam Backup & Replication" in the name column.

What makes you believe, that Veeam is responsible for the lines with NULL values in the name column? I'm asking because reproducing something with a different backup software is a huge effort and I'm not sure whether it helps in the end.

Best regards,
Hannes

[sandsturm]

Hi

I made another printscreen where you can see the account used for the jobs: https://ibb.co/8cVfbh7. You see that this strange tlog backup was executed with the VeeamBackupAccount. We don't use this account for anything else then veeam, therefore this tlog backup is definitively executed from veeam....

thx
sandsturm

[HannesK]

Hi,
hmm okay. To continue investigations, could you please create a support case and post the case number here? You can tell support that I will follow up with them.

Thanks,
Hannes

[sandsturm]

Hi Hannes

Case #03598715 was created

thanks for your help!

[ChrisGundry]

Was there any outcome on this please? Thanks

[sandsturm]

Hi

the answers of Veeam support was not really satisfying unfortunately:

Full/Bulk-logged Databases logs are only truncated when they're backed up, so they're truncated on every log backup interval.

However, there're 2 exclusions to that rule:

1. Veeam B&R always truncate logs before backing them up on the very first run of the log backup. This's done because we don't need the information in the old logs, as we cannot restore it. So discussed Database string basically means initial truncation. There's no way to avoid that behavior;

2. Another way to trigger truncation not on a log backup interval is to exclude VM from the backup, because when the VM gets included back, we supposes that this will be a new log backup chain. This's exactly why logs got truncated on 05.06 at 8:30 AM, as the VM was excluded from 1 backup run that occurred at 7 AM. There's no way to avoid that behavior too.

This means, that you'll have a gap of log backup data during the migration of the sql backup process from any solution to Veeam. If anyone wants to restore data from within this timeframe, it's not possible.

[ChrisGundry]

Ok, thank you for the reply!