Automated backups must lead to a working restore
SQL Server Agent schedules backups, but job success does not guarantee service recovery. Jira and Confluence need coordinated databases, application files and attachments. This guide pairs whitelists, jobs, audit and cleanup with restore testing.
Manual backup suits individual changes. Routine protection needs automation, failure alerts and freshness checks for every database. A .bak extension is not integrity evidence.
RPO limits data loss and RTO limits whole-service restoration time, including retrieval, restore, validation and startup. Agree these targets with the service owner and measure them in drills.
Replicas propagate destructive changes too. Snapshots and replication do not replace backup history and independent copies outside primary storage.
Versions, service identities and implementation scope
- This scenario uses standalone SQL Server 2019/2022 on Windows with Standard, Enterprise or Developer. Developer is nonproduction only. Express lacks SQL Server Agent and requires a separately designed scheduler such as Windows Task Scheduler.
- Agent must be running with an appropriate startup setting. This implementation does not target Azure SQL Database, Availability Groups or UNC storage. AG deployments require separate replica-policy and SQL-version assessment.
- The Database Engine service identity writes backups. A CmdExec proxy creates folders and performs cleanup. Permissions for one identity do not substitute for the other.
- Grant the engine required reads/writes on dedicated backup paths and the proxy folder-creation/file-deletion rights. Avoid Everyone and broad grants. Restrict the script directory to proxy Read/Execute and DBA/system-administrator modifications.
- PowerShell uses Windows PowerShell 5.1 and built-in SqlClient. The SqlServer module is unnecessary. Integrated Security, TLS encryption and certificate validation are enabled. Replace localhost with an instance/FQDN matching a trusted certificate. Do not toggle TrustServerCertificate to conceal trust errors.
- The eight whitelist names are examples. Disable absent databases before testing. The procedure does not silently skip them. System databases are rejected: protect master, model and msdb separately. Tempdb cannot be backed up.
COMPRESSION reduces backup size and IO at a CPU cost. Measure duration and CPU before enabling it during busy periods and check edition/version support.
A backup from a newer SQL Server engine does not restore to an older engine. Prepare the restore destination with compatible versions and required certificates before an incident.
Design schedules and recovery chains for Jira and Confluence
Follow the scenario with DB-Jira and DB-Confluence on one standalone SQL Server. The targets are roughly fifteen minutes of potential data loss and an initial recovery time of sixty minutes. Sixty minutes is a contractual target that only a real restore can validate. Database size, network throughput and the number of logs to apply all affect the measured recovery time.
| Type | Schedule | Purpose |
|---|---|---|
| Full Backup | Daily at 01:00 | Recovery base and differential baseline |
| Differential Backup | 07:00, 13:00 and 19:00 | Changes since the last ordinary full. Less restore work |
| Transaction Log Backup | Every fifteen minutes, around the clock | Changes between data backups and point-in-time recovery |
| Retention and Cleanup | Thirty-day window. Daily cleanup at 05:00 | Remove expired files while preserving the chain required throughout the window |
A differential is cumulative from its base full, not from the previous differential. Restore the compatible full, latest appropriate differential and required logs in LSN order. For a 14:10 incident, review the 01:00 full, 13:00 differential and subsequent logs. The recoverable endpoint depends on the last healthy log and, where possible, a tail-log backup rather than the incident clock time alone.
A fifteen-minute schedule does not guarantee RPO: runtime, failure, delayed secondary copies and missing logs affect actual loss. SQL Server permits full and log backups concurrently. This procedure coordinates full/differential per database without making log backups wait behind a long full.
Schedules use the SQL server’s local clock. Filenames and audit use UTC. Record NTP/timezone in the runbook. Jira/Confluence also require coordinated Application Home, Shared Home, attachments and configuration according to their installed-version guidance.
Choose recovery models before enabling log jobs
| Model | Log Backup | Recovery Scope |
|---|---|---|
| SIMPLE | Not supported | Existing full/differential only. No arbitrary point-in-time recovery |
| FULL | Required for this policy | Point-in-time recovery with an intact log chain |
| BULK_LOGGED | Supported | Cannot stop within a log containing minimally logged operations |
SIMPLE still logs transactions. Its inactive log can be reused after checkpoint when no blocker remains, and it does not preserve a transaction-log backup chain. BACKUP LOG is therefore unavailable. FULL is selected for these Jira/Confluence databases because changes between data backups must be recoverable.
After SIMPLE changes to FULL or BULK_LOGGED, a valid full or differential data backup must establish the log chain. This runbook takes a fresh ordinary full for an explicit differential/recovery baseline. This is not a requirement to take full after every model change: FULL-to-BULK_LOGGED transitions do not break the chain like SIMPLE. Take a log backup before leaving FULL/BULK_LOGGED. Returning from SIMPLE requires a new chain.
SELECT name,recovery_model_desc,log_reuse_wait_desc
FROM sys.databases WHERE name IN (N'DB-Jira',N'DB-Confluence');
GO
ALTER DATABASE [DB-Jira] SET RECOVERY FULL;
ALTER DATABASE [DB-Confluence] SET RECOVERY FULL;
GO
-- After installing the procedure and preparing folders:
EXEC DBAOperations.dbo.usp_BackupWhitelist @BackupType='FULL';
EXEC DBAOperations.dbo.usp_BackupWhitelist @BackupType='LOG';
GOThese commands deliberately change only the two scenario databases. Decide the other whitelist members’ models separately. BULK_LOGGED is not a permanent log-size fix. Accept its recovery limitations before bulk operations.
Per-database paths and storage capacity
D:\SQLBackup
├── DB-Jira
│ ├── FULL
│ ├── DIFF
│ └── LOG
└── DB-Confluence
├── FULL
├── DIFF
└── LOGPer-database paths simplify restore selection, capacity checks and cleanup audit. Full/differential use .bak. Logs use .trn. Names include database, type, UTC milliseconds and GUID, preventing manual runs/retries from overwriting earlier files.
D: is only an example path. Different drive letters do not establish independent storage. Shared disks/LUNs still create a common failure point. Give multiple instances separate roots and management databases to avoid mixed audit/files.
Procedure, whitelist and folder provisioning
The file below creates the controlled execution components: the DBAOperations management database, an allowlist, a result table and a stored procedure. The allowlist contains only the requested names; it does not automatically include every database on the server. Compression and CHECKSUM remain enabled. If one database backup fails, TRY/CATCH records the error, attempts the others and ultimately fails the step so a partial failure is not hidden by the overall result.
The procedure validates paths and rejects system databases, snapshots, offline databases and AG members. LOG on SIMPLE is an error. A PowerShell step creates folders without xp_cmdshell/xp_create_subdir. The T-SQL job runs as a trusted DBA identity with backup/VERIFYONLY rights. A more restricted design requires module signing and permission tests.
-- SQL Server 2019/2022 on Windows, Standard/Enterprise/Developer.
-- DBA installation. No xp_cmdshell or undocumented folder procedures.
USE master;
GO
IF DB_ID(N'DBAOperations') IS NULL CREATE DATABASE [DBAOperations];
GO
USE [DBAOperations];
GO
IF OBJECT_ID(N'dbo.BackupWhitelist', N'U') IS NULL
CREATE TABLE dbo.BackupWhitelist
(
DatabaseName sysname NOT NULL PRIMARY KEY,
Enabled bit NOT NULL CONSTRAINT DF_BackupWhitelist_Enabled DEFAULT (1)
);
IF OBJECT_ID(N'dbo.BackupAudit', N'U') IS NULL
BEGIN
CREATE TABLE dbo.BackupAudit
(
AuditId bigint IDENTITY PRIMARY KEY,
RunId uniqueidentifier NOT NULL,
DatabaseName sysname NOT NULL,
BackupType varchar(4) NOT NULL,
StartedUtc datetime2(3) NOT NULL,
FinishedUtc datetime2(3) NULL,
BackupRoot nvarchar(200) NOT NULL,
BackupFile nvarchar(260) NULL,
Status varchar(12) NOT NULL,
Verified bit NOT NULL CONSTRAINT DF_BackupAudit_Verified DEFAULT (0),
ErrorNumber int NULL,
ErrorMessage nvarchar(4000) NULL
);
CREATE INDEX IX_BackupAudit_Retention
ON dbo.BackupAudit(DatabaseName, BackupType, Status, FinishedUtc);
END;
INSERT dbo.BackupWhitelist(DatabaseName)
SELECT v.DatabaseName FROM (VALUES
(N'DB-Confluence'), (N'DB-Jira'), (N'DWConfiguration'), (N'DWDiagnostics'),
(N'DWQueue'), (N'ORACLE_VIEW'), (N'RAYDANA_DB'), (N'STLA')
) v(DatabaseName)
WHERE NOT EXISTS
(SELECT 1 FROM dbo.BackupWhitelist w WHERE w.DatabaseName = v.DatabaseName);
GO
CREATE OR ALTER PROCEDURE dbo.usp_BackupWhitelist
@BackupType varchar(4),
@BackupRoot nvarchar(200) = N'D:\SQLBackup',
@Verify bit = 1,
@EncryptionCertificate sysname = NULL
AS
BEGIN
SET NOCOUNT ON;
IF @@TRANCOUNT > 0 THROW 51000, 'Run backups outside a user transaction.', 1;
SET @BackupType = UPPER(@BackupType);
IF @BackupType IS NULL OR @BackupType NOT IN ('FULL','DIFF','LOG')
THROW 51001, 'BackupType must be FULL, DIFF or LOG.', 1;
IF @Verify IS NULL THROW 51002, 'Verify must be 0 or 1.', 1;
IF @BackupRoot IS NULL OR LEN(@BackupRoot) > 100
OR @BackupRoot NOT LIKE N'[A-Za-z]:\%'
OR CHARINDEX(N'..', @BackupRoot) > 0 OR CHARINDEX(N'/', @BackupRoot) > 0
THROW 51003, 'Use a local absolute backup root, up to 100 characters.', 1;
WHILE RIGHT(@BackupRoot, 1) = N'\'
SET @BackupRoot = LEFT(@BackupRoot, LEN(@BackupRoot)-1);
IF LEN(@BackupRoot) < 4 THROW 51004, 'Do not use a drive root.', 1;
IF NOT EXISTS (SELECT 1 FROM dbo.BackupWhitelist WHERE Enabled = 1)
THROW 51005, 'Whitelist is empty; no backups were taken.', 1;
IF @EncryptionCertificate IS NOT NULL AND NOT EXISTS
(SELECT 1 FROM master.sys.certificates WHERE name = @EncryptionCertificate)
THROW 51006, 'Encryption certificate is missing in master.', 1;
DECLARE @RunId uniqueidentifier = NEWID(), @Database sysname,
@AuditId bigint, @File nvarchar(260), @Sql nvarchar(max),
@Stamp varchar(20), @Recovery nvarchar(60), @DatabaseId int,
@State nvarchar(60), @SourceId int, @Failures int = 0,
@LockResult int, @LockResource nvarchar(255), @Locked bit;
DECLARE databases CURSOR LOCAL FAST_FORWARD FOR
SELECT DatabaseName FROM dbo.BackupWhitelist WHERE Enabled = 1
ORDER BY DatabaseName;
OPEN databases;
FETCH NEXT FROM databases INTO @Database;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @File = NULL;
SET @Locked = 0;
INSERT dbo.BackupAudit(RunId,DatabaseName,BackupType,StartedUtc,BackupRoot,Status)
VALUES(@RunId,@Database,@BackupType,SYSUTCDATETIME(),@BackupRoot,'RUNNING');
SET @AuditId = SCOPE_IDENTITY();
BEGIN TRY
IF @Database COLLATE Latin1_General_100_BIN2 LIKE N'%[^A-Za-z0-9_-]%'
OR UPPER(@Database) IN
(N'CON',N'PRN',N'AUX',N'NUL',N'COM1',N'COM2',N'COM3',N'COM4',
N'COM5',N'COM6',N'COM7',N'COM8',N'COM9',N'LPT1',N'LPT2',N'LPT3',
N'LPT4',N'LPT5',N'LPT6',N'LPT7',N'LPT8',N'LPT9')
THROW 51007, 'Whitelist name is not safe for this folder layout.', 1;
SELECT @DatabaseId = NULL, @Recovery = NULL, @State = NULL, @SourceId = NULL;
SELECT @DatabaseId=database_id, @Recovery=recovery_model_desc,
@State=state_desc, @SourceId=source_database_id
FROM sys.databases WHERE name = @Database;
IF @DatabaseId IS NULL THROW 51008, 'Whitelist database does not exist.', 1;
IF @DatabaseId <= 4 THROW 51009, 'System databases are excluded.', 1;
IF @State <> N'ONLINE' OR @SourceId IS NOT NULL
THROW 51010, 'Database must be online and not a snapshot.', 1;
IF @BackupType = 'LOG' AND @Recovery = N'SIMPLE'
THROW 51011, 'SIMPLE recovery does not support log backups.', 1;
IF EXISTS (SELECT 1 FROM sys.databases
WHERE database_id=@DatabaseId AND group_database_id IS NOT NULL)
THROW 51012, 'Availability Group needs a replica-aware backup policy.', 1;
-- FULL/DIFF serialize; LOG may run concurrently with a data backup.
SET @LockResource = N'MeetAJ.Backup.' + @Database +
CASE WHEN @BackupType='LOG' THEN N'.LOG' ELSE N'.DATA' END;
EXEC @LockResult = sys.sp_getapplock
@Resource=@LockResource, @LockMode='Exclusive',
@LockOwner='Session', @LockTimeout=60000;
IF @LockResult < 0 THROW 51013, 'Could not acquire the database backup lock.', 1;
SET @Locked = 1;
SET @Stamp = CONVERT(char(8),GETUTCDATE(),112) + '_' +
REPLACE(CONVERT(char(12),GETUTCDATE(),114),':','');
IF LEN(@BackupRoot)+LEN(@Database)*2+LEN(@BackupType)+65 > 259
THROW 51014, 'Backup path would exceed 259 characters.', 1;
SET @File = @BackupRoot + N'\' + @Database + N'\' + @BackupType +
N'\' + @Database + N'_' + @BackupType + N'_' + @Stamp + N'_' +
CONVERT(nvarchar(36),NEWID()) +
CASE WHEN @BackupType='LOG' THEN N'.trn' ELSE N'.bak' END;
UPDATE dbo.BackupAudit SET BackupFile=@File WHERE AuditId=@AuditId;
SET @Sql = CASE WHEN @BackupType='LOG' THEN N'BACKUP LOG ' ELSE N'BACKUP DATABASE ' END
+ QUOTENAME(@Database) + N' TO DISK=@Path WITH '
+ CASE WHEN @BackupType='DIFF' THEN N'DIFFERENTIAL, ' ELSE N'' END
+ N'COMPRESSION, CHECKSUM, STOP_ON_ERROR, STATS=10'
+ CASE WHEN @EncryptionCertificate IS NULL THEN N'' ELSE
N', ENCRYPTION (ALGORITHM=AES_256, SERVER CERTIFICATE='
+ QUOTENAME(@EncryptionCertificate) + N')' END + N';';
EXEC sys.sp_executesql @Sql, N'@Path nvarchar(260)', @Path=@File;
IF @Verify=1
BEGIN
SET @Sql = N'RESTORE VERIFYONLY FROM DISK=@Path WITH CHECKSUM, STOP_ON_ERROR;';
EXEC sys.sp_executesql @Sql, N'@Path nvarchar(260)', @Path=@File;
END;
UPDATE dbo.BackupAudit
SET Status='SUCCEEDED',FinishedUtc=SYSUTCDATETIME(),Verified=@Verify
WHERE AuditId=@AuditId;
END TRY
BEGIN CATCH
SET @Failures += 1;
UPDATE dbo.BackupAudit SET Status='FAILED',FinishedUtc=SYSUTCDATETIME(),
ErrorNumber=ERROR_NUMBER(),ErrorMessage=ERROR_MESSAGE()
WHERE AuditId=@AuditId;
END CATCH;
IF @Locked=1
EXEC sys.sp_releaseapplock @Resource=@LockResource, @LockOwner='Session';
FETCH NEXT FROM databases INTO @Database;
END;
CLOSE databases;
DEALLOCATE databases;
IF @Failures > 0
THROW 51015, 'One or more backups failed. Check DBAOperations.dbo.BackupAudit.', 1;
END;
GO
Review the Database List Before Execution
Compare names with sys.databases first. Disable STLA if it is absent rather than hiding a typo by selecting every database. Disabling a member does not automatically terminate retention. Remaining archive/deletion needs a separate decision.
SELECT w.DatabaseName,w.Enabled,d.state_desc,d.recovery_model_desc
FROM DBAOperations.dbo.BackupWhitelist w
LEFT JOIN sys.databases d ON d.name=w.DatabaseName;
-- Example only: disable a database that is intentionally absent.
UPDATE DBAOperations.dbo.BackupWhitelist SET Enabled=0 WHERE DatabaseName=N'STLA';
-- Add an approved database explicitly:
-- INSERT DBAOperations.dbo.BackupWhitelist(DatabaseName) VALUES(N'NewShop');Create Backup Folders with PowerShell
Save this as C:/DBA/Prepare-SqlBackupFolders.ps1. It reads active whitelist members and creates FULL, DIFF and LOG directories. Invalid Windows names and reparse-point paths such as junctions are rejected.
# Windows PowerShell 5.1. Provision folders without enabling xp_cmdshell.
param([string]$ServerInstance = 'localhost', [string]$BackupRoot = 'D:\SQLBackup')
$ErrorActionPreference = 'Stop'
$rootPath = [IO.Path]::GetFullPath($BackupRoot).TrimEnd('\')
if ($rootPath -notmatch '^[A-Za-z]:\\.+' -or $rootPath.Length -gt 100) {
throw 'Use a local absolute backup folder, not a drive root.'
}
function Assert-NoReparsePath([string]$Path) {
$currentPath = [IO.Path]::GetFullPath($Path)
while ($currentPath) {
if (Test-Path -LiteralPath $currentPath) {
$item = Get-Item -LiteralPath $currentPath -Force
if ($item.Attributes -band [IO.FileAttributes]::ReparsePoint) {
throw "Reparse points are not permitted: $currentPath"
}
}
$parent = [IO.Directory]::GetParent($currentPath)
if ($null -eq $parent) { break }
$currentPath = $parent.FullName
}
}
Assert-NoReparsePath $rootPath
$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$builder.DataSource = $ServerInstance
$builder.InitialCatalog = 'DBAOperations'
$builder.IntegratedSecurity = $true
$builder.Encrypt = $true
$builder.TrustServerCertificate = $false
$connection = New-Object System.Data.SqlClient.SqlConnection $builder.ConnectionString
try {
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = 'SELECT DatabaseName FROM dbo.BackupWhitelist WHERE Enabled=1;'
$reader = $command.ExecuteReader()
$databases = @()
while ($reader.Read()) { $databases += $reader.GetString(0) }
$reader.Close()
if ($databases.Count -eq 0) { throw 'Whitelist is empty.' }
foreach ($database in $databases) {
if ($database -notmatch '^[A-Za-z0-9_-]+$' -or
$database -match '^(CON|PRN|AUX|NUL|COM[1-9]|LPT[1-9])$' -or
$database -in @('master','model','msdb','tempdb')) {
throw "Unsupported database folder name: $database"
}
foreach ($kind in @('FULL','DIFF','LOG')) {
$directory = Join-Path (Join-Path $rootPath $database) $kind
Assert-NoReparsePath $directory
[IO.Directory]::CreateDirectory($directory) | Out-Null
}
}
} finally { $connection.Dispose() }
Create a Windows credential and SQLBackupFiles proxy under Agent → Proxies → Operating System (CmdExec). The proxy needs DBAOperations connectivity, SELECT on BackupWhitelist/BackupAudit and appropriate filesystem permissions. Enter the credential password in the secure credential interface, never the article script or job. Authorize the job owner to use this proxy.
powershell.exe -NoProfile -NonInteractive -File "C:\DBA\Prepare-SqlBackupFolders.ps1" -ServerInstance "localhost" -BackupRoot "D:\SQLBackup"Provision folders before the first full. PowerShell does not grant the engine’s NTFS rights. Provision them separately. Calling the procedure without prepared folders can cause path errors.
Build Agent jobs and test manually
- In SSMS, open SQL Server Agent → Jobs → New Job. Use a clear name such as MeetAJ - SQL FULL and a trusted owner.
- Create the first step as Operating system (CmdExec), Run as SQLBackupFiles, running Prepare-SqlBackupFolders.ps1. Success advances to the next step. Failure quits with failure.
- Create the second step as Transact-SQL in DBAOperations. Use EXEC dbo.usp_BackupWhitelist @BackupType='FULL'. The other jobs use DIFF and LOG. Success quits successfully. Failure quits with failure.
- Schedule FULL daily at 01:00. Schedule DIFF every six hours from 07:00 through 19:00: exactly 07:00, 13:00 and 19:00. Schedule LOG every fifteen minutes from 00:00 through 23:59:59.
- Keep jobs disabled initially. Run preparation/FULL manually, then test DIFF/LOG. Use Start Job at Step. A disabled schedule does not prevent manual starts.
- Inspect each step in View History and compare BackupAudit, files and msdb.backupset. Overall job status is insufficient.
The equivalent script creates three disabled jobs/schedules without overwriting existing jobs. Adjust ServerInstance in the preparation step for named instances/certificate names. Configure Database Mail before replacing @Operator with an actual operator.
-- Provision SQLBackupFiles CmdExec proxy first; see article prerequisites.
-- Save 02-prepare-folders.ps1 as C:\DBA\Prepare-SqlBackupFolders.ps1.
USE msdb;
GO
DECLARE @Proxy sysname=N'SQLBackupFiles', @Operator sysname=NULL,
@Owner sysname=SUSER_SNAME(), @Notify int,
@Type varchar(4), @Job sysname, @Schedule sysname, @Command nvarchar(max),
@Start int, @End int, @SubType int, @SubInterval int;
IF NOT EXISTS (SELECT 1 FROM dbo.sysproxies WHERE name=@Proxy AND enabled=1)
THROW 51100, 'Create the enabled SQLBackupFiles CmdExec proxy first.', 1;
IF @Operator IS NOT NULL AND NOT EXISTS
(SELECT 1 FROM dbo.sysoperators WHERE name=@Operator AND enabled=1)
THROW 51101, 'Configured notification operator does not exist.', 1;
IF EXISTS (SELECT 1 FROM dbo.sysjobs
WHERE name IN (N'MeetAJ - SQL FULL',N'MeetAJ - SQL DIFF',N'MeetAJ - SQL LOG'))
THROW 51102, 'Jobs already exist. Review/update them; do not overwrite.', 1;
SET @Notify=CASE WHEN @Operator IS NULL THEN 0 ELSE 2 END;
BEGIN TRY
BEGIN TRANSACTION;
DECLARE types CURSOR LOCAL FAST_FORWARD FOR
SELECT Kind FROM (VALUES ('FULL'),('DIFF'),('LOG')) t(Kind);
OPEN types;
FETCH NEXT FROM types INTO @Type;
WHILE @@FETCH_STATUS=0
BEGIN
SET @Job=N'MeetAJ - SQL '+@Type;
SET @Schedule=@Job+N' schedule';
-- Jobs stay disabled until a manual FULL/DIFF/LOG test succeeds.
EXEC dbo.sp_add_job @job_name=@Job, @enabled=0,
@owner_login_name=@Owner, @notify_level_email=@Notify,
@notify_email_operator_name=@Operator;
EXEC dbo.sp_add_jobstep @job_name=@Job, @step_id=1,
@step_name=N'Prepare whitelist folders', @subsystem=N'CmdExec',
@proxy_name=@Proxy,
@command=N'powershell.exe -NoProfile -NonInteractive -File "C:\DBA\Prepare-SqlBackupFolders.ps1" -ServerInstance "localhost" -BackupRoot "D:\SQLBackup"',
@on_success_action=3, @on_fail_action=2, @retry_attempts=0;
SET @Command=N'EXEC dbo.usp_BackupWhitelist @BackupType='''+@Type+
N''', @BackupRoot=N''D:\SQLBackup'', @Verify=1;';
EXEC dbo.sp_add_jobstep @job_name=@Job, @step_id=2,
@step_name=N'Backup and verify', @subsystem=N'TSQL',
@database_name=N'DBAOperations', @command=@Command,
@on_success_action=1, @on_fail_action=2, @retry_attempts=0;
SET @Start=CASE @Type WHEN 'FULL' THEN 010000 WHEN 'DIFF' THEN 070000 ELSE 000000 END;
SET @End=CASE WHEN @Type='DIFF' THEN 190000 ELSE 235959 END;
SET @SubType=CASE @Type WHEN 'FULL' THEN 1 WHEN 'DIFF' THEN 8 ELSE 4 END;
SET @SubInterval=CASE @Type WHEN 'FULL' THEN 1 WHEN 'DIFF' THEN 6 ELSE 15 END;
EXEC dbo.sp_add_jobschedule @job_name=@Job, @name=@Schedule,
@enabled=1, @freq_type=4, @freq_interval=1,
@freq_subday_type=@SubType, @freq_subday_interval=@SubInterval,
@active_start_time=@Start, @active_end_time=@End;
EXEC dbo.sp_add_jobserver @job_name=@Job, @server_name=N'(LOCAL)';
FETCH NEXT FROM types INTO @Type;
END;
CLOSE types;
DEALLOCATE types;
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0 ROLLBACK;
THROW;
END CATCH;
GO
-- Execute individually and wait for each job to finish before starting the next.
EXEC msdb.dbo.sp_start_job @job_name=N'MeetAJ - SQL FULL';
-- After successful FULL:
EXEC msdb.dbo.sp_start_job @job_name=N'MeetAJ - SQL DIFF';
-- After recovery models and log chain have been checked:
EXEC msdb.dbo.sp_start_job @job_name=N'MeetAJ - SQL LOG';
GO
-- Enable only after validating history, files and restore tests.
EXEC msdb.dbo.sp_update_job @job_name=N'MeetAJ - SQL FULL',@enabled=1;
EXEC msdb.dbo.sp_update_job @job_name=N'MeetAJ - SQL DIFF',@enabled=1;
EXEC msdb.dbo.sp_update_job @job_name=N'MeetAJ - SQL LOG',@enabled=1;
GOJob starts are asynchronous. Starting them consecutively does not ensure FULL finishes before DIFF. Run each separately and observe completion in History. Interrupted sessions/Agent outages can leave RUNNING audit records. Do not read these as success.
Cleanup while retaining the recovery-chain base
Before writing cleanup rules, identify the chain needed for the first recovery point inside the retention window. Deleting every file older than thirty days can also remove the base full backup and logs leading up to that boundary. Those files may be slightly older but still necessary to recover part of the promised thirty-day window. Retain them to keep that window usable.
Cleanup protects each database’s latest verified full before the retention boundary and all full/differential/log files since its start. It deletes only older files with valid extensions, whitelist-conforming paths and successful audit history. Extra hours or days may remain after failed full backups. This preserves recovery. Without a trustworthy base, nothing is deleted for that database.
Save as C:/DBA/Cleanup-SqlBackups.ps1. Preview is the default. Unknown, incomplete or FAILED files are not automatically removed. Investigate/manage them separately. This job does not purge audit or msdb history.
# Windows PowerShell 5.1. Default is preview; -Delete authorizes actual cleanup.
# Retain the verified FULL before the 30-day boundary, plus ALL backups since it.
[CmdletBinding(SupportsShouldProcess=$true)]
param(
[string]$ServerInstance = 'localhost',
[string]$BackupRoot = 'D:\SQLBackup',
[ValidateRange(30,3650)][int]$RetentionDays = 30,
[switch]$Delete
)
$ErrorActionPreference = 'Stop'
$rootPath = [IO.Path]::GetFullPath($BackupRoot).TrimEnd('\')
if ($rootPath -notmatch '^[A-Za-z]:\\.+' -or $rootPath.Length -gt 100) {
throw 'Use a dedicated local backup root, not a drive root.'
}
function Assert-NoReparsePath([string]$Path) {
$currentPath = [IO.Path]::GetFullPath($Path)
while ($currentPath) {
if (Test-Path -LiteralPath $currentPath) {
$item = Get-Item -LiteralPath $currentPath -Force
if ($item.Attributes -band [IO.FileAttributes]::ReparsePoint) {
throw "Reparse point blocked: $currentPath"
}
}
$parent = [IO.Directory]::GetParent($currentPath)
if ($null -eq $parent) { break }
$currentPath = $parent.FullName
}
}
Assert-NoReparsePath $rootPath
$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$builder.DataSource = $ServerInstance
$builder.InitialCatalog = 'DBAOperations'
$builder.IntegratedSecurity = $true
$builder.Encrypt = $true
$builder.TrustServerCertificate = $false
$connection = New-Object System.Data.SqlClient.SqlConnection $builder.ConnectionString
$candidates = New-Object System.Data.DataTable
try {
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = @'
DECLARE @Cutoff datetime2(3)=DATEADD(day,-@Days,SYSUTCDATETIME());
SELECT a.DatabaseName,a.BackupType,a.BackupFile,f.BackupFile AS AnchorFile
FROM dbo.BackupWhitelist w
CROSS APPLY (
SELECT TOP (1) b.BackupFile,b.StartedUtc
FROM dbo.BackupAudit b
WHERE b.DatabaseName=w.DatabaseName AND b.BackupRoot=@Root
AND b.BackupType='FULL' AND b.Status='SUCCEEDED' AND b.Verified=1
AND b.FinishedUtc<=@Cutoff
ORDER BY b.FinishedUtc DESC,b.AuditId DESC
) f
JOIN dbo.BackupAudit a ON a.DatabaseName=w.DatabaseName
WHERE w.Enabled=1 AND a.BackupRoot=@Root AND a.Status='SUCCEEDED'
AND a.BackupType IN ('FULL','DIFF','LOG')
AND a.FinishedUtc<f.StartedUtc AND a.FinishedUtc<@Cutoff
AND a.BackupFile IS NOT NULL
ORDER BY a.DatabaseName,a.FinishedUtc;
'@
$command.Parameters.Add('@Days',[Data.SqlDbType]::Int).Value=$RetentionDays
$command.Parameters.Add('@Root',[Data.SqlDbType]::NVarChar,200).Value=$rootPath
$reader=$command.ExecuteReader()
$candidates.Load($reader)
$reader.Close()
} finally { $connection.Dispose() }
$cutoffUtc=[DateTime]::UtcNow.AddDays(-$RetentionDays)
foreach ($row in $candidates.Rows) {
$database=[string]$row.DatabaseName
$kind=[string]$row.BackupType
if ($database -notmatch '^[A-Za-z0-9_-]+$' -or
$database -in @('master','model','msdb','tempdb')) {
throw 'Invalid database name in cleanup manifest.'
}
$expectedDir=Join-Path (Join-Path $rootPath $database) $kind
$filePath=[IO.Path]::GetFullPath([string]$row.BackupFile)
$anchorPath=[IO.Path]::GetFullPath([string]$row.AnchorFile)
$anchorDir=Join-Path (Join-Path $rootPath $database) 'FULL'
if (-not [IO.Path]::GetDirectoryName($filePath).Equals($expectedDir,[StringComparison]::OrdinalIgnoreCase) -or
-not [IO.Path]::GetDirectoryName($anchorPath).Equals($anchorDir,[StringComparison]::OrdinalIgnoreCase)) {
throw "Manifest path is outside the expected backup directory: $filePath"
}
Assert-NoReparsePath $filePath
Assert-NoReparsePath $anchorPath
if (-not (Test-Path -LiteralPath $anchorPath -PathType Leaf)) {
throw "Protected FULL backup is missing; cleanup stopped: $anchorPath"
}
$extension=if ($kind -eq 'LOG') { '.trn' } else { '.bak' }
if ([IO.Path]::GetExtension($filePath) -ne $extension -or
-not [IO.Path]::GetFileName($filePath).StartsWith($database+'_'+$kind+'_',[StringComparison]::OrdinalIgnoreCase)) {
throw "Unexpected backup filename: $filePath"
}
if (-not (Test-Path -LiteralPath $filePath -PathType Leaf)) { continue }
$file=Get-Item -LiteralPath $filePath -Force
if ($file.LastWriteTimeUtc -ge $cutoffUtc) { continue }
if (-not $Delete) { Write-Output "PREVIEW: $filePath"; continue }
if ($PSCmdlet.ShouldProcess($filePath,'Delete expired SQL backup')) {
Remove-Item -LiteralPath $filePath -ErrorAction Stop
Write-Output "DELETED: $filePath"
}
}
# Preview candidates; no deletion.
powershell.exe -NoProfile -NonInteractive -File "C:\DBA\Cleanup-SqlBackups.ps1" -RetentionDays 30
# After reviewing candidates and the protected FULL:
powershell.exe -NoProfile -NonInteractive -File "C:\DBA\Cleanup-SqlBackups.ps1" -RetentionDays 30 -Delete -WhatIf
# Actual deletion is performed by the reviewed Cleanup Job.Run cleanup only against this instance’s dedicated root. Other identities must not swap paths into junctions or remove the protected base concurrently. Filesystem presence is checked, but base integrity/LSN continuity still requires chain checks and restore tests.
-- Save 04-cleanup.ps1 as C:\DBA\Cleanup-SqlBackups.ps1; preview before enabling.
USE msdb;
GO
IF EXISTS (SELECT 1 FROM dbo.sysjobs WHERE name=N'MeetAJ - SQL Cleanup')
THROW 51200, 'Cleanup job exists; review it instead of overwriting.', 1;
IF NOT EXISTS (SELECT 1 FROM dbo.sysproxies WHERE name=N'SQLBackupFiles' AND enabled=1)
THROW 51201, 'SQLBackupFiles proxy is missing.', 1;
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @Owner sysname=SUSER_SNAME();
EXEC dbo.sp_add_job @job_name=N'MeetAJ - SQL Cleanup',@enabled=0,@owner_login_name=@Owner;
EXEC dbo.sp_add_jobstep @job_name=N'MeetAJ - SQL Cleanup',
@step_name=N'Clean expired backups',@subsystem=N'CmdExec',@proxy_name=N'SQLBackupFiles',
@command=N'powershell.exe -NoProfile -NonInteractive -File "C:\DBA\Cleanup-SqlBackups.ps1" -ServerInstance "localhost" -BackupRoot "D:\SQLBackup" -RetentionDays 30 -Delete',
@on_success_action=1,@on_fail_action=2,@retry_attempts=0;
EXEC dbo.sp_add_jobschedule @job_name=N'MeetAJ - SQL Cleanup',
@name=N'MeetAJ - cleanup daily 05:00',@freq_type=4,@freq_interval=1,
@active_start_time=050000;
EXEC dbo.sp_add_jobserver @job_name=N'MeetAJ - SQL Cleanup',@server_name=N'(LOCAL)';
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0 ROLLBACK;
THROW;
END CATCH;
GO
After reviewing preview and policy, enable cleanup in SSMS. 05:00 is an example: change it if FULL/off-site transfer is incomplete and add transfer acceptance to the runbook. Maintenance Cleanup Task deletes by age/extension without preserving dependencies automatically. This scenario uses audit-based cleanup.
Encryption and certificate/private-key recovery
Backups contain readable organizational data and need protection at rest/in transit. @EncryptionCertificate adds AES_256 using a master certificate. Provision the database master key/certificate under organizational key policy, then export the certificate/private key to protected storage separate from backup files. Without required keys, replacement-server restore fails.
-- After provisioning and securely exporting the certificate/private key:
EXEC DBAOperations.dbo.usp_BackupWhitelist
@BackupType='FULL',
@EncryptionCertificate=N'SqlBackupEncryptionCert',
@Verify=1;
-- Add the SAME encryption parameter to the DIFF and LOG job commands too.Encryption defaults off to simplify testing. Before production, provision a certificate and add the parameter to all three jobs. TDE, backup encryption and storage encryption serve different purposes. Periodically test key access/import and do not leave keys only beside disposable backups.
History, VERIFYONLY and an actual restore
Read three sources together when checking results. Agent History describes step execution, BackupAudit records each database outcome, and msdb.backupset holds SQL Server's backup history. The script below displays missing backups as NULL. It keeps databases without backups in the report so those gaps remain visible.
-- Run on the source instance. msdb timestamps use server local time;
-- BackupAudit timestamps use UTC.
USE msdb;
GO
SELECT TOP (100) b.database_name,
CASE b.type WHEN 'D' THEN 'FULL' WHEN 'I' THEN 'DIFF' WHEN 'L' THEN 'LOG' END AS BackupType,
b.backup_start_date,b.backup_finish_date,b.is_copy_only,b.has_backup_checksums,
CAST(b.backup_size/1048576.0 AS decimal(18,2)) AS OriginalMB,
CAST(b.compressed_backup_size/1048576.0 AS decimal(18,2)) AS CompressedMB,
m.physical_device_name,b.first_lsn,b.last_lsn,b.database_backup_lsn
FROM dbo.backupset b
JOIN dbo.backupmediafamily m ON m.media_set_id=b.media_set_id
JOIN DBAOperations.dbo.BackupWhitelist w ON w.DatabaseName=b.database_name AND w.Enabled=1
WHERE b.type IN ('D','I','L')
ORDER BY b.backup_finish_date DESC;
GO
-- OUTER APPLY keeps missing backups visible (NULL), instead of hiding the DB.
SELECT w.DatabaseName,d.recovery_model_desc,
f.backup_finish_date AS LastFull,
i.backup_finish_date AS LastDifferential,
l.backup_finish_date AS LastLog,
d.log_reuse_wait_desc
FROM DBAOperations.dbo.BackupWhitelist w
LEFT JOIN sys.databases d ON d.name=w.DatabaseName
OUTER APPLY (SELECT TOP (1) backup_finish_date FROM dbo.backupset
WHERE database_name=w.DatabaseName AND type='D' AND is_copy_only=0
ORDER BY backup_finish_date DESC) f
OUTER APPLY (SELECT TOP (1) backup_finish_date FROM dbo.backupset
WHERE database_name=w.DatabaseName AND type='I'
ORDER BY backup_finish_date DESC) i
OUTER APPLY (SELECT TOP (1) backup_finish_date FROM dbo.backupset
WHERE database_name=w.DatabaseName AND type='L' AND is_copy_only=0
ORDER BY backup_finish_date DESC) l
WHERE w.Enabled=1;
GO
SELECT TOP (100) * FROM DBAOperations.dbo.BackupAudit ORDER BY AuditId DESC;
GO
-- Verify the latest successful Jira FULL using its real generated file name.
DECLARE @File nvarchar(260);
SELECT TOP (1) @File=BackupFile FROM DBAOperations.dbo.BackupAudit
WHERE DatabaseName=N'DB-Jira' AND BackupType='FULL' AND Status='SUCCEEDED'
ORDER BY FinishedUtc DESC;
IF @File IS NULL THROW 51300, 'No successful Jira FULL backup found.', 1;
RESTORE VERIFYONLY FROM DISK=@File WITH CHECKSUM,STOP_ON_ERROR;
GO
-- Example freshness thresholds; integrate these rows with your monitoring.
-- A successful Agent execution is not a substitute for per-database checks.
SELECT w.DatabaseName,t.Kind,latest.FinishedUtc,
CASE WHEN latest.FinishedUtc IS NULL THEN 'MISSING' ELSE 'STALE' END AS AlertReason
FROM DBAOperations.dbo.BackupWhitelist w
-- DIFF has a 12-hour overnight gap (19:00 to 07:00), so 7h would false-alert.
-- Use 13h here, or a schedule-aware check for stricter daytime detection.
CROSS JOIN (VALUES ('FULL',1560),('DIFF',780),('LOG',20)) t(Kind,MaxMinutes)
OUTER APPLY (
SELECT TOP (1) a.FinishedUtc FROM DBAOperations.dbo.BackupAudit a
WHERE a.DatabaseName=w.DatabaseName AND a.BackupType=t.Kind AND a.Status='SUCCEEDED'
ORDER BY a.FinishedUtc DESC
) latest
WHERE w.Enabled=1 AND (latest.FinishedUtc IS NULL OR
latest.FinishedUtc<DATEADD(minute,-t.MaxMinutes,SYSUTCDATETIME()));
GO
CHECKSUM improves error detection during page reads/backup writes. VERIFYONLY WITH CHECKSUM checks readability and existing checksums, but does not restore the database or establish logical integrity, application compatibility or RTO.
Restore Testing on an Isolated Instance
- Prepare an isolated instance with adequate storage. Use destination names/paths distinct from production.
- Inspect backup type, DatabaseBackupLSN and FirstLSN/LastLSN with HEADERONLY. Read logical names using FILELISTONLY.
- Restore FULL using MOVE to test paths and NORECOVERY. Apply the latest compatible DIFF and required logs in order with NORECOVERY.
- For point-in-time recovery, apply STOPAT in the appropriate log step, finish with RECOVERY and run DBCC CHECKDB.
- Start test Jira/Confluence with matching Home/attachments. Record total recovery time and last-data difference against RPO/RTO.
Do not copy REPLACE or production paths as a generic restore command. Select actual generated backups and their logical filenames. Last month’s successful test does not prove today’s chain is recoverable.
Example: restore one full on an isolated instance
This complete standalone-FULL restore targets an isolated instance. Replace filenames/logical names using actual FILELISTONLY output. D:/RestoreLab must already exist with SQL service permissions.
RESTORE FILELISTONLY FROM DISK=N'D:\RestoreLab\DB-Jira_FULL.bak';Run this batch only after reviewing output and confirming no same-name database exists. Add MOVE for every additional file when the backup contains more than one data/log file.
IF DB_ID(N'DB_Jira_RestoreTest') IS NOT NULL
THROW 52000, 'Restore target already exists; choose a fresh isolated name.', 1;
RESTORE DATABASE DB_Jira_RestoreTest
FROM DISK=N'D:\RestoreLab\DB-Jira_FULL.bak'
WITH MOVE N'DB-Jira' TO N'D:\RestoreLab\DB_Jira_RestoreTest.mdf',
MOVE N'DB-Jira_log' TO N'D:\RestoreLab\DB_Jira_RestoreTest_log.ldf',
CHECKSUM, RECOVERY, STATS=10;
DBCC CHECKDB (N'DB_Jira_RestoreTest') WITH NO_INFOMSGS, ALL_ERRORMSGS;This is FULL-only, not PITR. For PITR, apply compatible FULL/DIFF with NORECOVERY, then every contiguous LOG through the target STOPAT and RECOVERY as explained in the main implementation. Accept only clean CHECKDB, approved application queries and measured RTO. Import required encryption/TDE certificates and keys securely before restore.
Diagnose job failures from audit to storage
When a job fails, start by running the query below on the source instance. It reads the current recovery model and backup history. Compare that result with the audit record and job error to determine whether the problem concerns model selection, backup execution or the recorded backup state.
SELECT name,state_desc,recovery_model_desc,log_reuse_wait_desc
FROM sys.databases
WHERE database_id > 4;
SELECT TOP (20) database_name,type,backup_finish_date,first_lsn,last_lsn,database_backup_lsn
FROM msdb.dbo.backupset
ORDER BY backup_finish_date DESC;| Symptom | Diagnosis | Correction |
|---|---|---|
| No log backup | Recovery model, initial data backup after SIMPLE, LOG job and audit ErrorMessage | Select FULL for the intended database, take the initial data backup and manually test LOG. Inspect Agent History. |
| Error 4214 | The backup chain has not been established | Prepare folders and take an ordinary full. ALTER DATABASE alone is insufficient. |
| Error 3201 / OS error 5 | Engine service access to the root/database directory | Correct NTFS/share rights for the actual identity. The SSMS user does not write backup files. |
| OS error 3 | Root/FULL/DIFF/LOG existence and preparation-step result | Run Prepare as the same proxy. Inspect the SQL server’s paths, not the DBA workstation. |
| Job never ran | Agent state, job/schedule enabled flags, server time and starting step | Correct service/schedule configuration and detect missing executions with freshness monitoring. |
| Low disk / error 112 | Full size, differential/log growth, off-site transfer and cleanup | Increase capacity or revise policy. Do not delete required logs to free space. |
| FULL succeeds but DIFF fails | Ordinary full base, model changes and full backups outside this pipeline | Take a new ordinary full and review the chain. COPY_ONLY does not create a differential base. |
| CHECKSUM or VERIFYONLY failure | Exact error, IO/storage and incomplete/modified files | Quarantine the file, locate an alternative healthy chain and investigate database/storage health. Do not enable CONTINUE_AFTER_ERROR just to make the job green. |
| Growing transaction log | log_reuse_wait_desc, log backups, long transactions, replicas or replication | Resolve the retention cause. Daily shrink/SIMPLE switching is not a substitute. |
| PowerShell cannot connect to SQL | Proxy login, table rights, FQDN and TLS certificate/trust chain | Correct identity/certificates. Do not embed SQL passwords in jobs. |
| Job failed but some files exist | Per-database audit | The procedure attempts every member before reporting failure. Investigate the failed database/step. Failed-verification files may remain on disk. |
Freshness, alerts and independent backup copies
- Independent/off-site storage: retain local copies for speed and a second independent location. Keep at least one offline/immutable copy with separate identity so a compromised account cannot erase every version.
- Restore drills: regularly test restore and point-in-time recovery for critical services. Record results, duration and corrective-action ownership.
- Monitoring: check failures, missing/late backups per whitelist member, stale RUNNING records, free space, duration and off-site transfer. The validation example uses a twenty-minute log threshold. Align it to your RPO.
- Alert delivery: configure approved Database Mail, select it in Agent Properties → Alert System and create an operator. Enable failure email and test a controlled failure. Cleanup needs separate notification. Independent monitoring must detect Agent itself stopping.
- One chain owner: an ordinary full outside this pipeline can change the differential base. Use COPY_ONLY for ad hoc copies and coordinate other backup tools’ deletion/restore policies.
- System databases and keys: this job excludes master/model/msdb/tempdb. Protect master/model/msdb, logins, jobs, certificates and runbooks separately. Management audit also needs its own retention/backup.
- Capacity/performance: measure full size, differential growth and log generation. VERIFYONLY reads the entire file and adds IO. It may run in a separate validation job for large databases, provided Verified state and cleanup remain coordinated.
- Change control: record whitelist additions, model/root changes and shorter retention. New databases have no coverage until explicitly added.
Before enabling schedules, verify whitelist database existence, engine/proxy rights, connection certificates, disk space, off-host transfer and a test restore. Report measured RPO/RTO to the service owner.
Conclusion
A reliable implementation of Automated SQL Server Backups with Agent and Restore Tests is more than a successful first run. Keep the configuration documented, observable, recoverable, and aligned with the team's change-management process.
SQL Server Automated Backup FAQ
Why is log backup unavailable in SIMPLE?
SIMPLE reuses inactive transaction log after checkpoint when no blocker exists and does not maintain a backup log chain. LOG needs FULL/BULK_LOGGED and an initial data backup.
What follows a SIMPLE-to-FULL change?
Take a valid full/differential to establish the log chain. This scenario selects a fresh ordinary full, then schedules LOG. The same requirement does not apply identically to every model transition.
Does a differential store changes since the previous differential?
No. It covers changes since its ordinary full base. Restore the compatible full, latest appropriate differential and required logs in sequence.
Does VERIFYONLY replace a restore test?
No. It checks readability and existing checksums without restoring the database. CHECKDB, application validation and measured RTO remain necessary.
Why are some files older than thirty days retained?
The full before the retention boundary and its subsequent backups may be required for the entire window. Cleanup preserves these dependencies rather than deleting the base/required logs solely by age.
Which identities need backup-folder access?
The engine service writes/reads backups. A CmdExec proxy creates folders and performs cleanup here. Both require appropriate rights on the dedicated paths.
Does this job back up master, model, msdb or tempdb?
No. System databases are rejected even if whitelisted. Use separate jobs for master/model/msdb. Tempdb cannot be backed up.
Does Agent alone provide complete Jira/Confluence backups?
No. Back up Home, Shared Home, attachments and configuration with installed-version-compatible procedures. Independent off-site copies and application startup after restore are also required.
Microsoft backup and restore references
- Microsoft: Backup overview
- Microsoft: BACKUP Transact-SQL
- Microsoft: Backup CHECKSUM and service permissions
- Microsoft: Restore and Recovery
- Microsoft: restore overview
- Microsoft: VERIFYONLY
- Microsoft: backup encryption
