Restore a backup into a scratch database

Every step here exists because the same mistake ruins a company: a restore aimed at the wrong target. The destination is named first, out loud, and nothing touches production at any point.

Steps

1. Say what you are restoring and where, before doing anything. Which backup, from when, into which database on which host. Write it down. If the destination name does not contain the word scratch, restore, or a date, rename it so it does.
2. Create the destination fresh. A new, empty database. Never restore into one that already holds anything, and never into one an application is configured to reach.
3. Check the backup before trusting it: its size, its timestamp, and that it is the full backup rather than an incremental one that needs its base.
4. Restore, and read the errors. A restore that prints errors and exits zero is a partial restore. Count the tables and compare a couple of row counts against what you expect.
5. Redact before anybody uses it, if this copy will be read by more than the person doing the investigation: clear or hash the columns holding email addresses, names, tokens and payment details. A production copy sitting in a scratch environment is a production data store with none of the controls.
6. Set an expiry, and honour it. Note when the copy will be deleted, and delete it. The most common way customer data escapes is a restore nobody removed.

Never, under any circumstances

• Point a restore at the production database, or at any database an application currently reads.
• Run a restore with credentials that can write to production. Use a role scoped to the scratch host.
• Copy the restored data anywhere else — a spreadsheet, a laptop, a ticket.

Stop and ask a human

Before restoring at all, if the reason is to recover from data loss in production rather than to investigate. Recovery is a decision with a blast radius and it belongs to whoever owns the data.