Design Automated Backups, RPO/RTO, Retention, Offsite Copies, and Disaster-Recovery Drills

Learning outcomes

ServiceHub’s final recovery task is operational: turn individual backup statements into a system that meets business recovery objectives even when a job fails, a log backup is missing, the primary host is gone, or the person performing recovery has not written the original scripts. The unit of success is not “backup completed.” It is a measured recovery drill.

1. Start from RPO/RTO, then choose the backup schedule

Suppose ServiceHub accepts at most 10 minutes of committed-work loss (RPO = 10 minutes) and must restore the core database within 45 minutes (RTO = 45 minutes). Under FULL recovery, a 10-minute log-backup cadence is a starting requirement, not the whole solution. You still need full/differential frequency that keeps restore work inside 45 minutes, backup throughput that completes before the next schedule window, and enough offsite/immutable retention to survive the threat model.

2. Generate an auditable backup-health snapshot

Backup automation should write normal SQL Server history, but monitoring must interpret it. The following query shows last full/differential/log times and the current recovery model. A missing log timestamp can be legitimate for SIMPLE recovery but is a critical failure for a database whose RPO depends on FULL recovery.

WITH b AS(  SELECT database_name,         MAX(CASE WHEN type='D' AND is_copy_only=0 THEN backup_finish_date END) AS last_full,         MAX(CASE WHEN type='I' THEN backup_finish_date END) AS last_diff,         MAX(CASE WHEN type='L' THEN backup_finish_date END) AS last_log  FROM msdb.dbo.backupset  GROUP BY database_name)SELECT d.name,d.recovery_model_desc,d.state_desc,d.log_reuse_wait_desc,       b.last_full,b.last_diff,b.last_log,       DATEDIFF(minute,b.last_log,SYSDATETIME()) AS minutes_since_log_backupFROM sys.databases AS dLEFT JOIN b ON b.database_name=d.nameWHERE d.database_id>4ORDER BY d.name;GO

Do not alert blindly on the numeric age for every database. Compare it to the declared policy for that database and account for maintenance windows, intentionally offline databases and different recovery models. The monitor needs configuration-as-data, not one universal threshold hidden in a script.

3. Automate the same safe T-SQL whether Agent exists or not

SQL Server Agent is available on Developer/Standard/Enterprise but not Express. The free learning path therefore keeps the backup command itself in T-SQL and treats scheduling as a separate concern. On Developer you can schedule it with Agent; on Express you can invoke sqlcmd from Windows Task Scheduler, systemd timer, cron or another approved scheduler. The scheduler identity must have appropriate SQL permissions and storage access without embedding reusable administrator passwords in the command line.

USE master;GOCREATE OR ALTER PROCEDURE dbo.usp_BackupServiceHubRecoveryLab  @kind varchar(12)ASBEGIN  SET NOCOUNT ON;  DECLARE @root nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultBackupPath'));  IF @root IS NULL THROW 51000,'No default backup path is available.',1;  DECLARE @sep nchar(1)=CASE WHEN CHARINDEX(N'/',@root)>0 THEN N'/' ELSE N'\' END;  IF RIGHT(@root,1) IN (N'/',N'\') SET @sep=N'';  DECLARE @stamp char(15)=CONVERT(char(8),GETDATE(),112)+'_'+REPLACE(CONVERT(char(8),GETDATE(),108),':','');  DECLARE @file nvarchar(4000);  IF @kind='FULL'  BEGIN    SET @file=@root+@sep+N'ServiceHubRecoveryLab_FULL_'+@stamp+N'.bak';    BACKUP DATABASE ServiceHubRecoveryLab TO DISK=@file WITH INIT,CHECKSUM;  END  ELSE IF @kind='DIFF'  BEGIN    SET @file=@root+@sep+N'ServiceHubRecoveryLab_DIFF_'+@stamp+N'.bak';    BACKUP DATABASE ServiceHubRecoveryLab TO DISK=@file WITH DIFFERENTIAL,INIT,CHECKSUM;  END  ELSE IF @kind='LOG'  BEGIN    SET @file=@root+@sep+N'ServiceHubRecoveryLab_LOG_'+@stamp+N'.trn';    BACKUP LOG ServiceHubRecoveryLab TO DISK=@file WITH INIT,CHECKSUM;  END  ELSE THROW 51001,'@kind must be FULL, DIFF, or LOG.',1;  SELECT @file AS backup_file;END;GOEXEC dbo.usp_BackupServiceHubRecoveryLab @kind='LOG';GO

Production automation should add a policy table, retry/notification handling, duration/size telemetry, retention and offsite-copy verification. It should not dynamically delete backups merely because a local directory is “older than N days” without confirming an independent recovery copy exists.

Developer/Standard/Enterprise: SQL Server Agent job step → EXEC master.dbo.usp_BackupServiceHubRecoveryLab 'LOG';Express on Windows: Task Scheduler → current sqlcmd using integrated/service identityExpress on Linux: systemd timer or cron → current sqlcmd using protected credential/integrated mechanismContainer: schedule outside the ephemeral container and persist both data and backup media appropriately

4. A disaster-recovery drill measures actual RPO and RTO

A drill begins by recording the incident/drill start time and the latest committed business marker on the source. It restores to a separate target, records the target’s newest marker, runs integrity/application checks, and measures elapsed time to an accepted service state. The difference between the source’s last committed marker and the restored marker is measured data loss; the wall-clock interval is the observed RTO for that drill.

USE master;GOIF OBJECT_ID(N'dbo.ServiceHubRecoveryDrill',N'U') IS NULLCREATE TABLE dbo.ServiceHubRecoveryDrill(  drill_id bigint IDENTITY PRIMARY KEY,  started_at datetime2(3) NOT NULL,  completed_at datetime2(3) NULL,  source_latest_event_time datetime2(3) NULL,  restored_latest_event_time datetime2(3) NULL,  target_database sysname NOT NULL,  integrity_ok bit NULL,  application_checks_ok bit NULL,  notes nvarchar(1000) NULL);GOINSERT dbo.ServiceHubRecoveryDrill(started_at,source_latest_event_time,target_database,notes)SELECT SYSUTCDATETIME(),MAX(event_time),N'ServiceHubRestoreValidation',N'Chapter 15 local restore drill'FROM ServiceHubRecoveryLab.lab15.RecoveryEvent;GOUPDATE dSET completed_at=SYSUTCDATETIME(),    restored_latest_event_time=(SELECT MAX(event_time) FROM ServiceHubRestoreValidation.lab15.RecoveryEvent),    integrity_ok=1,    application_checks_ok=CASE WHEN EXISTS      (SELECT 1 FROM ServiceHubRestoreValidation.lab15.WorkOrderRecovery) THEN 1 ELSE 0 ENDFROM dbo.ServiceHubRecoveryDrill AS dWHERE drill_id=(SELECT MAX(drill_id) FROM dbo.ServiceHubRecoveryDrill);GOSELECT *,DATEDIFF(second,started_at,completed_at) AS observed_rto_seconds,       DATEDIFF(second,restored_latest_event_time,source_latest_event_time) AS observed_data_loss_secondsFROM dbo.ServiceHubRecoveryDrillORDER BY drill_id DESC;GO

The small lab numbers are not performance benchmarks. In production, record dataset size, storage tier, CPU/memory, compression/encryption, network/object-storage throughput, concurrency and whether the drill restored off-host. Compare observed RPO/RTO against the declared objective and remediate gaps.

5. Build the recovery runbook and close the chapter cleanly

A useful runbook is executable under stress by someone other than its author. Include: current database/recovery model; backup schedule and retention; media locations; encryption/TDE key escrow; tail-log decision tree; ordered restore commands; file MOVE mappings; STOPAT policy; credentials/permissions; DNS/application cutover; integrity/business acceptance; rollback decision; and where recovery evidence is recorded.

USE master;GODROP PROCEDURE IF EXISTS dbo.usp_BackupServiceHubRecoveryLab;DROP TABLE IF EXISTS dbo.ServiceHubRestoreMarker;-- Keep dbo.ServiceHubRecoveryDrill if you want the local drill history; otherwise:DROP TABLE IF EXISTS dbo.ServiceHubRecoveryDrill;GODECLARE @db sysname;DECLARE c CURSOR LOCAL FAST_FORWARD FORSELECT name FROM sys.databasesWHERE name IN(N'ServiceHubRestoreValidation',N'ServiceHubRestoreTarget',N'ServiceHubRestoreSource', N'ServiceHubRecoveryModelLab',N'ServiceHubRecoveryLab');OPEN c; FETCH NEXT FROM c INTO @db;WHILE @@FETCH_STATUS=0BEGIN  EXEC(N'ALTER DATABASE '+QUOTENAME(@db)+N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE '+QUOTENAME(@db)+N';');  FETCH NEXT FROM c INTO @db;ENDCLOSE c; DEALLOCATE c;GO-- The SQL backup files remain in InstanceDefaultBackupPath intentionally.-- Delete those disposable Chapter 15 files through an approved OS/storage-- process only after you have finished the restore exercises and verified names.

6. Production judgment

Production judgment. Backups and HA are complementary. Backups protect against corruption, operator error and historical rollback in ways an Availability Group cannot. HA can reduce service interruption but can also replicate bad writes immediately. Chapter 16 builds that distinction by adding cluster/replica availability on top of—not instead of—the tested recovery system you created here.

Authoritative references

• Backup and restore overview — recovery strategy foundation
• Backup history and header information — backup evidence and validation limits
• SQL Server Agent — Agent automation boundary
• sqlcmd utility — CLI automation path
• Recovery models — RPO/RTO implications