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"
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
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.