Running SQL Server Backup Jobs with Ola Hallengren and Plain T-SQL: An Admin Guide

This guide covers the setup produced by New-sqmBackupMaintenanceJob from sqmSQLTool, but the moving parts are plain SQL Server objects: an Agent job, a stored procedure in master, a small control table, and Ola Hallengren's Maintenance Solution. You can operate, inspect and change all of it with nothing but SSMS.

Why It Is Built This Way

Three design decisions shape everything below, and knowing them makes the rest predictable:

• The job step is Transact-SQL, not PowerShell. A T-SQL step runs inside the engine: nothing to install on the server, no module versions to keep in sync, no external process that has to start and exit cleanly for the step to finish. The backup runs where the data is.
• The logic lives in a stored procedure, the step is one line. You can read the step at a glance, and you can run, test or change the procedure without touching the job. One generic procedure serves every job and every backup type on the instance, so there is one place to look, not one copy per job drifting apart over time.
• Each database is backed up by its own call. Failures stay isolated - one database that cannot be backed up does not take the other forty with it - and excluding a database is a per-database decision instead of an ever-growing exclusion string.

Everything that varies between jobs and instances - directory, backup type, retention, mail - is a parameter passed by the job step, so the configuration is visible where an operator actually looks, and the procedure itself contains no hard-coded values.

How the Pieces Fit Together

Object	Where	Role
DatabaseBackup	master	Ola Hallengren's procedure. Does the actual BACKUP, verification, naming and file cleanup.
sqm_BackupUserDatabases	master	Loops over the databases and calls DatabaseBackup once per database. One generic procedure for every job and backup type.
sqm_BackupExclude	master	Control table: which databases to skip. Optional.
CommandLog	master	Ola's own log table - every command it issued, with duration and outcome.
Agent job	msdb	One T-SQL step containing one EXEC with all parameters spelled out.

Per-database calls are deliberate. Handing Ola a whole list in one call is fewer statements, but then one broken database can take the run down with it, and the exclusion list has to be assembled as one long -DatabaseName,... string that becomes unmanageable on instances with many exclusions. One call per database keeps failures isolated and the exclusion decision simple.

Prerequisites

Ola's Maintenance Solution must be present on the instance. Check:

SELECT OBJECT_ID(N'master.dbo.DatabaseBackup', N'P') AS DatabaseBackup,
       OBJECT_ID(N'master.dbo.CommandLog',     N'U') AS CommandLog;

If DatabaseBackup is NULL, install it from ola.hallengren.com (or with Install-sqmOlaMaintenanceSolution; Test-sqmOlaInstallation reports what is present). The backup target directory must exist and the SQL Server service account must be able to write to it - not your account. That distinction bites people with UNC paths: the backup is written by the engine, under its own service identity, no matter who started the job.

Creating the Jobs

Import-Module sqmSQLTool

New-sqmBackupMaintenanceJob -SqlInstance "SQL01" -BackupType FULL -UseExcludeTable
New-sqmBackupMaintenanceJob -SqlInstance "SQL01" -BackupType LOG  -UseExcludeTable

Each call creates (or with -Update, replaces) one job, and drops and recreates the shared procedure. So -Update refreshes the backup logic too, not just the job definition. Without -BackupPath, the instance's configured backup directory plus \Usr-db is resolved at creation time and written into the step, where it stays visible.

On an Availability Group, one call covers every replica

If the instance belongs to an Availability Group, you do not run this once per replica. The command detects the other replicas from sys.availability_replicas and repeats the whole setup on each of them automatically: the Agent job and the stored procedure in master, with the same schedule, directory, retention and exclude settings.

That the procedure is created separately on every replica is not redundancy, it is necessary: master is a system database and is never part of an Availability Group, so nothing in it replicates. Each replica needs its own copy, and this is what puts it there.

The Job Step

This is the entire step. Every setting is visible, and you can edit it here without going back to PowerShell:

EXEC master.dbo.[sqm_BackupUserDatabases]
     @BackupType               = N'FULL',
     @Directory                = N'D:\Backup\Usr-db',
     @CleanupTime              = 672,
     @UseExcludeTable          = 1,
     @SyncExcludeTable         = 1,
     @IncludeSystemDatabases   = 0,
     @Verify                   = 'Y',
     @Compress                 = 'Y',
     @Checksum                 = 'Y',
     @OverrideBackupPreference = 'Y',
     @LogToTable               = 'Y',
     @MailTo                   = NULL,
     @MailProfile              = N'Default',
     @MailOnSuccess            = 0;

Parameters

Parameter	Default	Meaning
@BackupType	required	FULL, DIFF or LOG. Passed straight to Ola.
@Directory	required	Root backup directory. Ola creates per-database and per-type subfolders below it.
@CleanupTime	NULL	Retention in hours. NULL = never delete anything.
@UseExcludeTable	0	Honour sqm_BackupExclude and skip databases marked inactive.
@SyncExcludeTable	1	Reconcile that table with sys.databases before backing up, so new databases appear automatically.
@IncludeSystemDatabases	0	Include master, model and msdb. Leave at 0 if you back those up separately.
@Verify	'Y'	RESTORE VERIFYONLY after each backup. Costs time, catches unreadable backups early.
@Compress / @Checksum	'Y'	Backup compression and page checksums.
@OverrideBackupPreference	'Y'	'N' honours the Availability Group backup preference, so only the preferred replica backs up. See the AG section below.
@LogToTable	'Y'	Write every command to master.dbo.CommandLog. Keep this on.
@MailTo	NULL	Database Mail recipient for the run report. NULL = no mail.
@MailOnSuccess	0	By default mail is only sent on failures; 1 sends it every run.

Retention: Always Hours

Ola's @CleanupTime is expressed in hours, so everything in this setup is:

Retention	@CleanupTime	Typical use
2 days	48	LOG backups
1 week	168	DIFF on a short cycle
2 weeks	336	DIFF (default)
4 weeks	672	FULL (default)

Cleanup runs after a successful backup (Ola's AFTER_BACKUP mode), so a failed run never leaves you having deleted the old backups without having produced a new one.

Excluding a Database

master.dbo.sqm_BackupExclude holds one row per database. IsActive = 1 means "back this up" and is the default for anything newly discovered, so you never have to remember to add a new database - only to exclude one. You can maintain it with plain SQL, or in a dialog (see below):

-- stop backing up a database
UPDATE master.dbo.sqm_BackupExclude
SET    IsActive = 0, Reason = N'decommissioned 2026-09'
WHERE  DatabaseName = N'OldAppDB';

-- current state
SELECT DatabaseName, IsActive, IsOrphaned, Reason
FROM   master.dbo.sqm_BackupExclude
ORDER  BY IsActive, DatabaseName;

IsOrphaned = 1 marks a row whose database no longer exists on the instance. The row is kept rather than deleted, so that an exclusion decision survives a database being detached and reattached; if the database comes back, the flag clears itself on the next run.

Doing it in a dialog instead

If you would rather not write UPDATE statements against a control table - or want to hand the decision to someone who shouldn't - Show-sqmBackupExcludeForm opens a small dialog for exactly this table:

Show-sqmBackupExcludeForm -SqlInstance "SQL01"

Sync & Laden runs Sync-sqmBackupExcludeTable to reconcile the table with the current databases and fills the grid. From there you tick IsActive on or off per database and edit the Reason text; orphaned rows are highlighted so a database that has disappeared is obvious at a glance. Speichern writes back only the rows you actually changed.

Schedules

Defaults when you don't specify a schedule:

Type	Schedule	Retention
FULL	daily 20:15	672 h (4 weeks)
DIFF	Mon-Sat 20:00	336 h (2 weeks)
LOG	every 15 min from 00:00	48 h (2 days)

The LOG start time matters more than it looks: an interval schedule only repeats between its start time and end of day, so starting at 00:00 is what makes "every 15 minutes" actually mean all day.

Verifying a Run

Three independent places to look, in increasing detail. What was backed up, according to SQL Server itself:

SELECT database_name, type, backup_start_date,
       CAST(compressed_backup_size/1024.0/1024 AS decimal(10,1)) AS SizeMB
FROM   msdb.dbo.backupset
WHERE  backup_start_date > DATEADD(DAY, -1, GETDATE())
ORDER  BY backup_start_date DESC;

type is D for full, I for differential and L for log - the quickest way to confirm a "LOG" job is really taking log backups.

What the procedure actually did, including failures and skips, is in the job history (View History, or the step output). A healthy run ends with a summary line:

Backing up SalesDB (FULL)...
Skipping OldAppDB (sqm_BackupExclude: IsActive = 0).
Done. OK: 18, skipped: 1, failed: 0.

And Ola's own log, with the exact command and duration per database:

SELECT TOP 50 DatabaseName, CommandType, StartTime, EndTime, ErrorNumber, Command
FROM   master.dbo.CommandLog
ORDER  BY ID DESC;

Confirming it with sqmSQLTool

The same three questions have ready-made answers in the module, which is usually quicker than writing the queries yourself:

Question	Command
Does every database have a current backup?	Get-sqmDatabaseHealth reports the last FULL, DIFF and LOG backup per database - and reads sqm_BackupExclude while doing so, so a database you excluded on purpose is not reported as a gap. That is exactly the coverage check above, without the manual comparison.
Are the files themselves readable?	Test-sqmBackupIntegrity runs RESTORE VERIFYONLY against one or more backup files (stripes included) and returns true or false.
Can the backup actually be restored?	Invoke-sqmRestoreTest restores into a new, throwaway database - never over the original - and documents outcome, data volume, duration and throughput as a TXT and HTML report.

Get-sqmDatabaseHealth   -SqlInstance "SQL01"
Test-sqmBackupIntegrity -SqlInstance "SQL01" -BackupFile "D:\Backup\Usr-db\SalesDB\FULL\SalesDB_FULL_20260917.bak"
Invoke-sqmRestoreTest   -SqlInstance "SQL01" -Database "SalesDB"

Availability Groups

Two things work together here: the setup is deployed to every replica, and the backup preference decides which replica actually backs up.

What gets deployed where

Creating a job on an AG member automatically repeats the same setup on every other replica, unless you pass -SkipAlwaysOnPropagation. On each replica this creates:

Created on every replica	Why
The Agent job and its schedule	Jobs live in msdb, which does not replicate. Each replica needs its own.
master.dbo.sqm_BackupUserDatabases	master is a system database and is never part of an AG, so the procedure cannot arrive there by replication.
master.dbo.sqm_BackupExclude	Same reason. The exclusion decision therefore has to be maintained per replica - see the note below.

Propagation runs in update mode, so repeating the command is safe: existing jobs and procedures are refreshed rather than duplicated. If one replica cannot be reached, that is logged as a warning and the remaining replicas still get their copy, so a single unreachable node does not abort the rollout. Check the result for entries marked Secondary_* to see what happened where.

Which replica actually backs up

With @OverrideBackupPreference = 'Y' (the default here) every replica backs up what it has, regardless of the AG backup preference. That is the right default for a standalone instance and usually wrong for an AG, where it produces backups on several replicas at once.

On an AG, set it to 'N' and let the backup preference decide. Every replica then runs the same job on the same schedule, and only the preferred one actually takes backups. After a failover no job needs to be touched: the new primary was already running its copy, and the preference simply resolves differently. Set the preference on the AG itself, and remember that log backups taken on a secondary still form one continuous log chain with the primary's.

When Something Goes Wrong

Symptom	Where to look
Job fails, some databases fine	By design: the step fails at the end if any database failed, after attempting all of them. The failing one is named in the step output and in CommandLog.ErrorNumber.
"The directory does not exist"	Ola checks the target. Verify the path exists and that the SQL Server service account can write to it - test from the engine's perspective, not from Explorer under your own account.
LOG backup fails for one database	Almost always SIMPLE recovery model, or no full backup taken yet. Ola skips SIMPLE databases for log backups rather than failing.
A database is missing from the backups	Check sqm_BackupExclude for IsActive = 0 first; then whether it is online in sys.databases.
Job reports success but no files appear	Confirm the step is the EXEC you expect and that @Directory points where you think. Changing the procedure does not change existing jobs - recreate them with -Update.
Backup files pile up	@CleanupTime = NULL means no cleanup. Note Ola only cleans the directory structure it manages, and only for the backup type being run.

Changing Things Safely

Because everything lives in the step parameters, most changes are an edit in SSMS - retention, mail recipient, target directory - and take effect on the next run. Two things worth knowing:

• ☐ Re-running New-sqmBackupMaintenanceJob with -Update overwrites the step, so manual edits made in SSMS are lost. Make the change in the parameters you pass to the function if it should be permanent.
• ☐ You can run the procedure directly to test a change without touching the job or waiting for a schedule - it's an ordinary stored procedure:

  EXEC master.dbo.sqm_BackupUserDatabases
       @BackupType = N'FULL', @Directory = N'D:\Backup\Test',
       @UseExcludeTable = 1, @CleanupTime = NULL;

Related sqmSQLTool Commands

Full parameter reference for every function used or mentioned above:

Command	What it does here
New-sqmBackupMaintenanceJob	Creates the backup job and the procedure described in this guide.
Install-sqmOlaMaintenanceSolution	Installs Ola Hallengren's Maintenance Solution on the instance.
Test-sqmOlaInstallation	Reports whether the Maintenance Solution is present and the Agent is running.
Show-sqmBackupExcludeForm	Dialog for maintaining the exclude table.
Sync-sqmBackupExcludeTable	Reconciles the exclude table with the current databases.
Register-sqmBackupExcludeTrigger	Optional DDL trigger that adds newly created databases to the exclude table immediately, instead of at the next run.
Invoke-sqmUserDatabaseBackup	Ad-hoc backup of user databases without going through a job.
Get-sqmDatabaseHealth	Reports the last FULL/DIFF/LOG backup per database, aware of the exclude table.
Test-sqmBackupIntegrity	Verifies existing backup files with RESTORE VERIFYONLY.
Invoke-sqmRestoreTest	Restore test into a throwaway database, with an auditable report - the only check that proves a backup is usable.
New-sqmRestoreTestJob	Schedules that restore test as a recurring job.
New-sqmOlaUsrDbBackupJob	Alternative job builder: separate FULL/DIFF/LOG jobs in Ola's own naming scheme.

The complete command reference is at sqmSQLTool commands.

A Short Checklist

• ☐ master.dbo.DatabaseBackup exists on the instance.
• ☐ Target directory exists and is writable by the SQL Server service account.
• ☐ Retention set in hours, and checked against the actual restore requirement.
• ☐ sqm_BackupExclude reviewed - every IsActive = 0 is deliberate (quickest with Show-sqmBackupExcludeForm).
• ☐ LOG job starts at 00:00 if it is meant to cover the whole day.
• ☐ @OverrideBackupPreference = 'N' on Availability Groups, and job plus procedure present on every replica (propagated automatically when the job was created).
• ☐ Coverage monitored per database, not from job outcome alone (Get-sqmDatabaseHealth).
• ☐ A restore actually tested (Invoke-sqmRestoreTest). An unverified backup is a hypothesis.