Veeam or other backup system stomping on transaction logs

What this check looks for

Backups in the last 72 hours whose physical_device_name is either the GUID style name a VSS writer produces, matching the pattern {________-____-____-____-____________}, or NUL, and which were not taken with COPY_ONLY.

Those two device shapes are how a virtual machine or snapshot backup product appears in msdb. The product is usually Veeam, which is why the check is named after it, but Commvault, Rubrik, Veritas, Azure Backup and the built in Windows Server Backup all do the same thing.

Why it matters

Something outside SQL Server took a full backup of your database and did not tell your backup strategy about it. Two things break at once.

The differential base moves. A differential backup contains everything changed since the last full backup. Not since your last full backup: since any full backup. When the VM backup takes one at 11pm, your 2am differential is now a differential against the VM backup, and restoring your own full plus your own differential produces an error, not a database. The chain you tested is not the chain you have.

The log chain breaks. This is the more serious one. Depending on how the tool is configured, it may truncate the log as part of taking its backup. Every log backup you have taken since your last full is then unrestorable, because the records they would need have been released. Point in time recovery to any moment after the stomp is gone.

And nothing reports it. Your backup jobs succeed. Your monitoring says backups are green. The VM backup product says it succeeded too, and from its point of view it did. The two systems each believe they are protecting the database and neither knows about the other. The discovery moment is a restore.

There is a legitimate version of this arrangement, and the difference is one setting: COPY_ONLY. A copy only backup reads the database without resetting the differential base and without truncating the log. Every serious VSS product supports it, and it is usually not the default.

How to confirm it yourself

SELECT bs.[database_name],
       CASE bs.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential'
                      WHEN 'L' THEN 'Log' ELSE bs.[type] END AS [type],
       bs.[is_copy_only],
       bs.[backup_start_date],
       bs.[backup_finish_date],
       bmf.[physical_device_name]
  FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
 INNER JOIN msdb.dbo.backupmediafamily AS bmf WITH (NOLOCK)
         ON bmf.[media_set_id] = bs.[media_set_id]
 WHERE bs.[backup_start_date] > DATEADD(HOUR, -72, GETDATE())
   AND (bmf.[physical_device_name] LIKE '{________-____-____-____-____________}%'
        OR bmf.[physical_device_name] = 'NUL')
 ORDER BY bs.[backup_start_date] DESC;

is_copy_only = 0 on one of these rows is the finding. A 1 means the tool is configured correctly and there is nothing to do.

To see the interleaving, which is what makes it obvious:

SELECT bs.[database_name],
       bs.[backup_start_date],
       CASE bs.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Diff' WHEN 'L' THEN 'Log' END AS [type],
       bs.[is_copy_only],
       CASE WHEN bmf.[physical_device_name] LIKE '{%' THEN 'VSS tool'
            ELSE 'your backup job' END AS [who],
       bmf.[physical_device_name]
  FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
 INNER JOIN msdb.dbo.backupmediafamily AS bmf WITH (NOLOCK)
         ON bmf.[media_set_id] = bs.[media_set_id]
 WHERE bs.[database_name] = N'YourDatabase'
   AND bs.[backup_start_date] > DATEADD(DAY, -7, GETDATE())
 ORDER BY bs.[backup_start_date];

Reading that list top to bottom shows exactly where your chain was interrupted.

How to fix it

Set the backup product to use copy only. That is the entire fix, and it is one checkbox.

• Veeam: in the job’s Guest Processing settings, under SQL Server transaction log handling, choose to leave the logs alone or select copy only. The relevant option is “Perform copy only” in the application aware processing settings.
• Other products: look for “copy only”, “do not truncate logs”, or “application aware processing” in the SQL Server options.

Then decide which system owns SQL Server backups, because the failure underneath this is organizational rather than technical. Two teams are each backing up the same database and neither knows. Pick one:

1. SQL Server owns backups. The VM tool runs copy only, purely for machine level recovery. This is the usual right answer, because native backups restore individual databases to a point in time and VM snapshots generally do not.
2. The backup product owns backups. Then it needs to handle log backups too, and your Agent backup jobs should be turned off rather than left running and ignored.

What does not work is both, which is the state this finding reports.

Then repair the chain. Take a fresh full backup, immediately, so there is a known base:

BACKUP DATABASE [YourDatabase]
  TO DISK = N'\\backupserver\sqlbackups\YourDatabase_full.bak'
  WITH CHECKSUM, COMPRESSION, INIT;
BACKUP LOG [YourDatabase]
  TO DISK = N'\\backupserver\sqlbackups\YourDatabase_log.trn' WITH CHECKSUM, COMPRESSION;

And then test a restore. This is the finding where a restore test is worth the most, because the thing that is broken is specifically the ability to restore, and nothing else reveals it.

How long it takes

About two hours, most of it agreeing which system owns backups. The setting change takes minutes.

Related reports

Related checks

Frequently asked questions

Veeam says the backup succeeded. Is this a false positive? No, and both statements are true. The tool backed the machine up successfully. It also reset your differential base and possibly truncated your log while doing it.

Is COPY_ONLY less safe? No. It reads the same data. It simply does not participate in the differential and log chain, which is exactly what you want from a second system.

We only use the VM backup, no native backups. Then this finding is less urgent, and the question becomes whether you can restore a single database to a point in time. For most VM level products the answer is no.

Why 72 hours? It keeps the finding about the chain you would actually restore from. The queries above look back as far as you like.