Module 58: Database Administration

Why each option is right or wrong

• “Yes, so the app never hits a permission error” (Incorrect): Avoiding errors this way removes an important safety net.
• “Yes, as long as the password is long and random” (Incorrect): A strong password doesn't limit what a compromised app can do.
• “No, apps should connect without any password at all” (Incorrect): Apps need credentials; they should belong to a limited role.
• “No, use a role with only the permissions it needs” is correct.

  A superuser can drop tables, read every database and change settings. If the app is compromised, or a bug runs the wrong query, the damage is unlimited. A dedicated role that can only read and write the app's tables limits what can go wrong.

  • Separate roles: app, migrations, read-only reporting.
  • Least privilege: each gets only what its job needs.

📖 Revise: Lesson 1: Users, roles and permissions

Why each option is right or wrong

• “Read-only access makes reports load faster” (Incorrect): Speed isn't the reason; safety is.
• “Reporting tools can't write data even with full access” (Incorrect): Many can run any SQL they're allowed to.
• “It hides the data from the report's readers” (Incorrect): They can still read; they just can't change anything.
• “A mistake can't then change or delete data” is correct.

  Reports are often written quickly by different people. With read-only access, a wrong query can only read too much or too little; it can't update or delete anything.

  • Even safer: point reports at a read replica.
  • Also limit: which tables and columns they can read.

📖 Revise: Lesson 1: Users, roles and permissions

Why each option is right or wrong

• “Edit the original migration file and run it again” (Incorrect): Databases that already ran it won't rerun it, so copies drift apart.
• “Delete the migration file and fix the database by hand” (Incorrect): Manual fixes aren't recorded and won't reach other copies.
• “Drop the test database and ignore the typo in production” (Incorrect): The typo would still be in production's structure.
• “Write a new migration that fixes it” is correct.

  Once a migration has run anywhere, changing the file means that database and others no longer agree with the history. A new migration that corrects the mistake runs everywhere in the same order.

  • Unrun migrations: can still be edited before anyone applies them.
  • History: stays a true record of every change.

📖 Revise: Lesson 2: Migrations

Why each option is right or wrong

• “Add it as NOT NULL in one step and accept the error” (Incorrect): Existing rows have no value, so the change fails or locks the table.
• “Copy the whole table, add the column, then swap them over” (Incorrect): Possible, but heavier and riskier than staged changes.
• “Add it only in the app's code, not in the database” (Incorrect): The database then can't enforce the rule.
• “Add it nullable, fill values, then add NOT NULL” is correct.

  Adding a NOT NULL column directly fails if existing rows have no value, and filling millions of rows in one step can lock the table. Doing it in stages, nullable first, then filling in batches, then adding the constraint, keeps the app running.

  • Called: expand and contract.
  • Deploy code: that writes the new column before making it required.

📖 Revise: Lesson 2: Migrations

Why each option is right or wrong

• “Backups on the same server make the database slower” (Incorrect): Speed isn't the main risk; losing both together is.
• “Backups can't be read on the server that made them” (Incorrect): They can; the issue is losing both at once.
• “Nothing; it's the most convenient and safe place for them” (Incorrect): Convenient, but one failure takes out both.
• “If the server fails or is hacked, backups go with it” is correct.

  A backup on the same machine protects against a bad query, but not against disk failure, fire, ransomware or a deleted server. Keep copies somewhere separate, ideally in another location and account.

  • 3-2-1 rule: 3 copies, 2 types of storage, 1 off-site.
  • Protect them: limit who can delete backups.

📖 Revise: Lesson 3: Backups

Why each option is right or wrong

• “How long it takes to restore the database” (Incorrect): That's the recovery time objective (RTO).
• “The number of backup copies you keep” (Incorrect): That's retention, not RPO.
• “The password used to encrypt the backups” (Incorrect): RPO is a time target, not a password.
• “The most recent data you can afford to lose” is correct.

  Recovery point objective: if backups run every 24 hours, you could lose up to a day of data. If that's too much, you need more frequent backups or continuous archiving.

  • RTO: recovery time objective, how long recovery may take.
  • Set them with the business: they drive backup choices.

📖 Revise: Lesson 4: Restoring

Why each option is right or wrong

• “Backups expire unless they're restored once a month” (Incorrect): Backups don't expire; untested ones may simply not work.
• “Restoring makes the backup smaller” (Incorrect): Restoring doesn't change the backup.
• “Hosting providers require it before backups are kept” (Incorrect): It's your safeguard, not a hosting rule.
• “They can fail silently; only a restore proves they work” is correct.

  Backup jobs can produce empty files, miss a database, or use a format the restore tool rejects, all without obvious errors. Restoring to a test server regularly shows the backup is complete and that you know the steps.

  • Schedule it: for example monthly.
  • Time it: to check your RTO is realistic.

📖 Revise: Lesson 4: Restoring

Why each option is right or wrong

• “Yes, a full restore is the quickest way to fix it” (Incorrect): Quick, but it throws away everything since the backup.
• “Yes, as long as the backup is from last night” (Incorrect): Even a recent backup loses this morning's other changes.
• “No, deleted rows can't be recovered from backups” (Incorrect): They can, from a backup restored elsewhere.
• “No, restore elsewhere and copy back only the lost rows” is correct.

  Restoring over production would undo every change since the backup, losing this morning's new orders and updates. Restoring to a separate database and copying back just the 30 customers fixes the mistake without new losses.

  • Check related rows: their orders or notes may need restoring too.
  • Then: find out how the delete happened.

📖 Revise: Lesson 4: Restoring

Why each option is right or wrong

• “They delete the oldest rows to make space” (Incorrect): Databases don't delete your data to free space.
• “They compress all the data automatically” (Incorrect): They don't; writes fail instead.
• “Nothing; databases don't need disk space after setup” (Incorrect): They need space for every write and for their logs.
• “They stop accepting writes, or stop entirely” is correct.

  Databases need disk space for data, logs and temporary work. When it runs out, writes fail and the database may shut down to protect itself, taking the app with it. Monitoring disk space with an alert well before it fills avoids this.

  • Alert at: for example 80% full.
  • Watch for: logs and old backups filling the disk.

📖 Revise: Lesson 5: Everyday care

Why each option is right or wrong

• “Nothing; the provider handles everything” (Incorrect): Providers handle infrastructure; your data and access remain your responsibility.
• “Running your own backups every hour as well” (Incorrect): Extra backups can help, but testing restores and access matter more.
• “Replacing the server's disks when they fail” (Incorrect): Managed services handle the hardware.
• “Testing restores, access control and knowing recovery steps” is correct.

  The provider runs backups, but only you know whether restores contain what you need, who should have access, and how to recover quickly under pressure. Practise the steps before you need them.

  • Also yours: schema design, indexes and query performance.
  • Write a runbook: the recovery steps, in order.

📖 Revise: Lesson 5: Everyday care