Backup Compression, Encryption, Checksums, VERIFYONLY, Restore Validation, and Media Strategy

Learning outcomes

A recovery file can be readable yet insecure, incomplete, too slow to restore inside the RTO, or impossible to decrypt on the disaster-recovery server. Backup engineering therefore includes media size, CPU cost, checksums, encryption protectors, off-host storage and—most importantly—real restore validation.

1. Checksums improve corruption detection, not business validation

BACKUP ... WITH CHECKSUM asks SQL Server to verify page checksums where present and calculate a backup checksum. During restore/VERIFYONLY, those checksums can detect many forms of media corruption. This is valuable, but it is not proof that every logical relationship, application invariant or external dependency is healthy.

DECLARE @root nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultBackupPath'));IF @root IS NULL THROW 51000,'InstanceDefaultBackupPath is unavailable; choose a writable backup directory explicitly.',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'';SELECT @root AS backup_root;DECLARE @full nvarchar(4000)=@root+@sep+N'ServiceHubRecoveryLab_validation.bak';BACKUP DATABASE ServiceHubRecoveryLab TO DISK=@fullWITH INIT,CHECKSUM,NAME=N'Chapter15 checksum validation full';RESTORE VERIFYONLY FROM DISK=@full WITH CHECKSUM;RESTORE HEADERONLY FROM DISK=@full;

RESTORE VERIFYONLY checks that the backup set is complete/readable and performs additional restore-like checks, but Microsoft explicitly states that it does not verify the structure of the contained data. The gap between “VERIFYONLY succeeded” and “the application can run after disaster recovery” is exactly why recovery drills restore a copy.

2. Compression is a resource trade, and SQL Server 2025 adds ZSTD

Compressed backups usually reduce bytes written and can therefore reduce backup elapsed time on I/O-bound systems, but compression consumes CPU and its benefit depends on data compressibility, TDE, storage throughput and concurrent workload. SQL Server 2025 supports the new ZSTD algorithm in addition to MS_XPRESS and Intel QAT paths where supported.

DECLARE @root nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultBackupPath'));IF @root IS NULL THROW 51000,'InstanceDefaultBackupPath is unavailable; choose a writable backup directory explicitly.',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'';SELECT @root AS backup_root;DECLARE @edition nvarchar(128)=CONVERT(nvarchar(128),SERVERPROPERTY('Edition'));DECLARE @zstd nvarchar(4000)=@root+@sep+N'ServiceHubRecoveryLab_zstd.bak';IF @edition LIKE N'%Express%'  SELECT N'Skip creation: Express does not create compressed backups.' AS note;ELSEBEGIN  BACKUP DATABASE ServiceHubRecoveryLab TO DISK=@zstd    WITH INIT,CHECKSUM,COMPRESSION (ALGORITHM=ZSTD),         NAME=N'Chapter15 ZSTD compressed full';  RESTORE VERIFYONLY FROM DISK=@zstd WITH CHECKSUM;END;

Do not infer production compression ratios or throughput from this tiny lab. Measure representative backup size, CPU saturation, storage latency and restore speed on your hardware. SQL Server documentation currently notes a known issue around configuring ZSTD as the server-wide backup compression algorithm; this course therefore demonstrates the explicit per-backup option instead of recommending a global setting.

3. Backup encryption and TDE solve related but different media problems

TDE encrypts database files and causes backups of that TDE database to remain protected by the TDE certificate/asymmetric key. Backup encryption can encrypt a backup independently using a certificate or EKM asymmetric key in master. In both cases, losing the required protector can convert a perfectly intact backup file into unrecoverable data.

-- Run only in a disposable non-Express lab with secrets supplied at execution time.-- USE master;-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong temporary lab secret>';-- CREATE CERTIFICATE ServiceHubBackupCert--   WITH SUBJECT='ServiceHub Chapter 15 backup encryption';-- BACKUP CERTIFICATE ServiceHubBackupCert--   TO FILE=N'<separate protected path>/ServiceHubBackupCert.cer'--   WITH PRIVATE KEY--   (FILE=N'<separate protected path>/ServiceHubBackupCert.pvk',--    ENCRYPTION BY PASSWORD='<different private-key export secret>');-- BACKUP DATABASE ServiceHubRecoveryLab-- TO DISK=N'<backup path>/ServiceHubRecoveryLab_encrypted.bak'-- WITH CHECKSUM,ENCRYPTION-- (ALGORITHM=AES_256,SERVER CERTIFICATE=ServiceHubBackupCert);

The course does not embed reusable passwords in HTML. In production, the restore runbook must identify the key-escrow location, authorized recovery operators and import steps before the backup is counted toward DR coverage.

4. A real restore is the validation that matters

Restore validation should answer three progressively stronger questions: can SQL Server read the media; can SQL Server restore and recover the database; and does the restored database satisfy integrity and application acceptance checks? VERIFYONLY addresses mostly the first. A disposable restore addresses the second. DBCC/application probes address the third.

DECLARE @root nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultBackupPath'));IF @root IS NULL THROW 51000,'InstanceDefaultBackupPath is unavailable; choose a writable backup directory explicitly.',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'';SELECT @root AS backup_root;USE master;IF DB_ID(N'ServiceHubRestoreValidation') IS NOT NULLBEGIN ALTER DATABASE ServiceHubRestoreValidation SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE ServiceHubRestoreValidation; END;DECLARE @dataRoot nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultDataPath'));DECLARE @logRoot nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultLogPath'));DECLARE @dataSep nchar(1)=CASE WHEN CHARINDEX(N'/',@dataRoot)>0 THEN N'/' ELSE N'\' END;DECLARE @logSep nchar(1)=CASE WHEN CHARINDEX(N'/',@logRoot)>0 THEN N'/' ELSE N'\' END;IF RIGHT(@dataRoot,1) IN (N'/',N'\') SET @dataSep=N'';IF RIGHT(@logRoot,1) IN (N'/',N'\') SET @logSep=N'';DECLARE @full nvarchar(4000)=@root+@sep+N'ServiceHubRecoveryLab_validation.bak';RESTORE DATABASE ServiceHubRestoreValidation FROM DISK=@fullWITH MOVE N'ServiceHubRecoveryLab' TO @dataRoot+@dataSep+N'ServiceHubRestoreValidation.mdf',     MOVE N'ServiceHubRecoveryLab_log' TO @logRoot+@logSep+N'ServiceHubRestoreValidation_log.ldf',     RECOVERY;GODBCC CHECKDB(N'ServiceHubRestoreValidation') WITH NO_INFOMSGS;SELECT COUNT(*) AS work_orders FROM ServiceHubRestoreValidation.lab15.WorkOrderRecovery;SELECT COUNT(*) AS recovery_events FROM ServiceHubRestoreValidation.lab15.RecoveryEvent;GO

A production acceptance suite should also test login/user mappings, encryption keys, required database options, compatibility level, critical stored procedures, representative reads/writes and dependent services. A backup is not resilient merely because it exits with “processed N pages.”

5. Media strategy must survive the failure you are planning for

If the only backup is on the same host/storage account with credentials writable by the same compromised administrator or ransomware process, the backup is part of the same failure domain. Keep multiple recovery copies across appropriate failure domains; protect write/delete privileges; consider immutable/WORM-capable storage where the platform supports it; monitor replication/copy failures; and rehearse recovery when the primary identity system is unavailable.

6. Production judgment

Production judgment. Compression, encryption and checksums change CPU, media size, key dependency and restore behavior. Record the exact SQL Server version/edition that created the backup and the minimum tested target. Preserve protectors independently. Treat VERIFYONLY as a fast screen, then run periodic full restore drills on representative hardware.

Authoritative references

• Backup compression — compression support, ZSTD and performance tradeoffs
• Backup encryption — encryption support and edition limits
• Create an encrypted backup — certificate and key workflow
• RESTORE VERIFYONLY — what VERIFYONLY checks and does not check
• Backup checksums — checksum behavior
• SQL Server 2025 editions and features — backup compression/encryption edition matrix