Sample Header Ad - 728x90

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

0 votes
2 answers
65 views
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. enter image description here how can I work around that?
Asked by Marcello Miorelli (17274 rep)
Jul 30, 2024, 09:59 PM
Last activity: Jul 31, 2024, 06:24 AM