Proving RPO and RTO: designing restore tests that measure real numbers

About this article: AI-assisted writing, reviewed against the cited sources. This is source research, not a lab validation of the procedures. Check the stated versions and your own recovery requirements before making changes.

A successful backup job proves that a job finished. It says nothing about how much data you would lose or how long you would be down. To get those two numbers, inject a failure while an external writer records every acknowledged write, time each recovery stage from the moment of failure, and stop the clock only when a real application transaction succeeds on the recovered system.

The commands below were not run in a lab. Server names, database names and timings are examples.

Recommendations and interpretations are editorial unless attributed; source research is not lab validation.

Why it matters

Recovery objectives are usually written into a plan once and then assumed. NIST defines the recovery time objective as the maximum time a system resource can remain unavailable before there is an unacceptable impact, and the recovery point objective as the point in time before an outage to which data can be recovered. NIST also separates them from maximum tolerable downtime: the RTO must normally be shorter than that limit, while "RPO is not considered as part of MTD".

An objective is a target. What you need from a test is the achieved value next to it.

Microsoft is blunt about the SQL Server case: you do not have a backup strategy until you have restored backups in every combination your strategy includes and tested each restored database for physical consistency. Google's SRE book makes the same point more generally: you only know you can recover your recent state if you actually do so.

In Australia, the Essential Eight also asks for this at its first maturity level: restoration of data, applications and settings from backups to a common point in time is tested as part of disaster recovery exercises.

How it works

Where each clock starts and stops

An RTO can look better than it is if the clock starts late. AWS defines RTO as the delay between the interruption of service and the restoration of service. So the clock starts at the fault, not when someone opens the restore console. Microsoft's Azure guidance says to account for total recovery time across the entire flow, including the time to detect the issue and respond.

AWS describes RPO as the maximum acceptable time after the last data recovery point. For measurement, distinguish recovery-point age at the failure from the interval and count of acknowledged writes actually missing. An idle period can make those figures differ.

Estimates are not measurements

Replication products publish useful live figures, but they are forecasts. For SQL Server Always On availability groups, Microsoft gives failover time as detection plus overhead plus redo, where redo time is redo_queue_size divided by redo_rate. It estimates data loss as log_send_queue_size divided by the log generation rate, and in a group of several databases the highest value sets the limit.

Run this query on the primary, which reports all replicas, against sys.dm_hadr_database_replica_states:

SELECT DB_NAME(database_id) AS database_name, replica_id, is_primary_replica,
       log_send_queue_size, redo_queue_size, redo_rate, last_commit_time
FROM sys.dm_hadr_database_replica_states;

The view does not hold everything. The log generation rate is the performance counter SQL Server:Database > Log Bytes Flushed/sec, in bytes per second, while log_send_queue_size is in kilobytes, so convert before dividing. redo_rate is averaged over the time redo was active since startup and may be higher than the performance counter, which can make the redo forecast too kind. Microsoft's simpler check compares last_commit_time between the primary and secondary rows.

PostgreSQL's pg_stat_replication lag columns are similar. On an asynchronous standby, replay_lag only approximates how long recent transactions take to become visible. When the standby has caught up and write activity stops, the last measured figures show briefly and then become NULL.

These figures are good for monitoring between tests. None of them includes the alert, the human decision, the application start or the DNS change.

What actually consumes restore time

Three things are commonly left out of a back-of-envelope estimate.

File preparation. SQL Server zeroes data and log files during a restore unless instant file initialisation applies. Instant initialisation of data files needs the SE_MANAGE_VOLUME_NAME privilege and is prevented by transparent data encryption. The SQL Server 2022 change for log files covers autogrowth up to 64 MB, so a restore should still be expected to zero its log. The service right belongs to the server, so a test server without it gives the wrong time. Encryption belongs to the database and comes with the backup, so an encrypted database gets no instant initialisation of data files on any server.

Redo and undo. Recovery rolls forward logged changes, then rolls back incomplete transactions. With traditional recovery, a long-running transaction open at the time of failure can make this slow. Accelerated database recovery is designed so that long-running transactions do not affect overall recovery time, but on SQL Server it is available from 2019 and off by default. An idle-hours test with no open transaction measures a kinder case than a busy afternoon.

Getting the backup. Azure's guidance names moving data across regions and restoring from cold storage as scenarios to verify. If the local copy would die with the site, a restore from local disk does not measure your disaster RTO.

What each verification step proves

Azure adds a check that is easy to forget: backups can be misconfigured to capture only a subset of your data, so validate completeness and not only whether the restore ran.

What to choose and why

Use three levels of test, because each yields a different number (our recommendation; NIST lists checklists, walk-throughs, tabletops, simulations and comprehensive exercises as the range of methods).

Azure's position is that production drills are the only way to verify RTO and RPO targets in real conditions, and to test overall and component-level RTO and RPO separately from full drills. NIST asks for tests as close to an operating environment as possible. If you cannot run the drill, say in the report that the RTO is inferred from isolated runs. No planned level times detection and decision. Take T0 to T2 from an unannounced exercise or from alert history for real incidents, add it to the measured stages, and say the RTO was composed that way.

1. Write the acceptance criteria first

NIST's test plan calls for explicit test objectives and success criteria. Before the run, write down the target RTO and RPO for this system, the failure scenario, which copy of the backup will be used, and the exact transaction that ends the test. Azure treats RTO and RPO as pass or fail criteria.

2. Measure RPO with one clock

Use a canary in each database whose data and log path you are measuring. The implementation is a reviewed application change, not a general-purpose production script. A separate database does not necessarily share the same backup or replication boundary.

The external writer needs an explicit protocol:

1. Allocate a unique run identifier and monotonic sequence for each attempt. Record the request time outside the failure domain.
2. Submit a transaction containing that identity and a known payload. Wait for the database client's successful commit acknowledgement.
3. Record a separate acknowledgement timestamp only after success, then durably save the external record. Preserve errors as well as successes.
4. Record the failure marker T0 on the same recorder, with a monotonic elapsed-time clock for durations and UTC for correlation. Stop or isolate the writer so later recovery writes cannot contaminate the comparison.
5. Before allowing new application writes, compare every acknowledged identifier at or before T0 with the recovered rows and payloads. Do not compare only the largest sequence: holes and unrelated test runs can hide missing writes.

The request timestamp is not the acknowledgement timestamp. A transaction submitted before T0 may complete afterwards, and a committed transaction may lose its reply. Those outcomes must remain separate in the evidence. A database row without a recorded acknowledgement is an uncertain client outcome, not proof that an acknowledged write was lost.

Client behaviour needs testing too. PostgreSQL's psql and SQL Server's sqlcmd expose different error and connection handling. SQL Server 2025's mandatory-encryption default can make a canary fail before it writes anything if certificate trust is missing. Validate authentication, timeout, connection-loss and log-flush behaviour in the test environment, then confirm the writer produced acknowledged records before the exercise starts.

Report at least three results. First, give the number and identities of acknowledged writes absent after recovery. Second, identify the latest verified recovery boundary without gaps in those writes. Third, give the age of that boundary at T0, with the canary interval and timestamp uncertainty. A last-acknowledgement-to-recovered-write interval is a separate measure; calling it recovery-point age would hide an idle interval before failure.

If no acknowledged writes are missing, report that result directly. The age of the last canary can still be nonzero because there were no later writes. If gaps exist below the highest recovered sequence, report them rather than claiming the maximum row proves complete recovery.

For an isolated restore, production can remain running while T0 is a declared cutoff. Only backup material that had reached the surviving failure domain by T0 is eligible. Later-arriving logs would measure a different scenario and must not be silently added to improve the result.

An acknowledgement is not always durable. SQL Server delayed durable commits report success before the log records are written to disk, and PostgreSQL asynchronous commit has a short window between the report to the client and a true commit. The canary shows that loss correctly, but the setting changes the result, so record it, along with the commit mode of any replica.

Run a writer against each system that must share a common point in time. The spread between their recovered positions is a number worth reporting: it is the inconsistency your application would have to absorb.

3. Choose the failure moment on purpose

With periodic backups, loss is a sawtooth: small just after a backup reaches safety and largest just before the next one does. A random test point samples the sawtooth. Inject the fault just before the next copy is due to land, so that you measure the worst case.

The worst-case figure is approximately the backup interval, plus the time a backup takes, plus the time to copy it out of the failure domain. Check the real gaps between tests:

SELECT TOP (50) backup_start_date, backup_finish_date
FROM msdb.dbo.backupset
WHERE database_name = N'Sales' AND type = 'L'
ORDER BY backup_finish_date DESC;

These dates come from backupset and show when the backup finished on the server, not when an off-site copy arrived.

In PostgreSQL, the archive command runs only on completed WAL segments, so a quiet server can hold committed work unarchived for a long time unless archive_timeout forces a switch. Watch pg_stat_archiver, remembering that it is not safe to assume every file older than last_archived_wal was archived. After restoring, pg_last_xact_replay_timestamp() gives the time of the commit or abort record of the last replayed transaction, a useful cross-check against the canary. Interpret it according to the server's recovery history; do not substitute it for the external acknowledgement comparison.

Also decide whether the failure leaves the log behind. SQL Server needs a tail-log backup to recover to the latest point, and on a damaged database that can succeed only if the log files are undamaged, the database supports it and it holds no bulk-logged changes. A storage or site loss removes the log, so test that case too.

For replication tools, the option you select changes the result. Azure Site Recovery offers Latest processed for low RTO and Latest for lowest RPO, and Latest app-consistent for the newest application-consistent point. Test the one your runbook would use, and do not take the RTO from one and the RPO from the other.

4. Time every stage

Use one recorder, appending UTC timestamps to a shared log as each stage ends. If timestamps must come from several hosts, check clock health first: chronyc tracking shows the last offset and the long-term RMS offset. Windows time is not covered here.

Measured service recovery time is T7 minus T0, the moment service is proven restored (our definition). Keep T8 as a separate figure: it shows how long the rest of the clients take, for example with cached DNS. If your objective is written around all users being back, say so and use T8. The intervals show where to spend money.

Do not rely on the database alone for these marks. In SQL Server, restorehistory.restore_date is the start of the restore. During the run, RESTORE ... WITH STATS and percent_complete show progress, but the same view documents estimated_completion_time as internal only.

5. Define the finish line

The RTO clock stops at T7, when a real business transaction completes through the normal client path, then reads back correctly. Azure says to validate DNS switchover, data consistency and client reconnection, and to check that the failover environment has enough capacity under load.

Failback is its own measurement, as Azure notes it is often more complex than the initial switchover.

If a consistency check such as DBCC CHECKDB would run before users return in a real event, it belongs inside the clock. If it would run afterwards, record its duration separately.

6. Report the number, not the feeling

A hypothetical result, for illustration only:

Report the worst of several runs, along with data size, load, backup age, commit durability and the accelerated database recovery setting at the time. When a test misses its target, analyse the gaps and update the plan.

Risks and limits

Warning: fault injection against a live system can cause the outage you are testing for. Do it only inside an approved change window with a tested way back. Azure advises dry runs in non-production before production drills.

Isolation failures. A restored system can reach real services with real credentials: mail relays, payment gateways, scheduled jobs, domain controllers. Azure recommends testing restores in an environment separate from production, and its Site Recovery drill recommends a non-production network. Disable outbound integrations in the restored copy, and treat restored data with the same access controls as production.

The canary is a change. Adding a table and a once-a-second writer to a production database needs approval, and it adds a small amount of log traffic. It can also change results on a nearly idle PostgreSQL server, because extra writes fill WAL segments sooner than real work would. On a quiet system, compare with a run where the canary writes only at the fault.

Boot verification is not a restore timing. Veeam's SureBackup in full recoverability mode starts machines directly from compressed and deduplicated backup files in an isolated lab, then runs heartbeat, ping and application tests. That is strong evidence of recoverability. It does not time a full copy back to production storage.

Small test data flatters results. Restore time depends on size, storage and contention. Times from a subset cannot simply be multiplied up. Azure's advice is to measure actual restore times against targets.

Not covered here. This article does not set targets, cover vendor-specific replication journals, or give a schedule that satisfies any particular audit.

Sources

Opened 2026-11-10.

• NIST SP 800-34 Rev. 1, Contingency Planning Guide
• NIST SP 800-53 Rev. 5 (CP-4, CP-9)
• ASD Essential Eight maturity model
• AWS Well-Architected: recovery objectives
• Azure Well-Architected: disaster recovery and testing strategy
• Azure Site Recovery: run a drill
• Google SRE Book: Data Integrity
• SQL Server: availability group performance, replica states, backup and restore, restore and recovery, tail-log backups, instant file initialisation, accelerated database recovery, RESTORE VERIFYONLY, DBCC CHECKDB, RESTORE arguments, sys.dm_exec_requests, backupset, restorehistory, sqlcmd
• PostgreSQL 18: continuous archiving, monitoring statistics, administration functions, psql
• SQL Server: managing accelerated database recovery, transaction durability. PostgreSQL 18: asynchronous commit
• chrony 4.9 chronyc
• Veeam: How SureBackup works

This article was originally published on 10 November 2026.