SQL Server full and transaction log backups completing but differential backups failing because of Veeam B&R configuration

Scenario

I recently refreshed our SQL Server Maintenance Plans, but System Centre Essentials has been intermittently reporting failures of the daily differential backups, with an error like this:

Executing the query “BACKUP DATABASE [DBNAME] TO DISK = N’…” failed with the following error: “Cannot perform a differential backup for database “DBNAME”, because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. BACKUP DATABASE is terminating abnormally.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

The strange thing is that the logs show all Weekly Full backups have completed without error. If I run a manual Full Backup, the next Differential Backup will complete, then the following day will fail.

Solution

First, I used the following query to show more details on the backups (make sure you select the DB in SSMS first):

-- For single db back up history I use

DECLARE @db_name VARCHAR(100)
SELECT @db_name = DB_NAME()

-- Get Backup History for required database

SELECT TOP ( 30 )
s.server_name,
s.database_name,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
s.user_name,
s.backup_start_date,
m.physical_device_name,
cast(CAST(s.backup_size / 1000000 AS INT) as varchar(14))
+ ' ' + 'MB' as bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'Seconds' TimeTaken,
CAST(s.first_lsn AS varchar(50)) AS first_lsn,
CAST(s.last_lsn AS varchar(50)) AS last_lsn,
s.recovery_model
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date desc,
backup_finish_date

Then I used this query to show the last date/time per backup type for each DB:

USE [Master];
GO

DECLARE @FileSpace TABLE (
[database_id] INT,
[db_name] NVARCHAR(100),
[file_id] INT,
[file_name] NVARCHAR(100),
[space_used] INT
);
INSERT INTO @FileSpace  EXEC sp_MSforeachdb 'USE [?] SELECT db_id(''?''),''?'' AS DBname,fileid, name ,FILEPROPERTY(name, ''SpaceUsed'')  AS spaceused FROM sys.sysfiles';

SELECT
S.name AS [DatabaseName],
S.recovery_model_desc AS [RecoveryModel],
S.create_date AS [DatabaseCreatedDate],
S.collation_name AS [DatabaseCollation],
F.[Last_Full_Backup_Date] AS [LastFullBackupDate],
F.backup_size AS [FullBackupSize],
L.Last_Log_Backup_Date AS [LastLogBackupDate],
L.backup_size AS [LogBackupSize],
I.[Last_Diff_Backup_Date] AS [LastDiffBackupDate],
I.backup_size AS [DiffBackupSize],
S.state_desc [DatabaseState],
CURRENT_TIMESTAMP AS [ReportDate]
FROM
SYS.DATABASES S
OUTER APPLY
(SELECT TOP  1 database_name, backup_finish_date AS [Last_Full_Backup_Date] , backup_size  FROM msdb.dbo.backupset
WHERE TYPE='D' and database_name =S.name  ORDER BY backup_finish_date DESC ) F

OUTER APPLY
(SELECT TOP  1 database_name, backup_finish_date AS [Last_Log_Backup_Date] , backup_size  FROM msdb.dbo.backupset
WHERE TYPE='L' and database_name =S.name  ORDER BY backup_finish_date DESC ) L
OUTER APPLY
(SELECT TOP  1 database_name, backup_finish_date AS [Last_Diff_Backup_Date] , backup_size  FROM msdb.dbo.backupset
WHERE TYPE='I' and database_name =S.name  ORDER BY backup_finish_date DESC ) I
ORDER BY
S.name;

Using the above queries, I found out Veeam B&R was breaking the backup chain.

To resolve the issue I disabled application processing for the SQL Server. I had previously disabled the truncating of logs, so I don’t think this had any relevance:

When you disable application processing, you should make sure you have enabled VMware Tools quiescence:

These changes will ensure COPY_ONLY backups are used for your SQL Server.

Reference