master Backup and Rebuild Readiness

Overview

If this server were gone tomorrow, how much of what is in master would come back?

Everything the rest of this suite lists lives in one database, and one database is either in a backup or it is not.

The question this page asks is not whether master has ever been backed up, which is easy and usually yes. It is whether the backup that exists is newer than the things it would have to restore: a master backup taken eleven days ago does not contain the forty one logins somebody created since, and nothing on an instance says so until the day it matters.

The master Backup and Rebuild Readiness page: the last backup of master against everything newer than it
The whole page. The header line names the backup and its age, the lanes put every kind of server level change on the same axis as it, and the grid turns that into a yes or no per item.

Where to find it

Expand a server in the tree, expand the master database, then Master → Backup and Rebuild.

The comparison is drawn rather than calculated

Backups of master go in one lane, and each kind of server level change goes in its own lane over the same axis.

So the gap between the last backup and the newest change is a distance on the screen rather than a date the reader has to subtract.

Two things this page cannot see, and says so

Role membership has no date

A login added to sysadmin yesterday moves nothing in sys.server_role_members, because that view has no create or modify column at all.

master Change Ledger reads the default trace for exactly this reason.

Endpoints have no date either

sys.endpoints carries a state and a payload and nothing that says when anybody made it.

The facts a restore actually needs

They are on the page as well, because at three in the morning the first thing that stops a restore of master is not the file.

It is that a master backup can only be restored to an instance at the same build, and nobody wrote down what build it came from.

So the page carries the build, the edition, the collation, the single user start syntax and the -m switch, and the toolbar copies them as a note.

Reading the chart

The lanes: backups of master in the top lane, each kind of server level change below it
One lane per kind of change. The header line counts what the picture shows: twenty four server level changes newer than a backup taken twenty nine days ago.

Backups of master are the top lane. Every other lane is a kind of server level change drawn against the same axis, so the marks that sit to the right of the last backup are the things a restore would not bring back.

Each lane label carries its own count, and the lanes with something newer than the backup say so under the name rather than leaving the reader to measure the gap.

Reading the grid

The grid, newest first, with the backup of master as a row of its own
Ordered newest first, with the backup of master listed as a row like any other. Everything above that row answers no in the In the last backup column, and everything below it answers yes.
Column What it is
What The kind of thing: a login, a server role grant, a linked server, a credential, an endpoint, a certificate, a startup procedure.
Name The object itself.
When Its create or modify date, where it has one.
In the last backup Yes or no. This is the column the page exists for.
Detail Why an item has no date, or what would be lost with it.

The toolbar

Control What it does
Change ledger Opens master Change Ledger.
User objects Opens master User Objects, which a rebuild also loses.
Keys and certificates Opens master Keys and Certificates.
Copy the restore notes Copies the build, edition, collation and restore syntax.

Where the data comes from

Source What it gives
msdb.dbo.backupset The backups of master, for the lane.
sys.server_principals Logins, with create_date and modify_date.
sys.server_role_members Role membership, which has no date.
sys.servers Linked servers, with modify_date.
sys.credentials Credentials, with create_date and modify_date.
sys.endpoints Endpoints, which have no date.
sys.certificates, sys.symmetric_keys Keys and certificates in master.
sys.procedures with is_auto_executed Startup procedures.
SERVERPROPERTY The build, edition and collation for the restore notes.
Report Why you would go there
master Change Ledger The changes that have no date column, read from the default trace instead.
master User Objects The other thing a rebuild of master loses.
master Keys and Certificates Whether the certificates were ever exported.
Backup Status Whether master is being backed up at all.
master Service Facts The build and edition, in their own page.

Frequently asked questions

master is backed up nightly. Is that enough? Read the In the last backup column. Nightly is enough for anything created before last night. The page tells you what was created since.

Why is role membership shown with no date? Because sys.server_role_members has no date columns. The page says so rather than leaving a blank cell that looks like a permission problem.

Can I restore this master backup onto a new server? Only to an instance at the same build. That is the first thing that stops a restore of master, and the restore notes on the toolbar carry the build this one came from.

What does a rebuild of master lose? Everything on this page that is not in a backup, plus everything on master User Objects.