Sql-server – Trouble with differential backup – full backup not being recognized

ola-hallengrensql serversql-server-2016

I have recently implemented backups using https://ola.hallengren.com/.

Backups

EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES, -%Archive', @Directory = N'H:\sqlbackup', @BackupType = 'FULL',@Verify = Y', @CleanupTime = 360, @CheckSum = 'Y', @LogToTable = 'Y'

EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES, -%Archive', @Directory = N'H:\sqlbackup',@BackupType = 'DIFF',@Verify = 'Y',@CleanupTime = 192,@CheckSum = 'Y',@LogToTable = 'Y'

EXECUTE [dbo].[DatabaseBackup]@Databases = 'USER_DATABASES',@Directory = N'H:\sqlbackup',@BackupType = 'LOG', @Verify = 'Y',@CleanupTime = 48,@CheckSum = 'Y',@LogToTable = 'Y'

Schedule:

  • Full – once weekly
  • Diff – daily
  • log – hourly

Servers:

  • 2 X sql server 2016, compatibility level set to (sql server 2012)
  • 1 X sql server 2016, compatibility level set to (sql server 2016)

PROBLEM:

Suddenly, differential started failing on 2 servers.

Error is :
Cannot perform a differential backup for database "aspnetdb", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.

However, the DB is registering the last full backup. This is a full backup I manually took after last failure like this. Recovery mode is "simple"
enter image description here

This occurs on 1 server with compatibility 2012, and on 1 with compatibility 2016

on 1 server, differential does not fail but size = to full backup

That same time the job starts failing on the other 2 server, on this server the job completes but the file size suddenly jumps to be the same as full backup.

Clues

  • when I kick off the full backup job manually, then differential can be run manually. Then it runs OK next night, then next day fails in the way to describe
  • I am not excluding simple-recovery model DBs from Log backup explicitly
  • I have changed some DBs to simple recovery model since backup was setup, but DB server was not restarted
  • I modified the olg script abit, to have different clean up time per backup type
  • log backups are working OK on the servers where diff fails
  • log backups are showing as completed on the server where the diff completes but file is large, but the log backup files are not on disk – silent failure. The last full backup is just before the 1st differential backup that generates the large file

Examining backup history for 1 DB:
the "is copy" & "is_snapshot" backups are triggered by azure backup services of the actual VM where the sql server runs

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
msdb.dbo.backupset.is_copy_only, 
msdb.dbo.backupset.is_snapshot, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date 

enter image description here

ANY CLUES FOR ME ?

Best Answer

You probably have a full backup before the ones that fails, like a snapshot backup or a VDI backup). Check the backup history tables in msdb and the errorlog file for clues.