Sefism is free for learners. We are here to help you grow, one step at a time.Join for free
Sefism.
Cloud & DevOps

Automated Backup Restore Drills for Small Business Databases

A backup service for small businesses that does not stop at taking backups: it restores them on a schedule into a throwaway container, checks the data and reports how long recovery really takes. A practical cloud and DevOps final year project for shops, clinics and agencies running MySQL, PostgreSQL or MongoDB on a single server.

Topics
Difficulty

Beginner

Achievable by one student in a semester with familiar tools.
Effort

1 semester · 1 to 2 students

Deliverables

6 to ship

3 optional extras

Suggested stack

Bash or PythonDockerPostgreSQL, MySQL, MongoDBresticS3-compatible storageage encryptionSQLite for run historyEmail and WhatsApp alerts
A suggestion, not a requirement. Swap anything for what you already know.

What you should ship

  • An agent that takes scheduled, encrypted backups of PostgreSQL, MySQL and MongoDB databases plus an uploads folder, and pushes them to off-site S3-compatible storage with a retention policy.
  • A restore drill runner that pulls the latest backup, restores it into a disposable Docker container and records whether it succeeded and how many seconds it took.
  • Data checks after each restore: row counts per table against the source, checksums of a sample of rows, and optional owner-defined SQL checks such as "orders from yesterday exist".
  • A plain-language monthly report per client showing last good backup, last successful restore, measured recovery time and any failed drills.
  • Alerting by email and one chat channel when a backup is missing, a drill fails or storage costs jump.
  • An evaluation report from at least three real or realistic databases, including deliberately corrupted backups that the drill must catch.

If you have time left

  • Point-in-time recovery for PostgreSQL using WAL archiving, with the drill restoring to a chosen timestamp.
  • A small web dashboard where an agency sees every client database, its drill history and a traffic-light status.
  • Detection of "silent shrinkage": alert when a table that normally grows loses a large share of rows between backups.

The problem: backups nobody has ever restored

Most small businesses that run their own server have some kind of backup. A cron job runs mysqldump every night, the file lands in a folder, and someone copies it to Google Drive when they remember. The first time anyone tries to restore it is the day the disk dies or a developer runs a DELETE without a WHERE clause. That is when they find the dump is empty, truncated, encrypted with a lost password, or missing the uploads folder that held every invoice PDF.

Clinics, online shops, schools and small agencies are hit hardest because they have no database administrator. The person responsible is often a freelancer who set things up two years ago. In places with unstable power, such as load-shedding in Pakistan, sudden shutdowns make corruption more likely, which makes tested backups more important, not less.

What already exists and the gap

  • pgBackRest is a serious PostgreSQL backup tool with verification, but it is PostgreSQL only and assumes an administrator who reads its documentation.
  • restic and BorgBackup handle encrypted, deduplicated file backups and have a check command, but they verify the archive, not whether the database inside it actually starts and contains yesterday's orders.
  • SimpleBackups is a hosted service that schedules database and file backups for many engines, priced for teams already paying for cloud tools.
  • Small open source projects such as RestoreDrill prove the idea for PostgreSQL alone.

The gap is a cheap, multi-engine tool that treats the restore as the product: it restores on a schedule, checks the data, measures recovery time and explains the result to a non-technical owner.

What you build

An agent installed on the client's server and a small control service.

  • Backup jobs: per database, a schedule, retention rule and destination (Backblaze B2, Cloudflare R2, Wasabi or MinIO). Dumps are compressed, encrypted with age and uploaded through restic.
  • Restore drills: on a separate schedule the runner downloads the latest snapshot, starts a matching database version in Docker, restores, runs checks, records timings and throws the container away.
  • Checks: row counts compared with counts captured at backup time, checksums of sampled rows, and owner-written SQL assertions.
  • Reports and alerts: a monthly summary per client, plus immediate alerts when a job misses its window or a drill fails.

Roles are simple: the operator (freelancer or agency) configures clients; the business owner only receives reports.

The technically interesting part

The hard part is proving a restore is correct without copying production data around carelessly. You need a manifest captured at backup time (table list, row counts, sample checksums) that travels with the backup, so the drill compares like with like even though the live database has moved on. You also need to match database versions: restoring a MySQL 8 dump into MySQL 5.7 fails for reasons that have nothing to do with the backup. Show your evaluators the manifest format and how the drill decides pass, warn or fail.

How to evaluate it

  • Fault injection: produce at least ten broken backups (truncated file, wrong key, missing table, empty dump, wrong engine version) and report how many the drill catches. The target is all of them.
  • Recovery time: measured restore time for databases of different sizes, for example 100 MB, 1 GB and 5 GB, on a small VPS.
  • Overhead: CPU and memory used on the production server during backup windows.
  • Usability: give the monthly report to five non-technical owners and check whether they can say, unaided, when their data was last proven recoverable.

Suggested plan

  1. Weeks 1 to 3: backup jobs for PostgreSQL and MySQL, encryption and upload with retention.
  2. Weeks 4 to 6: the manifest format and the Docker restore runner.
  3. Weeks 7 to 9: data checks, MongoDB support and alerting.
  4. Weeks 10 to 12: reports, fault-injection suite and measurements.
  5. Weeks 13 to 14: write-up and a live demo that restores a broken server.

Path to a product

The first customer is a web agency or freelancer who looks after ten to thirty client servers and wants proof to show clients. Charge a small monthly fee per protected database, with local payment options such as JazzCash or Easypaisa alongside cards so it works in Pakistan and elsewhere. The monthly report is the sales tool: agencies forward it to their clients as evidence of value. Later add hosted storage so customers do not need their own bucket.

Scope warning

Do not build your own backup format, deduplication or encryption. Use restic and age. Do not try to support every database engine; three is plenty. Do not build a full web dashboard before the drill runner is reliable, because a pretty screen over untested restores defeats the point.

Learn what you need on Sefism

Get help with this idea

The most common trap here is spending the semester on backups and never getting to the drills. A mentor can help you pin down the scope, choose a stack you can finish with, and design an evaluation your examiners will respect. You build it; we help you think it through. New here? Create a free account first.

Ideas and guidance, not finished projects

These are project ideas and scoping guidance, published free for students to use as a starting point. I do not build, write, or sell final-year projects, and I do not complete coursework for anyone. Take an idea, make it yours, and build it.

Not sure this one fits you?

I help students pick an idea that matches their skills and their deadline, then scope it down until it is finishable. Guidance only, never done for you.

Discuss this idea
Work with me

Stuck on something specific?

Writing only gets you so far. If you want an answer to your situation rather than the general case, book a session and we will work through it together. Sessions are free for approved Sefism members, and a few slots open each week.

Book a free sessionSee all services

Follow along

New writing, resources and project ideas land here first.