Immutable Backups: Keeping a Copy Ransomware Cannot Delete

An attacker who can delete every recovery copy can turn a repairable incident into a permanent loss. Immutable backups add a retention boundary that must be enforced outside the compromised database server.

Define the Threat That Immutable Backups Answer

A backup is useful only while its media and required recovery material remain available. A file on a share that the production identity can overwrite or delete can disappear with the production data. Treat the recovery design as a separate failure boundary rather than another folder on the same machine.

Immutability means an accepted storage policy prevents changes or deletion for its enforced retention period. The exact policy mode and administrative bypass behavior matter. Verify the supported locked-retention semantics for the chosen native storage design. A label that says protected does not establish which identities can shorten the period or remove the policy.

I check the effective delete and policy-administration rights before judging the backup process by its green status. Separate the workload identity from the recovery-storage administrator. The attack scenario should include compromise of the production service, routine backup account, and relevant management access. A folder name containing safe has no enforcement authority.

Inventory Where Recent Backups Were Written

Local backup history provides evidence of the media destinations used by completed operations. It does not prove that the files still exist, remain readable, or have an enforced retention lock. Capture the inventory and compare it with the actual protected copy records.

SELECT b.database_name,b.type,b.backup_start_date,b.backup_finish_date,
       b.has_backup_checksums,b.is_copy_only,
       m.family_sequence_number,m.device_type,m.physical_device_name
FROM msdb.dbo.backupset AS b
JOIN msdb.dbo.backupmediafamily AS m ON m.media_set_id=b.media_set_id
WHERE b.backup_finish_date>=DATEADD(day,-7,SYSDATETIME())
ORDER BY b.database_name,b.backup_finish_date DESC,m.family_sequence_number;

Preserve all media families for striped backups. A destination field can describe an old path or a device rather than a presently accessible file. Confirm the actual transfer and retention result independently. A successful local backup followed by a failed protected-copy transfer leaves a gap that needs its own alert.

Inventory encryption certificates, private keys, passwords, and other required recovery material through the approved protected process. A retained encrypted backup is unusable if its only decryption material disappears with the original server. Keep those dependencies accessible to authorized responders outside that same failure boundary.

Create a Checked Backup Without Confusing Its Protection

CHECKSUM adds integrity checks to the backup operation where supported. It does not make the destination immutable or prevent an authorized attacker from deleting the file. The example creates a uniquely named copy-only full backup in an existing approved staging directory.

DECLARE @Path nvarchar(4000)=N'C:\BackupStage\ProtectionLab_'
    +CONVERT(nvarchar(36),NEWID())+N'.bak';
BACKUP DATABASE ProtectionLab
TO DISK=@Path
WITH COPY_ONLY,CHECKSUM,COMPRESSION;
IF @@ERROR=0 SELECT @Path AS CreatedBackupPath;

The database must already exist in the disposable lab, and the service account needs directory access. The path comes back only when the backup succeeds, so a failed write does not leave a misleading file name on screen. Copy-only preserves the existing differential-base strategy for this supplemental example. It does not replace the accepted full, differential, and log-backup schedule. Keep that recovery chain intact when adding an independently retained copy.

A protected transfer should record the source backup identity, all required files, transfer completion, retention expiration, and the effective storage policy. Do not declare the copy protected merely because upload or file copy finished. Verify the retention behavior under the relevant permitted administrative identities in a controlled test.

Enforce Immutable Backups With Independent Administration

Choose a retention period that covers the expected detection and recovery interval. Confirm when the policy begins, whether it is locked, which objects it covers, and what happens to newer versions or appended content. Different policy modes can have different bypass rules, so use the mode that matches the accepted threat model.

Separate credentials and administrative roles for writing backups, managing retention, reading recovery media, and performing restores. Restrict the production host's ability to administer the protected destination. Include multi-factor access and reviewed recovery procedures where supported. Routine convenience should not give one compromised account control of every recovery layer.

Immutable backups still need protection against creation of unusable new copies. Monitor unexpected backup failures, retention changes, transfer gaps, and lost recovery access. An attacker can corrupt production data before the next protected backup is created. Retain enough earlier recovery points to cover that possibility rather than keeping only the newest object.

Keep Offline and Offsite Recovery Material

An offline copy is disconnected from the active environment for its accepted storage interval. An offsite copy protects a different physical or regional failure boundary. Those properties can complement enforced retention, but they are not interchangeable. A connected offsite share with shared credentials can still be vulnerable to the same account compromise.

Define how approved responders locate, transport, and access disconnected media. Test that procedure with the actual key material and destination requirements. Record media handling and retention so an old offline copy does not become an unexplained box whose contents nobody can restore.

Which identity can currently remove every recovery path? Answer that question across online, offsite, and disconnected copies. I review the combined access design instead of assessing each storage destination in isolation. Independent layers are valuable only when their administrative boundaries are actually independent.

Verify Integrity and Perform a Real Restore

RESTORE VERIFYONLY checks whether the backup set is readable and complete within its documented scope. With checksum verification, it can detect relevant integrity problems. It does not restore the database or prove the application's recovery behavior, so follow it with an isolated restore test.

RESTORE VERIFYONLY
FROM DISK=N'C:\RecoveryMedia\ProtectionLab_full.bak'
WITH FILE=1,CHECKSUM;
RESTORE FILELISTONLY
FROM DISK=N'C:\RecoveryMedia\ProtectionLab_full.bak';

Use the file list to confirm logical names, then restore to new test paths. The following names are placeholders for a reviewed two-file backup. Include every file and media family when the actual backup layout differs.

RESTORE DATABASE ProtectionLabRestoreTest
FROM DISK=N'C:\RecoveryMedia\ProtectionLab_full.bak'
WITH FILE=1,MOVE N'ProtectionLab_Data' TO N'C:\RestoreTestData\ProtectionLabRestoreTest.mdf',
     MOVE N'ProtectionLab_Log' TO N'C:\RestoreTestData\ProtectionLabRestoreTest_log.ldf',
     RECOVERY,CHECKSUM;
DBCC CHECKDB(N'ProtectionLabRestoreTest') WITH NO_INFOMSGS;

Test from the independently retained copy rather than only from staging. Include authentication and representative business validation. Measure the complete recovery path and preserve failed results. A local staging restore cannot certify that responders can access the locked copy during the assumed incident.

Keep Evidence That Immutable Backups Restore

Keep backup-chain coverage, protected-transfer results, enforced expiration dates, recovery access, and the latest restore evidence together. Review changes to storage policy and administrative roles. Ensure retention expiration does not remove the only usable chain before a replacement has been verified.

Immutable backups are one component of recoverability. Their strength comes from enforced policy, independent access, retained keys, and tested recovery copies. Verify each layer against the accepted threat and failure scenarios rather than assuming one storage setting solves the entire incident.

Related reading on this blog: Full, Differential and Log Backups: A Practical Guide and Check Backup Reliability.

A retained backup is not a complete recovery guarantee, it is one protected input to a tested and independently accessible recovery path.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.