I have recently implemented backups using https://ola.hallengren.com/.
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'
- Full - once weekly
- Diff - daily
- log - hourly
- 2 X sql server 2016, compatibility level set to (sql server 2012)
- 1 X sql server 2016, compatibility level set to (sql server 2016)
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.
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.
- 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 ?