sqmSQLTool · Backup & Restore · 2026

Backup & Restore

FULL + DIFF + LOG. Selective. AlwaysOn-aware.

The sqmSQLTool backup strategy: daily differentials, selective backup of individual databases, automatic AG detection, and a structured restore in one pass.

FULL / DIFF / LOG Ola Hallengren AlwaysOn-aware sqm_BackupExclude Restore chain

Uwe Janke · Senior SQL Server DBA · dtcSoftware

Motivation

Why not just run FULL every day?

⏱

Backup windows keep shrinking

Growing databases mean a daily FULL backup fills the entire maintenance window. No room left for integrity checks, index maintenance, or statistics updates.

💾

Storage cost

7 x FULL per week = 7 x the full database size. With DIFF + LOG, Monday through Saturday only backs up the changed pages - typically 5-15% of the database size.

🚨

Some databases don't need a backup at all

Temporary databases, replication targets, staging DBs, Ola helper tools: these need to be identified and explicitly excluded - not backed up by accident.

🔄

AlwaysOn: which node backs up?

In an AG, backup jobs run on every node. Without a preferred-replica check, the backup runs multiple times - or not at all, if the active node changes.

Strategy

FULL + DIFF + LOG

FULL once a week (Sunday), DIFF daily (Mon-Sat), LOG hourly. The backup window shrinks, RPO drops to <1 h.

Sun

FULL

✓ LOG

Mon

DIFF

✓ LOG

Tue

DIFF

✓ LOG

Wed

DIFF

✓ LOG

Thu

DIFF

✓ LOG

Fri

DIFF

✓ LOG

Sat

DIFF

✓ LOG

1 x

FULL per week

Sunday, 8pm. The base for every restore chain that week.

6 x

DIFF per week

Mon-Sat 8pm. Only changed pages - typically 5-15% of the DB.

24 x

LOG per day

Hourly (Full recovery model). RPO: max. 1 hour.

FULL only

up to 24 h

+ DIFF

max. 1 h*

+ LOG

< 1 h

* DIFF without a LOG backup: recovery point = last DIFF, no point-in-time possible.

Architecture

Two backup engines

sqmSQLTool supports both approaches - native and via Ola Hallengren. Recommended: Ola for new environments.

⚙️

Native: New-sqmBackupMaintenanceJob

Creates SQL Agent jobs that call Invoke-sqmUserDatabaseBackup. No external framework needed.

BackupType	FULL \| DIFF \| LOG
Path	`<BackupDir>\User-Db`
Exclude	`-UseExcludeTable`
AlwaysOn	built in: AG databases on the primary only
Mail	`-MailTo`, `-MailOnSuccess`

ℹ One call per job type (FULL/DIFF/LOG). Job 1 syncs the exclude table, job 2 runs the backup.

⭐

Recommended: New-sqmOlaUsrDbBackupJob

Creates jobs built on Ola Hallengren's DatabaseBackup procedure. Industry standard, extensive options.

Switches	`-Full` `-Diff` `-Log`
Path	`<BackupDir>\Usr-db`
Exclude	`-UseExcludeTable`
Quality	`-Compress`, `-Verify`, `-CheckSum`
Logging	`-LogToTable` (msdb)

✓ One call creates all three jobs (FULL/DIFF/LOG) including schedules.

ℹ Ola Hallengren not installed? New-sqmOlaUsrDbBackupJob detects that and installs the Ola scripts (DatabaseBackup, CommandLog, DatabaseIntegrityCheck, IndexOptimize) automatically on the target instance.

Setup

New-sqmOlaUsrDbBackupJob

One PowerShell call sets up the entire FULL/DIFF/LOG job structure on an instance.

# Full setup: FULL (Sun 8pm) + DIFF (Mon-Sat 8pm) + LOG (hourly)
New-sqmOlaUsrDbBackupJob -SqlInstance 'SQL01\PROD' `
    -BackupDirectory 'D:\SQLBackup' `
    -Full  -FullScheduleTime '20:00'  -FullScheduleDays @('Sunday') `
    -Diff  -DiffScheduleTime '20:00'  -DiffScheduleDays @('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday') `
    -Log   -LogScheduleIntervalMinutes 60 `
    -UseExcludeTable `
    -Compress -Verify -CheckSum -LogToTable

SQL Agent jobs created:

Job name (configurable)	Type	Schedule	Path
`OlaHH-UserDatabases-FULL`	FULL	Sunday 8pm	`D:\SQLBackup\Usr-db`
`OlaHH-UserDatabases-DIFF`	DIFF	Mon-Sat 8pm	`D:\SQLBackup\Usr-db`
`OlaHH-UserDatabases-LOG`	LOG	Hourly	`D:\SQLBackup\Usr-db`

ℹ System databases (master, model, msdb) are backed up separately as FULL via New-sqmOlaSysDbBackupJob - they don't support DIFF/LOG.

Selective backup

Backing up only specific databases

Two approaches - combine as needed.

Approach 1: explicit list (-Database)

Directly in the function call - for ad-hoc or one-off backups.

# Back up only two databases
Invoke-sqmUserDatabaseBackup `
    -SqlInstance 'SQL01' `
    -Database 'Finance', 'Controlling' `
    -BackupPath 'D:\SQLBackup\User-Db'

# Back up everything except the exclude table
Invoke-sqmUserDatabaseBackup `
    -SqlInstance 'SQL01' `
    -All `
    -UseExcludeTable

When to use -Database

Recovery test for a specific database
Emergency backup before a patch
Migration: test a backup at a new path

Approach 2: the sqm_BackupExclude table

A persistent exclusion list in master. Changes apply immediately to every backup job - no job changes needed.

# Initialize the table (once)
Sync-sqmBackupExcludeTable `
    -SqlInstance 'SQL01'

# Exclude a database
INSERT INTO master.dbo.sqm_BackupExclude
    (DatabaseName, Reason)
VALUES ('StagingDB', 'No backup - repopulated daily')

# Read access for the backup service account
Set-sqmBackupExcludePermission `
    -SqlInstance 'SQL01' `
    -LoginName   'DOMAIN\sqlsvc'

Selective backup - detail

master.dbo.sqm_BackupExclude

The full table definition with an audit trail. Created and maintained automatically by Sync-sqmBackupExcludeTable.

Column	Type	Meaning
`DatabaseName`	sysname PK	Name of the database to exclude
`Reason`	nvarchar(255)	Justification (e.g. "Staging")
`ExcludedBy`	sysname	Who excluded it (`SUSER_SNAME()`)
`ExcludedAt`	datetime2	When (default: `SYSDATETIME()`)
`IsActive`	bit	1 = actively excluded, 0 = reactivated
`IsOrphaned`	bit	1 = the database no longer exists on the instance

sqm_BackupExclude_History

Every change to the exclude table is written automatically to the history table via the trg_sqm_BackupExclude_Audit trigger. A complete change history.

Sync-sqmBackupExcludeTable

Regularly reads the instance's current databases and automatically flags deleted ones as IsOrphaned = 1. No manual cleanup needed.

✓ Recommended: set up Sync-sqmBackupExcludeTable as step 1 in the backup job. New-sqmBackupMaintenanceJob does this automatically when -UseExcludeTable is set.

AlwaysOn

AG-aware backup

Backup jobs run on every AG node. Without a check, the backup runs multiple times, or on the wrong node.

The problem without a check

SQL01 (Primary)	Backup runs
SQL02 (Secondary)	Backup runs too
Result	Double storage use, same file name in the same second (OS error 32)

The fix: built into every backup path

Invoke-sqmUserDatabaseBackup `
    -SqlInstance            'SQL01' `
    -All                    `
    -UseExcludeTable

Since 1.9.166.0 no switch is needed: the role of the local replica is checked per AG database. Secondaries skip the database, the primary always backs it up.

How it works

The job runs on SQL01 and SQL02

Both nodes have identical backup jobs.

Checked per database

Role of the local replica from sys.dm_hadr_availability_replica_states: primary or not.

Backup only on the primary

SQL02 skips every AG database while it is secondary.

Transparent through failover

After failover to SQL02, SQL02 backs up - no job changes needed.

✓ Works with -Full, -Diff, and -Log. Both Ola Hallengren and the native engine.

⚡ AG backup preference is ignored on purpose: the primary always backs up. A preference like SECONDARY or a readable secondary can no longer lead to double backups or to a database without any backup.

Quality

Checking backup integrity

Test-sqmBackupIntegrity - RESTORE VERIFYONLY without an actual restore. Catches corrupt backup files before they're needed.

# Check every backup file in a path
Test-sqmBackupIntegrity `
    -SqlInstance 'SQL01' `
    -BackupPath  'D:\SQLBackup\User-Db'

# Output the file list (header info)
Test-sqmBackupIntegrity `
    -SqlInstance    'SQL01' `
    -BackupPath     'D:\SQLBackup\User-Db' `
    -FileListOnly

ℹ Internally: SQL Server runs RESTORE VERIFYONLY FROM DISK = '...'. Checks checksums, file sizes, and the backup header - no actual restore.

What gets checked

Backup header readable and complete
Checksums (if the backup was created with -CheckSum)
File size vs. expected size
Backup set completeness (no aborted backup)

Recommended use

Daily, as a separate job after the backup window
After a storage move / robocopy run
Before a planned restore test
-FileListOnly: inventory of every backed-up database

Restore

Invoke-sqmRestoreDatabase

The full restore workflow - from a single backup file to a FULL+DIFF+LOG chain. AlwaysOn-aware.

# Simple restore from one file
Invoke-sqmRestoreDatabase `
    -SqlInstance  'SQL01' `
    -BackupFile   'D:\SQLBackup\User-Db\Finance_FULL_20260518.bak'

# Restore chain: FULL + DIFF + LOG (point-in-time)
Invoke-sqmRestoreDatabase `
    -SqlInstance   'SQL01' `
    -BackupFiles   'D:\Backup\Finance_FULL.bak',
                   'D:\Backup\Finance_DIFF.bak',
                   'D:\Backup\Finance_LOG_10.trn',
                   'D:\Backup\Finance_LOG_11.trn' `
    -NewDatabaseName     'Finance_Restore_Test' `
    -NewDatabaseFilePath 'E:\MSSQL\DATA' `
    -NewLogFilePath      'F:\MSSQL\LOG'

⚡ The order in -BackupFiles determines the restore sequence: FULL first, then DIFF, then the LOGs in chronological order.

Restore - features

What the restore takes care of

AlwaysOn handling

`-KeepAlwaysOn`	Remove the DB from the AG, run the restore, then automatically add it back
`-RejoinAvailabilityGroup`	Rejoin the AG only, after a manual restore (WITH NORECOVERY)
`-WithNoRecovery`	Restore in NORECOVERY mode - for multi-stage restore chains

Safety & caution

`-BackupBeforeRestore`	Backs up the target database before it's overwritten - a safety net
`-ForceSingleUser`	Forces single-user mode if active connections exist

Automatic post-processing

Runs automatically after the restore:

✓

Repair orphaned users

Orphaned DB users are automatically linked back to existing logins.

✓

Remove non-existent Windows logins

Windows logins that no longer exist in AD are removed from the database.

✓

Set the DB owner to sa

Security consistency: the database owner is set to sa.

✓

Optionally suppress the user export

-NoUserExport: turn off user restoration for pure restore tests.

Reference

Quick reference

Task	Function
Set up backup jobs (Ola)	`New-sqmOlaUsrDbBackupJob`
Set up backup jobs (native)	`New-sqmBackupMaintenanceJob`
Back up system databases	`New-sqmOlaSysDbBackupJob`
Ad-hoc user DB backup	`Invoke-sqmUserDatabaseBackup`
Create/sync the exclude table	`Sync-sqmBackupExcludeTable`
Set exclude permission	`Set-sqmBackupExcludePermission`
Check a backup file	`Test-sqmBackupIntegrity`
Restore a database	`Invoke-sqmRestoreDatabase`

Parameter cheat sheet	Meaning
`-All`	Every user DB (respects the exclude table)
`-Database`	Explicit DB list
`-UseExcludeTable`	Honor sqm_BackupExclude
(built in)	AG-aware - AG databases on the primary only
`-BackupType DIFF`	Differential backup (native)
`-Diff`	Create the differential job (Ola)
`-BackupFiles`	Restore chain (FULL+DIFF+LOG[])
`-BackupBeforeRestore`	Safety net before overwriting

ℹ Backup path convention: <BackupDir>\Usr-db (Ola) vs. <BackupDir>\User-Db (native) - keep this consistent on a fresh install.

sqmSQLTool - Core module AlwaysOnSetup - AG setup SQLSetupTool - Base install SQLMigration - Database migration

Boundaries

Scope & prerequisites

Prerequisite	Detail
SQL Server version	2016 and later recommended
Recovery model	FULL for LOG backups (sqmSQLTool doesn't check this - a DBA task)
Ola Hallengren	Must be installed on the instance (the `DatabaseBackup` SP in msdb or master)
SQL Server Agent	Running and reachable
Backup path	The SQL Server service account has write access
AlwaysOn	AG configuration already set up (e.g. via AlwaysOnSetup)
Database Mail	Optional - for mail notifications (-MailTo)

What sqmSQLTool backup isn't

ℹ Not a backup monitoring dashboard. sqmSQLTool sets up jobs and runs backups. Success monitoring is handled by the SQL Agent job history or Database Mail.

ℹ Not a tape/cloud backup. sqmSQLTool backs up to local or network paths. Archiving to tertiary storage is a separate task.

Checking the recovery model

-- Confirm before a LOG backup:
SELECT name, recovery_model_desc
FROM   sys.databases
WHERE  recovery_model_desc <> 'FULL'
  AND  name NOT IN ('tempdb', 'model')

Summary

Rollout plan

From zero to a running FULL+DIFF+LOG strategy in four steps.

Confirm the recovery model

Set every database that needs backing up to FULL. No LOG backup is possible without Full recovery.

Initialize the exclude table

Run Sync-sqmBackupExcludeTable on every instance. Add staging, replication targets, and temporary DBs.

Set up the backup jobs

New-sqmOlaUsrDbBackupJob with -Full -Diff -Log -UseExcludeTable -Compress -Verify. System databases separately with New-sqmOlaSysDbBackupJob.

Run a restore test

Invoke-sqmRestoreDatabase -BackupFiles ... on a test system. Confirm backup integrity with Test-sqmBackupIntegrity.

            🏆 What we gain
            
              Backup window-70% Mon-Sat, thanks to DIFF instead of FULL
RPO< 1 hour with hourly LOG
ControlSelective databases with no job changes
AlwaysOnOne job set for every AG node
TraceabilityAudit trail in sqm_BackupExclude

            
          

Relevant functions (8)

New-sqmOlaUsrDbBackupJob New-sqmOlaSysDbBackupJob New-sqmBackupMaintenanceJob Invoke-sqmUserDatabaseBackup Sync-sqmBackupExcludeTable Set-sqmBackupExcludePermission Test-sqmBackupIntegrity Invoke-sqmRestoreDatabase

sqmSQLTool - Core module AlwaysOnSetup - AG setup SQLSetupTool - Base install SQLMigration - Database migration

Backup window	-70% Mon-Sat, thanks to DIFF instead of FULL
RPO	< 1 hour with hourly LOG
Control	Selective databases with no job changes
AlwaysOn	One job set for every AG node
Traceability	Audit trail in sqm_BackupExclude