Automating Backups With SQL Agent and Checking They Worked

To automate SQL Server backup reliably, schedule the backup and the checks that make its outcome meaningful. Include verification, retention, failure notification, and a separate restore exercise in the design.

Start With the Recovery Requirement

Decide how much data loss and recovery time the application can tolerate. That determines the backup types and frequency. A nightly full backup alone does not provide an arbitrary point-in-time recovery path.

SQL Server Agent is available in supported editions but not Express. Express needs an external scheduler with equivalent failure handling. Confirm the backup destination and the service identity's access before building the schedule.

SELECT name, recovery_model_desc, state_desc
FROM sys.databases
WHERE database_id > 4;
SELECT SERVERPROPERTY('InstanceDefaultBackupPath') AS default_backup_path;

Use a dedicated destination with monitored capacity and the required separate protection or off-host copy. A backup stored only beside the database files may share the same failure. Record who owns retention and recovery testing.

Create a Backup With a Unique Filename

The following T-SQL can form the backup-and-verification step for one database. Replace the database and destination with reviewed values. The directory must already exist on the server and be writable by the engine service.

DECLARE @Database sysname = N'YourDatabase';
DECLARE @Folder nvarchar(260) = N'D:\SQLBackups\';
IF DB_ID(@Database) IS NULL OR DB_ID(@Database) <= 4
    THROW 50030, 'Choose the intended user database.', 1;
DECLARE @File nvarchar(4000) = @Folder
    + N'Full_' + CONVERT(nvarchar(36), NEWID()) + N'.bak';
BACKUP DATABASE @Database TO DISK = @File WITH CHECKSUM;
RESTORE VERIFYONLY FROM DISK = @File WITH CHECKSUM;
SELECT @Database AS database_name, @File AS backup_file;

The unique name avoids routinely overwriting the previous backup. This is a normal full backup, so account for its role in any differential strategy. Add compression only after checking support and testing the workload tradeoff.

VERIFYONLY checks readability and backup completeness, with checksum checking when requested. It does not restore the database or validate all internal data structures. Keep an actual restore test as a separate required activity.

Give the Job Explicit Success and Failure Paths

In SSMS, create an Agent job with a T-SQL step containing the reviewed command. Set its database context to master and choose an appropriate job owner. Configure step failure to quit reporting failure.

Add the schedule that matches the recovery plan, then review overlap and maintenance timing. For a maintenance-plan implementation, use a backup task with verification followed by cleanup through a success-only connection. Its generated Agent job provides the schedule and outcome.

If using separate job steps, make the backup step continue only after successful verification. Do not configure failure to continue into cleanup. Test the complete job under its scheduled execution context, not only from your administrator query window.

Make Cleanup Respect the Backup Chain

Use the Maintenance Cleanup task with a dedicated folder, exact backup extension, and approved age policy. Review subfolder inclusion explicitly. The task works on local files and does not decide which files your recovery chain still needs.

Retain the full backup required by every retained differential or log sequence. Age alone can be a poor approximation of recoverability. Validate the policy against representative restore sequences before enabling deletion.

Keep cleanup after successful backup verification, with failure reporting of its own. Purging msdb history is a separate operation from deleting backup files. Neither should remove the only evidence needed to understand retained media.

Test the Failure Notification

Configure Database Mail, an operator, and Agent's mail settings through the approved mail infrastructure. Set the job to notify the operator on failure. Do not assume saving an email address proves delivery.

SELECT j.name, j.enabled, j.notify_level_email,
       o.name AS operator_name, o.enabled AS operator_enabled
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysoperators AS o
  ON o.id = j.notify_email_operator_id
ORDER BY j.name;

Use a controlled lab failure to test the path from failed step to received notification. Also monitor a job that never starts, since it cannot report its own failure. Keep the response instructions with the alert.

Check History and Rehearse Recovery

Review job history together with backup history. A completed job can still have an incorrectly configured flow, while backup history does not prove the file remains accessible. Use both as evidence rather than substitutes for recovery testing.

SELECT TOP (20) b.database_name, b.type,
       b.backup_start_date, b.backup_finish_date,
       b.has_backup_checksums, m.physical_device_name
FROM msdb.dbo.backupset AS b
JOIN msdb.dbo.backupmediafamily AS m
  ON m.media_set_id = b.media_set_id
ORDER BY b.backup_finish_date DESC;

Restore retained backups to an approved alternate location and validate the recovered database and application path. Record the files and keys required. The schedule is dependable when the recovery process is demonstrated, not merely recurring.

Backup automation is not a recurring command, it is a monitored recovery process.

This post was rewritten from scratch in September 2026. The original, published on 2016-03-02, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.