Backup Time Frames

Overview

Backup Size answers how big. Backup Time Frames answers when, and for how long.

Every backup of one database over the last fourteen days is drawn as a bar on a timeline, positioned where it ran and as wide as it took. Grouped into lanes by backup type, that makes three things visible at once that no grid of start times shows.

  • Where the backup window actually is, as opposed to where the schedule says it is.
  • Whether backups overlap each other, or overlap something else that matters.
  • Whether a backup is getting slower, from the bars getting wider day by day.
The Backup Time Frames report
Fourteen days of backups. Bar position is when it ran, bar width is how long it took.

Where to find it

A database level report. Expand the database → Real Time → Backup Time Frames.

The page title reads Backup Time Frames for <database name>.

The node is not offered for tempdb.

Requirements

  • Access to msdb, where the backup history lives.
  • Backup history covering the last fourteen days. A msdb cleanup job with a shorter retention leaves this page showing less than a full window.

Reading the timeline

The chart covers fourteen days, counted back from the start of today, with day dividers across the top.

Each bar is one backup. Where it starts and ends on the axis is when it ran; its width is its duration. Bars are grouped into lanes by backup type, so full, differential, log and any COPY_ONLY variant each get their own row.

Hovering a bar gives the type, the start, the end, the duration and the file it was written to.

A very short backup is a very narrow bar, and a log backup that takes two seconds is close to a line. That is the honest picture: on a busy database the log lane reads as a row of ticks and the full lane as a handful of wide blocks, which is exactly the contrast the page is for.

Reading the grid

The grid below the timeline lists every backup in the last fourteen days, newest first.

Column What it is
Start Time When the backup started.
End Time When it finished.
Duration How long it took.
Backup Size Uncompressed size.
Compressed Size actually written.
Ratio Compressed as a percentage of uncompressed. Lower is better.
Backup Type Full Database, Differential or Log, with (COPY_ONLY) appended where it applies.
Description The description recorded with the backup.
File Name The physical device it was written to.
The backup time frames grid
The same fourteen days as a list, newest first.

How to read the report

  1. Look at the full backup lane first. Those are the wide bars and they are what has to fit in a window.
  2. Look for bars getting wider day by day. That is the answer to when does this stop fitting, and it arrives well before the night it stops fitting.
  3. Check what else runs at the same time. Job Schedules shows the maintenance jobs; a full backup overlapping an index rebuild is two heavy workloads on one set of spindles.
  4. Check the gaps in the log lane. Regular ticks with a hole in them is a log backup job that failed, and every hole is unprotected time.
  5. Compare this database’s window with the others on the instance. Several databases all backing up at 02:00 is one instance doing four backups at once.

Common patterns

A full backup lane where every bar is wider than the last. Growth. Extrapolate the width and you have the date the backup no longer fits the window. Backup Size shows the same trend in bytes.

Log ticks that stop overnight and resume in the morning. Either the log backup job is disabled overnight, which leaves that time exposed, or it has been failing. Failed Jobs distinguishes them.

Two lanes with bars stacked on top of each other. A full backup and a log backup running at the same time. Not an error, and it is worth knowing that the log backup taken during a full covers a different range than you might expect.

A COPY_ONLY lane alongside a full lane. Two different things are backing this database up, usually a native job and a third party tool. Worth confirming which one is the one you would actually restore from.

One bar much wider than its neighbours. A backup that hit contention, a slow target, or a network share that was busy. Check the file name column: a backup written to a different destination is usually the explanation.

An empty page. No backups in the last fourteen days. That is the most important thing this page ever tells you. Backup Status puts a number on the exposure.

Where the data comes from

  • msdb.dbo.backupset for the backups, their types and their timings.
  • msdb.dbo.backupmediafamily for the file each was written to.

Both the timeline and the grid are restricted to roughly the last fourteen days, so the page stays readable rather than compressing a year into the same width.

Nothing is stored by this page.

Report Why you would go there
Backup Size The whole retained history as a size trend rather than fourteen days on a clock.
Backup Status Whether the backups add up to a restore, and what would be lost right now.
Recovery Exposure How long the restore would take.
Job Schedules What else runs in the same window.
Job History The job that takes these backups, run by run.
Disk Latency by Hour by Day What the backup window costs the storage.

Frequently asked questions

Why only fourteen days? Because the point of the page is the shape of a normal night. A year on the same axis would be a solid block.

Why is a bar so thin I can barely see it? Because the backup was fast. Bar width is duration, and a two second log backup is two seconds wide.

Why do I see two lanes for what I thought was one backup type? Because one of them is COPY_ONLY, which is tracked as its own type on this page since it behaves differently in a restore.

The page is empty. There have been no backups for this database in the last fourteen days.

How is this different from Backup Size? This is fourteen days positioned on a clock. Backup Size is the whole retained history plotted as size over time.

Why is Backup Time Frames missing on tempdb? Because tempdb is never backed up.