how to prevent the diff backup running on days when the full backup is scheduled to run?

Question

I have an environment that we want a full backup once a week (if we fail we can tolerate up to 2 weeks) but the diffs have to be running every day, every day but not the days when the full is scheduled to run.

to achieve this I have create a first step called check day on my diff backup job and I put there something like this:

DECLARE @DateInt INT = CAST(CONVERT(VARCHAR(8),GETDATE(),112) AS INT)

DECLARE @NextFullBU INT = ( SELECT next_run_date
                            FROM sysjobschedules b
                            JOIN sysjobs A
                            ON A.job_id = B.job_id
                            JOIN sysschedules s
                            ON b.schedule_id = s.schedule_id
                            WHERE a.name = 'DatabaseBackup - USER_DATABASES - FULL'
                            AND s.name = 'DatabaseBackup - USER_DATABASES - FULL'
                          )

IF @DateInt = @NextFullBU
BEGIN
  RAISERROR ('This job must not execute today because of the FULL backup.',16,127)
END

However, that marks the job as failed, which is not in reality.

how can I work around that?

Answer 1

You can set up the weekly schedule to run on every day except on the day of the full backup:

Answer 2

how can I work around that?

Put this condition in 'DatabaseBackup - USER_DATABASES - DIFF' step like

IF @DateInt != @NextFullBU
BEGIN
 EXECUTE [dbo].[DatabaseBackup]
 @Databases = 'USER_DATABASES',
 @Directory = NULL,
 @BackupType = 'DIFF',
 @Verify = 'Y',
 @CleanupTime = 378,
 @CheckSum = 'Y',
 @LogToTable = 'N'
END