Recovery Exposure
Overview
The Recovery Exposure report answers the two questions people actually ask about a backup, on one picture:
- RPO. If this database died right now, how much data would be gone?
- RTO. How long until it is back?
Every other backup report in the product answers the first question. None of them answers the second, because a count of backup files is not a duration – forty log files off a local SSD and forty off a tape library are the same number and a completely different afternoon.
The chart is a cascade on a timeline whose origin is the moment of failure. Left of the origin is what would be lost. Right of it, each restore stage starts where the previous one ended, so the width of the whole lane is the time to get back and the width of each band is the part of it that stage is responsible for.
Where to find it
A database-level report. Expand the server → expand the database → Real Time → Recovery Exposure.
Not available on Azure SQL Database, which takes and keeps its own backups and exposes no msdb backup history to read. Azure SQL Managed Instance and AWS RDS both keep msdb and are supported.
Reading the cascade
The vertical line is the moment of failure. Everything left of it already happened. Everything right of it is what happens next.
Left of the line: what you lose. The hatched band reaches back to the last backup that protects your data. Its colour is the same banding the instance Backup Status report uses, so a database that reads Exposed there reads Exposed here.
Right of the line: what it costs. The stages, in the order they run:
| Stage | What it is |
|---|---|
| Locate media | Finding the files, mounting the share, pulling from the vault. |
| Restore full | The base full backup. Usually the widest band. |
| Restore differential | Only when there is one taken after the base full. |
| Replay logs | Every log backup after that, in order. |
| Recovery | The undo phase, after the last file is in. |
The total sits at the end of each lane, green when it is inside the RTO target and amber when it is not.
Why the fill patterns matter
A restore time cannot be measured. SQL Server records when a restore finished and never how long it ran, so there is no restore rate anywhere on the instance to read. Every number on the right of this chart is therefore an estimate, and the fill says what kind:
| Fill | Meaning |
|---|---|
| Solid | The bytes and the rate behind it were both measured on this instance. |
| Combed (vertical stripes) | Many files rather than one. The count is on the band. |
| Hatched (diagonal) | A constant you set, or a model with constants in it. |
That is the difference between a useful estimate and a confident one. You can see at a glance how much of the total is arithmetic and how much is assumption.
The three scenarios
Recovery is a choice, not a fact, and the choice is a trade. The report costs all three and draws them on the same axis so the trade is visible:
- Latest – full, differential, and every log. The finest recovery point available, and usually the longest restore.
- Last diff – full and differential, stopping there. Shown only when a differential was taken after the base full.
- Last full – the base full alone. The fastest way back and the most data lost.
Clicking a lane switches the grid underneath to that scenario. On a database with a long log chain, the Latest lane is often hours long while Last full is inside the target – and seeing both at once is the point of the report.
Where the numbers come from
| Input | Source |
|---|---|
| Data loss | Measured. The gap between now and the last protecting backup, from msdb. |
| Restore throughput | Measured. This database’s own backup rows: bytes divided by how long the backup ran. |
| Restore factor | Yours. How much slower a restore runs than the backup did. Defaults to 1. |
| Locate media | Yours. Defaults to 5 minutes. |
| Per log file cost | Model constant. 6 seconds per file, before any bytes are replayed. |
| Log replay rate | Model constant. A quarter of the data rate, because redo is largely single threaded. |
| Recovery (undo) | Model constant. Nothing in msdb predicts it. |
The footer under the chart names the throughput it used and every constant it applied. If a database has no backup with a usable duration to measure from, the report says so and falls back to a stated assumption rather than quietly inventing one.
Tuning it
Two boxes on the toolbar: RTO target and Restore factor. Change either and press Apply. Nothing goes back to the server – all three scenarios are costed from data already in memory, so it repaints instantly and you can nudge the numbers until they match a restore you have actually timed.
When the chain is broken
If the log chain has a break, the restore does not take longer. It stops.
The lane draws a wall at the break instead of continuing, the data loss is measured against the last point that can actually be reached rather than against the newest backup on the page, and the subtitle says where the break is. A report that printed a confident duration for a restore that cannot finish would be worse than one that printed nothing.
The same applies to a database with no full backup, or whose last full went to the NUL device: the lane reads Nothing to restore from rather than drawing a cascade.
The grid
One row per file the restore would read, in the order it would read them.
| Column | Meaning |
|---|---|
| Step | Position in the sequence. |
| Type | Full, Differential, or Log. |
| Taken | When the backup finished. |
| Size / Compressed | What msdb recorded for it. |
| Estimate | How long this file is expected to take. |
| Cumulative | Elapsed time once this file is done. |
| Flags | damaged, copy only, no checksum, summarised. |
| Written To | The path msdb recorded. |
Selecting a row highlights the stage it belongs to on the chart. Many rows map to one band – four hundred log files are a single band – which is the division of labour: the grid says which file, the chart says what it costs you.
On summarised rows. Where log backups were taken too close together to list individually, one row stands for the rest and says how many. The count itself is exact: it comes from a server-side count over the whole of backupset, not from the rows on screen.
Script Restore
The button that makes this page worth opening before an outage rather than during one.
It writes the full RESTORE DATABASE and RESTORE LOG sequence for the selected scenario, with the real device paths msdb recorded, NORECOVERY throughout, and a final WITH RECOVERY. Where the chain was summarised, the script drops in the query that lists the remaining files rather than inventing names for backups it never read.
It only ever shows the script. Nothing on this page runs anything against your server.
Check every path before you run it.
msdbrecords where a backup was written, not whether the file is still there.
On Amazon RDS
RDS does not grant a login the permissions RESTORE DATABASE needs, so a native restore script is not something an RDS DBA can adjust and run: it fails on its first statement. The report detects RDS (by the presence of the rdsadmin database) and changes what it offers.
Script Restore emits the RDS sequence instead:
| Step | Procedure |
|---|---|
| Full | msdb.dbo.rds_restore_database |
| Differential | msdb.dbo.rds_restore_database with @type = N'DIFFERENTIAL' |
| Log | msdb.dbo.rds_restore_log |
| Bring online | msdb.dbo.rds_finish_restore |
| After every step | msdb.dbo.rds_task_status |
Three things the script says that the on-premises version never has to:
- The restores are asynchronous.
rds_restore_databasereturns as soon as the task is queued, not when the restore is done. Running the sequence as one batch starts a restore and then fails on the next statement. Each step is followed by therds_task_statuscall that has to report complete before the next one runs. - There is no restoring over the top. RDS has no equivalent of
WITH REPLACE. The target has to be dropped first, or restored under a different name. - None of it works without
SQLSERVER_BACKUP_RESTOREon the instance’s option group, and an IAM role that can read the bucket. That is at the top of the script because it is the first thing to check and the last thing anybody remembers.
Paths become S3 ARNs. Where msdb recorded an ARN as the device, the script names the real object and is runnable as written. Where it recorded a Windows path, from a history migrated in from elsewhere, the script leaves a placeholder and shows the recorded path beside it as a comment. It will not dress a Windows path up as an ARN.
Backup scripts change too. The right-click menu offers a differential rather than a log backup on RDS, because rds_backup_database takes FULL and DIFFERENTIAL and has no log equivalent.
What the estimate means on RDS
The times are still the cost of restoring a backup out of S3, and they are measured from this database’s own backup rates. They do not cover:
- An RDS point-in-time restore, which rebuilds the whole instance onto a new endpoint and is driven from the console or
aws rds restore-db-instance-to-point-in-time. Nothing inside SQL Server drives it, and its timing has little to do with the numbers on this page. The generated script names this path where log backups are involved, because it is often the real answer. - A Multi-AZ failover, which is usually far faster than any restore.
The chart footer says so on RDS rather than leaving the estimate to be read as the whole story.
SQL Server on EC2 is not RDS. It is an ordinary instance, restores the ordinary way, and gets the ordinary script.
What this report does not do
- It does not verify your backups. No
RESTORE VERIFYONLY, no test restores, no touching backup files. It readsmsdbandsys.databasesand nothing else. - It does not convert data loss into rows or bytes. Nobody sampled a log generation rate, so the report will not pretend to have one. Exposure stays in time.
- It does not model an Availability Group failover. Where you have a synchronous secondary, failing over is usually far faster than any restore, and the real RTO is not on this page. The footer says so.
How to read the report
- Look left first. How much data is at risk right now? Is the band past the RPO target?
- Look at the total on the right. Is it inside the RTO target?
- Find the widest band. That is what to fix. If it is Replay logs, the problem is the number of files, not their size – a less frequent log backup, or a differential in the middle of the day, cuts it directly.
- Compare the lanes. If Last full is comfortably inside target and Latest is not, you have a decision to make in advance rather than at 3am.
Common patterns
A huge Replay logs band. Log backups every minute and no differential. The per-file cost dominates and the bytes barely matter. Add a daily differential.
Restore full is nearly the whole bar. A large database and a short chain. The only levers are faster storage, more backup files in parallel, or a differential strategy.
Latest and Last full are almost the same width. The chain is short. Recovery is cheap and there is nothing to tune here.
The lane reads Nothing to restore from. Stop reading the rest of the report and take a backup.
Related reports
| Report | Why you would go there |
|---|---|
| Backup Status | The same exposure question across every database on the instance. |
| Backups | Backup sizes over time for this database. |
| Backup Time Frames | When the backup jobs actually run. |
| Files | How big the database is, which is what the full restore is moving. |
| Last DBCC CheckDB Known Good | Whether what you would restore was ever verified. |
Frequently asked questions
Is the recovery time accurate? It is an estimate and the report says so on the chart. The data volumes are exact and the throughput is measured from this database’s own backups. What is assumed is how much slower a restore runs than a backup, and the fixed costs at each end. Time one real restore, set the restore factor to match, and the estimate becomes good.
Why not measure the restore time directly? There is nothing to measure. msdb.dbo.restorehistory records the moment a restore finished and never how long it ran, so no restore rate exists on the instance to read.
Why is my SIMPLE database not showing a log stage? SIMPLE recovery cannot take log backups at all, so the finest recovery point it can offer is its last full or differential. It is also judged against your full backup cycle rather than the log RPO target, so it is not painted red for a decision somebody made deliberately.
Where does the RPO target come from? The same setting the instance Backup Status report uses, so the two pages always agree. The RTO target is set on this page’s toolbar.
The restore script does not work on my RDS instance. Check that this report detected RDS at all: it looks for the rdsadmin database. If it did, the script uses the rds_ procedures and the most common reasons it still fails are the option group missing SQLSERVER_BACKUP_RESTORE, an IAM role that cannot read the bucket, or the target database still existing. See On Amazon RDS.
Why does this page read the whole instance? It runs the same query the Backup Status report runs and picks one database out of the answer. A separate single-database query would be faster and would eventually disagree with the first one about the same server.
The log file count on the chart is bigger than the number of rows in the grid. Why? Log backups taken too close together to draw apart are summarised into one row. The count is correct; the list is abbreviated, and the summarised row says by how much.