To be sure that an attempt is made to take a backup on each database, have some history easy to access and also a option to reconfigure the job for en extra backup on one or more databases I set up one job step for a backup of each database.
This job structure could be illustrated like this:
- job step 1: Backup database alpha.
- job step 2: Backup database beta.
- job step 3: Backup database gamma.
To ensure a true job status I have added a final control job step, that check the status of the previous job steps.
The status of the previous job steps I get from the table msdb.dbo.sysjobhistory where I filter on the job start time by inserting SQL Agent Tokens in the T-SQL statement:
DECLARE @error_count INT = (
WHERE job_id = $(ESCAPE_NONE(JOBID))
AND [run_date] = $(ESCAPE_NONE(STRTDT))
AND [run_time] >= $(ESCAPE_NONE(STRTTM))
AND [run_status] <> 1);
IF (@error_count > 0)
DECLARE @error_msg NVARCHAR(2047) = N''Error in Database Full Backup job. Check log file for details. (Error Count = '' + CAST(@error_count AS NVARCHAR(3)) + N'' job steps).'';
RAISERROR(@error_msg, 19, 0) WITH LOG;
RAISERROR(''Database Full Backup job conpleted with no errors.'', 0, 0) WITH NOWAIT;
This control jobs step generates a error if a previous job step in the same job has failed. The plan (hope) is that the general monitoring platform like Microsoft System Center will catch the error and raise an incident to the Service Desk.