The database backup verification decision record: why the restore test policy you chose determines your unrecoverable data surface and your regulatory evidence gap

A 28-person SaaS company had been running a nightly PostgreSQL backup job since their first week of production operations. The job ran at 02:00 UTC using pg_dump, compressed and encrypted the output with a symmetric key retrieved from their self-hosted Vault cluster, and uploaded the resulting file to an S3 bucket. Every morning, the job's exit code was checked in the team's infrastructure monitoring dashboard: green meant the backup had completed. The dashboard had been green every morning for eighteen months.

The Vault cluster was a single-node instance running on the same EC2 host as the PostgreSQL database. The team had chosen to co-locate them to reduce operational complexity in the early stage of the company: fewer hosts, fewer things to manage. The Vault instance used the PostgreSQL database as its storage backend — Vault's secret data was stored in a dedicated schema on the same PostgreSQL instance it was protecting. This architecture had been set up by the founding engineer over a weekend and was documented in a short README: "Vault runs on the database host. Keys for backup encryption are stored in Vault at secret/backup/postgres."

In the company's nineteenth month, a storage engineer was investigating slow disk performance on the database host. He ran a filesystem check that required unmounting the data partition. He unmounted it. The PostgreSQL instance crashed. The Vault instance, which depended on PostgreSQL as its storage backend, also crashed. When the engineer remounted the partition and restarted the services, the PostgreSQL data directory was intact. But Vault's initialization state was not recoverable: the Vault instance required an unseal key to start, and the unseal key had been stored in a 1Password vault that the founding engineer, who had left the company four months earlier, had not shared with the current team before departing. The Vault cluster could not be unsealed. The encryption key at secret/backup/postgres was inaccessible.

The storage engineer's filesystem check had not corrupted any data — the PostgreSQL instance came back up cleanly and the company lost no production data. But the incident prompted the operations team to review the backup posture. They found the problem before it became a crisis: they had eighteen months of backup files in S3, each encrypted with a key that was now locked in an unsealed Vault cluster. If the database host had been destroyed instead of briefly crashed, those backup files would have been unreadable. The monitoring dashboard had been green every morning for eighteen months because the backup job had completed successfully — but "completed successfully" meant "uploaded an encrypted file to S3." It had never meant "we verified that this file can be decrypted and restored." The restore had never been tested. The key retrieval path from a separate environment had never been tested. The assumption that the Vault cluster would be available and unsealed at the moment it was needed for recovery had never been tested.

The founding session that established the backup strategy documented the tool (pg_dump), the schedule (nightly), the encryption (Vault-managed symmetric key), and the destination (S3). It did not specify that the decryption key must be retrievable from an environment that is operationally independent from the database host. It did not specify that a restore test — importing the backup into a separate database instance in an isolated environment and confirming that the data is readable and complete — must be executed on a documented cadence. And it did not specify that the monitoring dashboard's green status represents a completed upload, not a verified restore. The eighteen months of backup files were genuine protection against a hardware failure that did not destroy the Vault cluster. They were no protection against the scenario where the Vault cluster was unavailable at recovery time — which was the scenario most likely to occur alongside a database host failure, because the two systems were on the same host.

A 35-person product SaaS ran nightly snapshots of their PostgreSQL database using AWS RDS automated backups. The backup window was configured for 03:00–04:00 UTC, the retention period was seven days, and the CloudWatch alarm for backup failure had never fired. The team's disaster recovery documentation stated: "In the event of complete data loss, restore the most recent RDS automated snapshot. Expected RTO: 2 hours. Expected RPO: 24 hours (daily backup)." The RPO commitment appeared in the company's SOC 2 System Description and had been accepted by their customers without objection.

In month fourteen, an engineer named Priya deployed a database migration that was intended to backfill a new normalized_email column in the users table. The migration ran a Python script that read each row, applied a normalization function, and wrote the result back. The normalization function contained a bug: when it encountered email addresses with Unicode characters in the local part, it returned an empty string instead of the normalized address. The migration ran successfully — zero errors, clean exit code — and the deployment continued. Forty-three users had Unicode characters in their email addresses. After the migration, their normalized_email column was an empty string.

The bug was not discovered immediately. The normalized_email column was introduced to support a new email deduplication feature that had not yet been released. Queries using the column were not yet in production. Three hours after the migration completed, a monitoring alert fired for a different reason — a spike in failed password reset requests. An engineer investigated and found that the password reset flow, which had been updated that morning to use the new normalized_email column for user lookup, was returning zero results for a subset of users. The investigation identified the Unicode email address bug within two hours of the monitoring alert. By this time, it was 11:00 AM — eight hours after the migration had run at 3:00 AM.

The recovery decision was: restore from the most recent backup snapshot to recover the correct normalized_email values, or write a targeted fix migration that re-ran the normalization for the affected 43 rows. The engineering lead chose the targeted fix migration — it was faster and avoided the eleven-hour data loss that a full snapshot restore would produce. But the discussion revealed a gap in the team's understanding of their recovery posture: if the migration had corrupted a column that was already in active use by production queries, the targeted fix might not have been an option, and the team would have faced a choice between an eleven-hour data loss and no recovery at all. The last RDS automated snapshot had been taken at 03:00 AM. A restore from that snapshot would recover the database state from before the migration — but it would also lose eleven hours of production data: customer orders, support tickets, account configuration changes, and user sign-ups from 03:00 AM through 02:00 PM.

The founding session that specified "daily RDS automated backups, RPO 24 hours" had documented the backup mechanism correctly. What it had not documented was the distinction between two different failure categories: complete infrastructure failures (where the database is destroyed and must be restored from the most recent snapshot) and data corruption events (where the database is operational but a subset of its data is incorrect, and recovery requires restoring to a point before the corruption was introduced). For complete infrastructure failures, a daily snapshot and a 24-hour RPO were appropriate and the backup strategy was sound. For data corruption events, the effective recovery point was not the most recent snapshot timestamp — it was the most recent snapshot timestamp before the corruption was introduced, which for a corruption introduced at 03:00 AM was the snapshot from the previous day's backup window: a 24-hour-old snapshot, not a same-day snapshot. And without WAL archiving or RDS point-in-time recovery enabled, there was no mechanism for recovering to any point between snapshots. The founding session had not specified whether WAL archiving was required, what RPO applied to corruption events specifically, or whether "daily backup, RPO 24 hours" applied equally to both failure categories.

RDS automated backups enable point-in-time recovery by maintaining continuous transaction log backups alongside the daily snapshots — but only if PITR is explicitly enabled and the backup retention period covers the target recovery point. The team's RDS instance had automated backups enabled with a seven-day retention, which would have enabled PITR to any point in the last seven days — including to 02:59 AM, one minute before Priya's migration ran. But the disaster recovery documentation had been written by the founding engineer who configured RDS and who had understood "automated backups" to mean "daily snapshots." He had not documented PITR as a capability, had not confirmed it was enabled, and had not included it in the recovery procedures. When the incident occurred, the team consulted their disaster recovery documentation, which described only the snapshot restore procedure. It took twenty minutes of AWS console investigation during the incident to determine that PITR was in fact enabled — and by that point, the targeted fix migration had already been selected as the faster option. The PITR capability that was available had not been in the playbook, because the founding session had not distinguished between snapshot recovery and log-based point-in-time recovery.

A 42-person B2B SaaS company processed financial transactions for their customers — subscription billing, invoice payments, and account credit adjustments. Their database was backed up nightly, with a thirty-day retention window configured in their infrastructure-as-code templates. The thirty-day retention had been chosen in the first month of operations by the founding CTO, who documented the rationale in the infrastructure README: "30-day backup retention is sufficient for recovery from operational incidents. Longer retention is not needed for our current operational risk profile." The decision had not been revisited as the company grew.

In the company's third year, the company entered a sales process with a large enterprise customer. The enterprise customer's security team conducted a vendor due diligence review that included a request for evidence of financial transaction data handling practices. As part of the review, the enterprise customer's legal team issued a data preservation notice and requested a database export of all financial transactions processed for one of the customer's subsidiaries during Q4 of the previous fiscal year — a period that ended five months and three weeks before the request arrived.

The operations team pulled up the backup configuration. The thirty-day retention window had expired for every backup from the requested period. The Q4 transaction data existed in production in aggregate form — the current database contained account balances, cumulative invoice totals, and rolled-up financial summaries. But the individual transaction records from Q4 — the granular rows in the transactions table that recorded each payment event with its timestamp, amount, currency, reference ID, and status — were not in any accessible backup. The oldest available backup was thirty days old. The oldest available transaction records were the ones in the current production database. The Q4 records had been in the thirty-day retention window for thirty days after Q4 ended, and then they had been deleted by the automated retention policy.

The company was not directly regulated as a financial institution, so the transaction record deletion was not a regulatory violation in the strict sense — but it was a contractual gap. The enterprise customer's standard vendor agreement, which the company had signed in year two as part of expanding into the enterprise segment, included a record retention clause requiring the vendor to maintain transaction records for a minimum of three years for audit purposes. The company had signed the agreement. The infrastructure team had not been informed that the agreement changed the retention requirements. The thirty-day retention window that had been set in month one of operations had never been reviewed against the contractual obligations added in year two.

The sales process was paused while the legal team assessed the exposure. The company's counsel advised that the Q4 transaction records were likely covered by the contract's record retention clause and that their deletion, while not deliberate, created a dispute risk if the enterprise customer later had questions about Q4 transaction accuracy. The enterprise customer ultimately proceeded with the vendor relationship — partly because the company's current transaction records were complete and accurate, and partly because the sales team committed to a contractual amendment specifying a three-year transaction record retention period going forward. But the incident cost three weeks of the sales process timeline and required a legal review that would not have been necessary if the founding backup retention decision had included a step for mapping the retention period to the contractual obligations that would accumulate as the company grew.

The founding session that set the thirty-day retention window had documented the operational rationale correctly: thirty days was sufficient for recovery from infrastructure incidents at a company with no regulatory obligations and no enterprise contracts. What it had not documented was the assumption embedded in that rationale — that the retention period was set against the current obligation profile, and that any change to the obligation profile (a new regulatory filing, a new enterprise contract with a record retention clause, a new product feature processing regulated data) would require a retention policy review. The retention period was set once, in the first month of operations, and the mechanism for revisiting it when the obligation profile changed was never established.

Structural properties set by the backup verification decision

Three structural properties are determined when a team decides on a database backup strategy. None appear explicitly in the session that configures the backup job, sets the retention period, or enables the encryption — they are operational characteristics that emerge from the backup strategy's assumptions about key availability, recovery granularity, and the regulatory context in which the data is held.

Property 1: The restore test gap and the unrecoverable data surface. A backup file that has never been restored is not a verified backup — it is a verified upload. The restore test gap is the interval between the last successful restore test and the current moment. During this interval, the backup file's restorability is an untested assumption: the assumption that the file is complete and uncorrupted; that the storage path is accessible from the recovery environment; that the decryption key is retrievable from its storage location via the recovery procedure; that the database engine version on the recovery host is compatible with the backup format; and that the restore procedure produces a working database that the application can connect to and read from. Any of these assumptions can fail silently — the backup job reports success, the monitoring dashboard shows green, and the assumption remains untested until a recovery attempt reveals the failure. The unrecoverable data surface is the set of all backup files in storage that would fail to restore under real recovery conditions. The surface is non-zero whenever the restore test gap is non-zero — which means the surface is non-zero for every team that does not run automated restore tests. The structural requirement is a restore test that exercises the complete recovery procedure: retrieves the backup file from the storage location where it would be retrieved in a real recovery, decrypts it using the key retrieval path that would be used in a real recovery, imports it into a database instance in an environment isolated from production, runs validation queries to confirm schema integrity and data completeness, and confirms that the application can connect and operate normally. The test must be executed on a documented cadence and must alert on failure. The disaster recovery decision record documents the RTO and RPO targets that the restore test validates — confirming not only that the backup can be restored, but that it can be restored within the time window the team has committed to.

Property 2: The point-in-time recovery window and the data loss surface. The backup schedule defines the maximum RPO for complete infrastructure failures — but for data corruption events, the effective recovery point depends on when the corruption is detected relative to the backup schedule. A corruption introduced one hour after the last backup snapshot and detected eleven hours later requires restoring to the pre-corruption snapshot and replaying the write-ahead log from the snapshot time to the moment before the corruption was introduced. Without continuous WAL archiving or binary log streaming, the write-ahead log records between the last snapshot and the current time are not preserved, and recovery to any point other than the last snapshot boundary is impossible. The data loss surface for corruption events is the interval between the first corrupted write and the detection of the corruption — and that surface is bounded only by point-in-time recovery, not by more frequent snapshots. Increasing snapshot frequency from daily to hourly reduces the maximum RPO for infrastructure failures from twenty-four hours to sixty minutes, but it does not close the data loss surface for corruptions detected hours after the last snapshot boundary. The structural requirement for closing the corruption data loss surface is continuous WAL archiving (PostgreSQL) or binary log streaming (MySQL, MariaDB) to a storage location separate from the primary database host, combined with a tested PITR procedure that can restore to an arbitrary transaction timestamp within the retention window. The database migration strategy decision record is the natural dependency: migrations that introduce data transformations are the most common source of corruption events that require PITR rather than snapshot restore, and the migration strategy ADR should specify whether PITR is required before any migration that modifies existing data is permitted to run in production.

Property 3: The backup retention policy and the regulatory evidence period. The backup retention period determines how far back in time the organization can recover data and produce evidence for compliance requests, legal holds, and security investigations. The retention period is set at founding against the obligation profile that exists at founding — typically minimal, because early-stage companies have few regulatory obligations and no enterprise contracts with record retention clauses. The gap opens as the business grows: new product features process regulated data (health information, financial transactions, payment card data), new customer contracts include record retention clauses, and new geographic markets bring data localization and evidence preservation requirements. The regulatory evidence period is the minimum duration for which records must be preserved under each applicable framework. A thirty-day retention window satisfies no common regulatory framework for transaction records — PCI DSS requires twelve months of online access for cardholder transaction data, SOC 2 audit evidence covers a twelve-month audit period, and most enterprise vendor agreements include record retention clauses of one to three years. The structural requirement is a mapping between the retention period and the obligation profile: for each category of data in the database, identify the applicable regulatory frameworks and contractual obligations, determine the minimum retention period required by each, and set the retention window to the maximum of the organization's operational recovery requirement and the maximum applicable obligation period. This mapping must be reviewed whenever the obligation profile changes — when a new enterprise contract is signed, when a new product feature is launched that processes regulated data, or when the business enters a new geographic market. The data governance decision record is the upstream document: it classifies the data in the database by regulatory category and sensitivity, which provides the input that the backup retention policy mapping requires. Without a data governance classification, it is not possible to know which retention period applies to which data — and a single retention window applied uniformly to all data in the database will either over-retain some categories (incurring unnecessary storage cost and privacy risk) or under-retain others (creating compliance gaps for the regulated categories).

What the founding session records and what it omits

The founding backup session — typically a single afternoon of infrastructure setup during the first weeks of production — records the tool, the schedule, the storage destination, and sometimes the encryption configuration. What it does not record is the set of operational policies that the backup strategy requires to be effective: the restore test cadence, the PITR configuration for corruption recovery, the key storage independence requirement, and the retention period mapping to regulatory obligations.

The omission is structurally similar to the gap in the branching strategy, the API security, and every other founding-session decision: the failure modes are theoretical at founding, when the database is small, the obligation profile is minimal, and the team that set up the backup system is the same team that would restore from it. The restore test gap is not visible when the team ran the first restore manually during setup and the system worked. The PITR gap is not visible when the team has never experienced a data corruption event and "daily backup" sounds like a complete recovery strategy. The retention gap is not visible when the business is pre-enterprise and has no customer contracts specifying record retention requirements.

The failure modes become visible when the backup is actually needed. And the three scenarios in this post share a common pattern: the team discovered the gap at the worst possible moment — during a recovery attempt, during an incident investigation, during an enterprise sales process — when there was no time to fix the underlying policy and the only option was to manage the consequences. The eighteen months of unreadable backup files were discovered during an incident that happened not to destroy production data; had it destroyed production data, the discovery would have coincided with zero recovery options. The PITR capability that could have recovered the corrupted migration was discovered during the incident rather than in a recovery runbook — it was available but unknown, which meant the team spent twenty minutes during the incident determining whether the capability existed rather than executing a documented recovery procedure. The retention window gap was discovered during a sales process rather than a legal dispute — but the discovery came close enough to the dispute scenario that the legal team needed to assess the exposure.

The backup verification ADR closes the gap by recording the three policy commitments that the backup strategy assumes: the restore test cadence (how often restorability is validated and under what procedure), the PITR configuration (whether continuous log archiving is required and what RPO it is intended to achieve for corruption events), and the retention period mapping (which regulatory frameworks and contractual obligations apply to each data category and what retention window they require). These are the commitments that distinguish a backup strategy from a backup job — the difference between "we upload an encrypted file to S3 every night" and "we have verified that we can recover our production database to within ten minutes of any point in the last thirty days, that the recovery can be completed within two hours, and that we retain financial transaction records for three years in compliance with our enterprise customer agreements." The first statement describes the backup job. The second describes the backup verification decision. The decisions never written down are rarely the high-level choices — "we back up nightly to S3" appears in every infrastructure README. They are the operational policies that were assumed in the session that set up the backup system and never formally recorded: the restore test requirement, the PITR decision, the retention mapping. Those are the decisions that accumulate interest as the infrastructure ages, the obligation profile grows, and the gap between the backup job's documented behavior and the team's assumed recovery capability widens invisibly until a recovery event makes it visible.

The database backup verification ADR: five sections

Section 1: Backup mechanism and restore test cadence. Specify the backup tool (pg_dump, pg_basebackup, mysqldump, RDS automated backups, Barman, pgBackRest), the backup schedule, and the storage destination. Then specify the restore test procedure and the cadence at which it is executed: what environment the restore is performed in (an isolated environment, not production), what steps the procedure includes (retrieve backup file from storage, retrieve decryption key via the same path that would be used in a real recovery, import to a fresh database instance, run schema validation queries, verify row counts against production metrics from the backup timestamp, confirm application connectivity), and what the alerting behavior is when the restore test fails. The restore test cadence should be at minimum weekly for production databases and must be triggered by any change to the backup tool version, encryption configuration, storage destination, or restore procedure. The restore test is not a quality improvement — it is a requirement for the backup to be classified as verified. An unverified backup is not a recovery option; it is an untested assumption about recovery options.

Section 2: Encryption and key storage independence. Specify the encryption mechanism (symmetric key, KMS-managed key, Vault-managed key) and the key storage location. The key storage location must be operationally independent from the database host: a failure or destruction of the database host must not simultaneously render the key inaccessible. The structural requirement is that the key can be retrieved from the recovery environment without network access to the production VPC or the production host. For self-hosted Vault: the Vault cluster must run in a separate availability zone from the database, must use a separate storage backend not hosted on the database instance, and must have its unseal key documented and accessible to the current operations team (not only to a departed founding engineer). For cloud KMS: the KMS key must be in a region separate from the primary backup storage or must use multi-region key replication. The restore test validates key storage independence by executing the key retrieval step from the isolated test environment: if the key can be retrieved and the backup can be decrypted from outside the production VPC without production credentials, the key storage is sufficiently independent. If the restore test requires production network access to retrieve the key, the co-location failure mode is present and the key storage must be redesigned before the backup strategy can be classified as verified.

Section 3: Point-in-time recovery configuration and corruption event RPO. Specify whether point-in-time recovery is required and, if so, the target RPO for corruption events. For corruption events — data incorrectly written by a software bug, an erroneous migration, or a malicious actor while the database continues operating — the recovery point depends on continuous log archiving rather than snapshot frequency. Specify whether WAL archiving (PostgreSQL), binary log streaming (MySQL, MariaDB), or cloud-managed PITR (RDS, Cloud SQL) is configured; the log retention duration; and the tested recovery procedure for PITR (including how to identify the target recovery timestamp and how to confirm that the restored state is pre-corruption). The PITR RPO should be documented separately from the snapshot RPO, because they apply to different failure categories: the snapshot RPO applies to infrastructure failures, the PITR RPO applies to corruption events. If PITR is not configured, the corruption event RPO is the snapshot interval — which means a corruption introduced immediately after the last snapshot and detected eleven hours later produces eleven hours of data loss. The decision to not configure PITR must be explicit (not merely an omission), and the operations team must understand that the snapshot RPO does not apply to corruption scenarios.

Section 4: Retention period and regulatory mapping. Specify the backup retention period for each data category and the regulatory frameworks or contractual obligations that determine the minimum retention requirement. The mapping should list each applicable framework (PCI DSS, SOC 2, GDPR, HIPAA, contractual record retention clauses), the minimum retention period it requires for the relevant data category, and the resulting retention window. The backup retention period must be the maximum of the organization's operational recovery requirement and the maximum applicable retention obligation. Specify the review trigger: the retention policy must be reviewed whenever the obligation profile changes — when a new enterprise contract includes a record retention clause, when a new product feature processes data in a regulated category, or when the business expands into a new geographic market. The review mechanism (who is responsible, how they are notified of obligation profile changes, and how the retention configuration is updated) must be documented in the ADR so that the review actually occurs rather than being left to the discretion of the current operations team at the moment a new obligation arrives. The data governance decision record provides the upstream classification; this section translates that classification into specific retention windows.

Section 5: Recovery runbook completeness and incident integration. The backup strategy is complete only when the recovery runbook is complete and the incident response process routes incidents involving data loss or corruption to the runbook rather than requiring ad-hoc investigation during the incident. Specify what the recovery runbook must include: the step-by-step procedure for snapshot restore (including how to identify the correct snapshot, how to provision the recovery environment, and how to validate the restored state before switching production traffic), the step-by-step procedure for PITR restore (including how to determine the target recovery timestamp from application logs or monitoring, how to execute the PITR restore to that timestamp, and how to confirm that the restored state is pre-corruption), and the contact list for who must be notified and who must approve each step of the recovery. The runbook must be tested as part of the restore test cadence — the restore test does not only verify that the backup can be restored; it verifies that the runbook procedure produces a working database when followed by a team member who was not present when the backup was configured. The incident response playbook decision record documents the escalation path and the communication requirements for incidents involving data loss; the backup verification ADR should reference it for the notification and approval steps. The new CTO onboarding problem is most acute for backup and recovery: the incoming technical leader finds that the infrastructure README says "nightly backups to S3" and cannot determine whether the backups have ever been tested, whether PITR is configured, or whether the retention period covers the regulatory obligations the business has accumulated. The backup verification ADR makes those commitments explicit and auditable — findable by anyone who needs to verify the recovery posture without re-discovering it through a recovery event. The WhyChose extractor finds the backup strategy discussion in your AI session history — the conversation where your founding engineer chose the backup tool, the retention window, and the encryption approach — and surfaces the commitments that were made in that session, so you can check which of the five ADR sections those commitments correspond to and which were never documented.

FAQ