wowprompt

Database backup script with retention and a restore test

Writes a backup script for a MySQL or PostgreSQL database that dumps safely, compresses, keeps a set number of daily, weekly and monthly copies, copies them off the server, and proves a backup can be restored. Use it when a database has no tested backup, or the only backup is a hosting panel button.

A commented shell backup script, a cron line to run it, the retention rules in a table, a restore test procedure, and a list of failure alerts
The output this prompt produced, unedited.

The prompt

What to give

  • Database engine and version: [e.g. MySQL 8, PostgreSQL 16]
  • Database size: [e.g. 3 GB]
  • Server OS and shell: [e.g. Ubuntu with bash]
  • Where copies should go: [e.g. a second server over SSH, an object storage bucket]
  • Retention wanted: [e.g. 7 daily, 4 weekly, 6 monthly]
  • How you want to hear about failures: [e.g. email, a chat webhook]

The script must

  1. Read credentials from a protected option file or environment, never from the command line or the script.
  2. Take a consistent dump without locking writes: single-transaction for InnoDB, or the native dump tool for PostgreSQL.
  3. Compress, name the file with the database and an absolute timestamp, and write a checksum.
  4. Fail loudly: stop on any error, check the dump is not empty, and send the alert chosen.
  5. Copy the file off the server and confirm the copy's checksum.
  6. Delete only files older than the retention rules, matching the exact file name pattern.
  7. Log each run with size and duration.

Output format

  1. The full script in a code block with short comments.
  2. File permissions and the option file contents with placeholder values.
  3. A cron line to run it nightly, with a lock so two runs never overlap.
  4. | Copy type | Kept | Rule |
  5. Restore test steps: restore last night's file into a scratch database, count rows in three key tables, compare with live, then drop the scratch database.
  6. A monthly reminder line: a backup that has never been restored is not a backup.

Rules

  • Never write real passwords, hosts or bucket names; use [placeholders].
  • Do not restore over the live database in any example.
database backup scriptmysql backuppostgresql backupbackup retentionrestore testoffsite backup

How to run this

Copy the prompt and paste it into Gemini, then set the aspect ratio to 3:4. No reference photo is needed for this one.

The prompt is written in Markdown with each part under its own heading, which is what the copy action hands over. Change one section at a time, keeping the rest intact, so you can tell which change did the work.

Related prompts

All productivity