Chapter 25 · Production Capstone: Design, Secure, Tune, Automate, and Recover SQL Server
Implement Backups/PITR, AG/DR, Monitoring, Integrity Checks, Alerts, and Incident Runbooks
Perform a real disposable backup/restore drill, integrity checks, focused monitoring and HA/DR runbook design with edition/topology boundaries.
Learning outcomes
The capstone now has a deployable schema and measured workload. Protection must be equally observable: backups must restore, point-in-time procedures must have the required log chain, integrity checks must complete, monitoring must capture evidence before incidents, alerts must have owners, and HA/DR must be rehearsed at the topology actually purchased. This lesson performs a real same-instance restore into a disposable database so recovery evidence exists even when a learner has only one free local instance.
Create a backup and perform a real disposable restore rather than treating backup completion as recovery proof.
Connect recovery model/log-chain design to RPO and point-in-time restore requirements.
Implement focused Query Store/XEvent/integrity evidence and define actionable alert ownership.
Distinguish single-instance restore evidence from AG/FCI/log-shipping failover evidence.
Write an incident runbook whose detect/contain/recover/validate/escalate steps can be exercised safely.
ServiceHubCapstone. No production passwords,
certificates, private keys, cloud credentials, or real customer
data are embedded. The real restore drill uses the instance
default backup/data/log paths discovered through SERVERPROPERTY.
It creates ServiceHubCapstone_RestoreDrill and drops only that
copy. It does not delete backup media. Advanced AG/FCI topology
remains optional; Enterprise Developer can provide a free
non-production AG lab, while Standard Developer can model
Standard capabilities.
1. Backups are inputs to recovery, not recovery evidence
The mandatory drill takes a full backup, verifies the media,
restores it under a new database name, runs
DBCC CHECKDB, and validates ServiceHub row counts.
RESTORE VERIFYONLY alone does not prove database
logical integrity or application correctness.
USE master;GODECLARE @backup_dir nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultBackupPath'));DECLARE @sep nchar(1)=CASE WHEN CONVERT(nvarchar(20),SERVERPROPERTY('HostPlatform'))='Linux' THEN N'/' ELSE N'\' END;IF @backup_dir IS NULL THROW 50001,'InstanceDefaultBackupPath is unavailable; choose an approved writable backup path.',1;IF RIGHT(@backup_dir,1) NOT IN ('/','\') SET @backup_dir += @sep;DECLARE @backup nvarchar(4000)=@backup_dir+N'ServiceHubCapstone_full.bak';DECLARE @sql nvarchar(max)=N'BACKUP DATABASE ServiceHubCapstone TO DISK=@p WITH INIT,CHECKSUM,STATS=10;';EXEC sys.sp_executesql @sql,N'@p nvarchar(4000)',@p=@backup;SET @sql=N'RESTORE VERIFYONLY FROM DISK=@p WITH CHECKSUM;';EXEC sys.sp_executesql @sql,N'@p nvarchar(4000)',@p=@backup;SELECT @backup AS backup_file;2. Restore to a separate database and verify invariants
USE master;GODECLARE @backup_dir nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultBackupPath'));DECLARE @data_dir nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultDataPath'));DECLARE @log_dir nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultLogPath'));DECLARE @sep nchar(1)=CASE WHEN CONVERT(nvarchar(20),SERVERPROPERTY('HostPlatform'))='Linux' THEN N'/' ELSE N'\' END;IF RIGHT(@backup_dir,1) NOT IN ('/','\') SET @backup_dir+=@sep;IF RIGHT(@data_dir,1) NOT IN ('/','\') SET @data_dir+=@sep;IF RIGHT(@log_dir,1) NOT IN ('/','\') SET @log_dir+=@sep;DECLARE @backup nvarchar(4000)=@backup_dir+N'ServiceHubCapstone_full.bak';DECLARE @data nvarchar(4000)=@data_dir+N'ServiceHubCapstone_RestoreDrill.mdf';DECLARE @log nvarchar(4000)=@log_dir+N'ServiceHubCapstone_RestoreDrill_log.ldf';IF DB_ID(N'ServiceHubCapstone_RestoreDrill') IS NOT NULLBEGIN ALTER DATABASE ServiceHubCapstone_RestoreDrill SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE ServiceHubCapstone_RestoreDrill;END;DECLARE @q nchar(1)=NCHAR(39);DECLARE @sql nvarchar(max)= N'RESTORE DATABASE ServiceHubCapstone_RestoreDrill FROM DISK=N' + @q + REPLACE(@backup,@q,@q+@q) + @q + N' WITH MOVE N' + @q + N'ServiceHubCapstone' + @q + N' TO N' + @q + REPLACE(@data,@q,@q+@q) + @q + N', MOVE N' + @q + N'ServiceHubCapstone_log' + @q + N' TO N' + @q + REPLACE(@log,@q,@q+@q) + @q + N', RECOVERY, CHECKSUM, STATS=10;';EXEC sys.sp_executesql @sql;GODBCC CHECKDB(N'ServiceHubCapstone_RestoreDrill') WITH NO_INFOMSGS;SELECT COUNT_BIG(*) AS restored_work_orders FROM ServiceHubCapstone_RestoreDrill.ops.WorkOrder;GO
If the original database was created with different logical file
names, obtain them from RESTORE FILELISTONLY and
use those exact values. The capstone controls its own database
creation, so the logical names are deterministic here.
3. PITR and HA/DR are different drills
If the business RPO requires point-in-time recovery, use FULL recovery with a verified data-backup baseline and scheduled log backups; then rehearse a full/diff/log restore sequence to a timestamp before the injected mistake. HA adds other evidence: replica synchronization/redo queues, cluster health/quorum, listener/client reconnect, job ownership and failover behavior. Synchronous commit is not a universal zero-data-loss guarantee, and forced failover may lose data.
SELECT name,recovery_model_desc,log_reuse_wait_descFROM sys.databases WHERE name=N'ServiceHubCapstone';SELECT ag.name AS ag_name,ar.replica_server_name,ars.role_desc,ars.connected_state_desc,ars.synchronization_health_descFROM sys.availability_groups AS agJOIN sys.availability_replicas AS ar ON ar.group_id=ag.group_idLEFT JOIN sys.dm_hadr_availability_replica_states AS ars ON ars.replica_id=ar.replica_idORDER BY ag.name,ar.replica_server_name;-- Zero rows is valid evidence on the single-instance mandatory lab; it does not simulate an AG.4. Keep collectors focused and actionable
USE master;GOIF EXISTS(SELECT 1 FROM sys.server_event_sessions WHERE name=N'ServiceHubCapstone_Deadlocks') DROP EVENT SESSION ServiceHubCapstone_Deadlocks ON SERVER;GOCREATE EVENT SESSION ServiceHubCapstone_Deadlocks ON SERVERADD EVENT sqlserver.xml_deadlock_reportADD TARGET package0.ring_bufferWITH(MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF);GOALTER EVENT SESSION ServiceHubCapstone_Deadlocks ON SERVER STATE=START;GOSELECT name,startup_state FROM sys.server_event_sessions WHERE name=N'ServiceHubCapstone_Deadlocks';Ring buffer is appropriate for this short lab, not indefinite incident retention. Production should choose durable event_file targets, retention, access controls and privacy policy. Alerts need thresholds tied to SLO/failure evidence and an owner who knows the runbook; “send email for every warning” creates noise rather than reliability.
5. Integrity and recovery runbook
DBCC CHECKDB(N'ServiceHubCapstone') WITH NO_INFOMSGS;DBCC CHECKDB(N'ServiceHubCapstone_RestoreDrill') WITH NO_INFOMSGS;GOUSE master;IF DB_ID(N'ServiceHubCapstone_RestoreDrill') IS NOT NULLBEGIN ALTER DATABASE ServiceHubCapstone_RestoreDrill SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE ServiceHubCapstone_RestoreDrill;END;GO-- Deliberately keep ServiceHubCapstone_full.bak for the next drill/evidence review.-- OS-level retention/deletion belongs to the backup lifecycle policy.The runbook sequence is Detect → preserve evidence → contain blast radius → recover using the least-destructive proven path → validate database and business invariants → restore traffic → document RPO/RTO and gaps → improve prevention. Repair commands are not the default corruption strategy; restore from known-good data is.
Check your understanding
- Why is RESTORE VERIFYONLY insufficient by itself?
- What does the same-instance restore drill prove?
- Why can an AG still need backups?
- Why is the XE session bounded?
- What is the default corruption recovery philosophy?
Review the answers
1. It checks backup readability/completeness but does not replace a real restore, CHECKDB, and application/business validation.
2. Backup/restore media, file movement and database/application invariants on that instance; it does not prove cluster/site failover behavior.
3. HA replicas can propagate logical mistakes and do not replace independent PITR/corruption recovery.
4. Broad/indefinite tracing adds overhead and may capture sensitive SQL; production collection needs focused predicates/targets/retention.
5. Restore-first from known-good protected copies, with repair-with-data-loss reserved for exceptional salvage situations.