0

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 ?

  • I modified the olg script abit, to have different clean up time per backup type, Are you cleaning full backup too? – McNets May 29 '20 at 14:43
  • @McNets yes, cleaning up all of them. keeping full for 15 days (so 2 of them), diff for 8 days, log for 48h – Greg Balajewicz May 29 '20 at 14:53
1

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.

  • when I examine the history, for one DB, I get NULL for backup type in some rows... do not know how to interpret this... adding screen shot to the question – Greg Balajewicz May 29 '20 at 15:22
  • 1
    I don't know what query you ran to examine the backup history, but there is columns in the backupset table which are interesting. I'm thinking about is_snapshot and is_copy_only. Also, the ones with a GUID for physical device type suggests you use some 3:rd party tool to backup your SQL server and my gurss is that this is what causes your problems. And, again, check the errorlog file and correlate the messages in there with te backup history for your problematic occasions - might give you some more insights. – Tibor Karaszi Jun 1 '20 at 9:13
  • thanks Tibor, I added the columns and your comment gave me clues - those backups are triggered by azure backup services. The VM on which the sql server runs, is backed up using azure backup services - the backup is of the entire VM. – Greg Balajewicz Jun 3 '20 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.