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.
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]
How you want to hear about failures: [e.g. email, a chat webhook]
The script must
Read credentials from a protected option file or environment, never from the command line or the script.
Take a consistent dump without locking writes: single-transaction for InnoDB, or the native dump tool for PostgreSQL.
Compress, name the file with the database and an absolute timestamp, and write a checksum.
Fail loudly: stop on any error, check the dump is not empty, and send the alert chosen.
Copy the file off the server and confirm the copy's checksum.
Delete only files older than the retention rules, matching the exact file name pattern.
Log each run with size and duration.
Output format
The full script in a code block with short comments.
File permissions and the option file contents with placeholder values.
A cron line to run it nightly, with a lock so two runs never overlap.
| Copy type | Kept | Rule |
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.
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.
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.