Using Ola Hallengren's succeeds to take full backups but fails to take a differential

Question

I take weekly Full backups (all my databases are in Full recovery model) on Sundays and then schedule the Diffs to run nightly (except on Sundays).

Looking through my logs I see this:

Cannot perform a differential backup for database "DBNAME", because a current database backup does not exist

I do have the VSS Writer running but have no 3rd party tool that I know of running that should "invalidate" the Sunday full backups. If I manually run a Full backup then run a Diff it works just fine. These machines are VM's in Azure running SQL Server 2017.

The strange thing is on my test machine it works with the same setting (VSS Writer enabled).

I'd rather not disable the VSS Writer if do not have to.

Any thoughts?

Answer 1

That error message is thrown when you try to execute a differential backup on a database before a full backup was taken. I tested it creating a new database named MyLab and issuing the following command:

BACKUP DATABASE MyLab 
TO DISK = 'C:\BackupSQL\testbackupdiff.dif' 
WITH DIFFERENTIAL;

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

Taking a full backup before a differential is a prerequisite. The Create a Differential Database Backup doc says:

Prerequisites

• Creating a differential database backup requires a previous full database backup. If your database has never been backed up, run a full database backup before creating any differential backups.

Since your full backups are scheduled to happen on Sundays, you're likely to see no error Monday. But you can configure your hallengren script with the option to change the backup type to avoid that situation on the future. From the hallengren doc:

ChangeBackupType

Change the backup type if a differential or transaction-log backup cannot be performed.

Value     Description
  Y       Change the backup type if a backup cannot be performed.
  N       Skip the backup if a backup cannot be performed. This is the default.

DatabaseBackup checks differential_base_lsn in sys.master_files to determine whether a differential backup can be performed. If a differential backup is not possible, then the database is skipped by default. Alternatively, you can set ChangeBackupType to Y to have a full backup performed instead.

Another possibility is that your full backup is using the WITH COPY_ONLY option. See Copy-Only Backups doc:

A copy-only backup cannot serve as a differential base or differential backup and does not affect the differential base.