−50%

50% off your first month on any paid plan · code WELCOME50 · until Nov 30

Start free →
Guide

Automate database backups with cloud cron

Automate database backups with cloud cron

Most backup scripts break without telling you. This guide covers the full pipeline — dump, compress, upload, schedule — and how to add monitoring so silent failures don't cost you data.

If you can SSH into your server, run ls -la /var/backups/ right now. Check the dates. If the newest file is older than yesterday, keep reading.

Most backup scripts break silently. The cron job runs, exits with code 0, and uploads a file to S3. But the file might be empty. The database might have outgrown /tmp mid-dump. mysqldump writes a truncated file, gzip compresses it without complaint, and you get a 240-byte archive that looks fine until someone needs a restore.

We've fixed more broken backup setups than we'd like to admit. The pattern is always the same: script runs, nobody checks, months pass. This guide covers the full pipeline: dump, compress, upload, schedule with cloud cron, and catch silent failures before they cost you data.

Five stages of a backup that actually works#

A reliable backup has five stages. Skip any one, and you'll only find out when you need the backup most.

  1. Dump the database to a file
  2. Compress the dump (gzip, zstd)
  3. Upload to remote storage (S3, B2, GCS)
  4. Trigger the whole thing on a schedule
  5. Monitor that every step actually completed

Most tutorials stop at step 3. That's where backups quietly break.

Dump scripts that report failure#

The usual approach (mysqldump > file.sql) hides errors. If the dump fails halfway, the shell still exits 0. You need pipefail to catch that.

MySQL / MariaDB

#!/bin/bash
set -euo pipefail

DB_NAME="myapp_production"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DUMP_FILE="/tmp/${DB_NAME}_${TIMESTAMP}.sql.gz"

mysqldump \
  --single-transaction \
  --routines \
  --triggers \
  --quick \
  "$DB_NAME" | gzip > "$DUMP_FILE"

# Check if dump produced a real file (not just gzip headers)
FILE_SIZE=$(stat -f%z "$DUMP_FILE" 2>/dev/null || stat -c%s "$DUMP_FILE")
if [ "$FILE_SIZE" -lt 1024 ]; then
  echo "ERROR: Dump file suspiciously small (${FILE_SIZE} bytes)" >&2
  exit 1
fi

echo "Dump complete: $DUMP_FILE ($FILE_SIZE bytes)"

The key line is set -euo pipefail. Without it, a failed mysqldump piped into gzip still returns 0. Bash reports the exit code of the last command in the pipe. pipefail changes that: if any command fails, the whole pipe fails.

After the dump, the size check catches truncated files. Any real database compressed is at least a few KB. Under 1 KB means something went wrong.

PostgreSQL

#!/bin/bash
set -euo pipefail

DB_NAME="myapp_production"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DUMP_FILE="/tmp/${DB_NAME}_${TIMESTAMP}.sql.gz"

pg_dump \
  --format=custom \
  --compress=6 \
  --no-owner \
  "$DB_NAME" > "$DUMP_FILE"

FILE_SIZE=$(stat -f%z "$DUMP_FILE" 2>/dev/null || stat -c%s "$DUMP_FILE")
if [ "$FILE_SIZE" -lt 1024 ]; then
  echo "ERROR: Dump file suspiciously small (${FILE_SIZE} bytes)" >&2
  exit 1
fi

echo "Dump complete: $DUMP_FILE ($FILE_SIZE bytes)"

We always use --format=custom for production. It compresses automatically, and pg_restore can pull a single table from it if needed. Plain SQL works too, but custom is smaller and more flexible.

Upload to remote storage#

A backup on the same server as the database protects against nothing. Server dies, you lose both.

S3 and S3-compatible storage

# AWS S3
aws s3 cp "$DUMP_FILE" "s3://myapp-backups/db/${DB_NAME}_${TIMESTAMP}.sql.gz"

# Backblaze B2 (S3-compatible)
aws s3 cp "$DUMP_FILE" "s3://myapp-backups/db/${DB_NAME}_${TIMESTAMP}.sql.gz" \
  --endpoint-url https://s3.us-west-002.backblazeb2.com

# Verify upload
aws s3 ls "s3://myapp-backups/db/${DB_NAME}_${TIMESTAMP}.sql.gz"
if [ $? -ne 0 ]; then
  echo "ERROR: Upload verification failed" >&2
  exit 1
fi

Backblaze B2 costs $0.005/GB vs S3's $0.023/GB. For a 2 GB daily dump kept 30 days (60 GB total): $0.30 on B2, $1.38 on S3. B2 also gives you free egress up to 3x your stored data.

Retention and cleanup

Without cleanup, storage grows forever. Two options:

# Delete backups older than 30 days
aws s3 ls "s3://myapp-backups/db/" | while read -r line; do
  FILE_DATE=$(echo "$line" | awk '{print $1}')
  FILE_NAME=$(echo "$line" | awk '{print $4}')
  if [[ $(date -d "$FILE_DATE" +%s) -lt $(date -d "30 days ago" +%s) ]]; then
    aws s3 rm "s3://myapp-backups/db/$FILE_NAME"
  fi
done

Or set S3 lifecycle rules to expire objects automatically. Lifecycle rules are more reliable because they run inside AWS, not inside your script.

The complete backup script#

All pieces together: dump, validate, upload, clean up, ping monitoring:

#!/bin/bash
set -euo pipefail

# Config
DB_NAME="myapp_production"
S3_BUCKET="myapp-backups"
RETENTION_DAYS=30
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DUMP_FILE="/tmp/${DB_NAME}_${TIMESTAMP}.sql.gz"
S3_PATH="s3://${S3_BUCKET}/db/${DB_NAME}_${TIMESTAMP}.sql.gz"

# Dump
echo "[$(date)] Starting backup of $DB_NAME"
mysqldump --single-transaction --routines --triggers --quick "$DB_NAME" | gzip > "$DUMP_FILE"

# Validate
FILE_SIZE=$(stat -f%z "$DUMP_FILE" 2>/dev/null || stat -c%s "$DUMP_FILE")
if [ "$FILE_SIZE" -lt 1024 ]; then
  echo "[$(date)] ERROR: Dump too small (${FILE_SIZE} bytes)" >&2
  rm -f "$DUMP_FILE"
  exit 1
fi

# Upload
aws s3 cp "$DUMP_FILE" "$S3_PATH" --quiet
echo "[$(date)] Uploaded to $S3_PATH ($FILE_SIZE bytes)"

# Cleanup local
rm -f "$DUMP_FILE"

# Ping monitoring endpoint (WatchCron Cloud Cron callback)
curl -s -o /dev/null "https://ping.watchcron.com/YOUR_CHECK_ID"

echo "[$(date)] Backup complete"

That last curl pings a monitoring endpoint when the backup finishes. Combined with cron job logging, you get both a local trail and a remote signal. If the ping never arrives, you get an alert.

No server? No crontab? No problem#

Everything above assumes SSH access. But AWS RDS, PlanetScale, Supabase, DigitalOcean Managed Databases don't give you shell access. No crontab to edit.

Two ways around it:

First: a cheap VPS ($4/month Hetzner CX12, $5 DigitalOcean droplet) that does nothing but run backup scripts. It connects to the managed database remotely, dumps, uploads. Simple, but now you maintain a server.

Second: write a backup endpoint in your app and trigger it with an external cron service. Cloud Cron hits your endpoint on schedule, your app runs the backup, and the HTTP response tells you whether it worked. No extra server needed.

Triggering backups with WatchCron Cloud Cron#

Two modes, depending on your setup.

You have a server (crontab + monitoring)

Your script already runs via crontab. Add a heartbeat ping at the end (the curl line above). Create a check in WatchCron with the same schedule. If the heartbeat doesn't arrive on time, you get an alert via Slack, email, or Discord.

This catches three things crontab misses: the script fails before the ping, the cron daemon doesn't fire the job at all, or the script hangs on a timeout.

You don't have a server (Cloud Cron as trigger)

Create a backup endpoint in your app:

// Express.js
app.post('/api/backup', async (req, res) => {
  if (req.headers['x-api-key'] !== process.env.BACKUP_API_KEY) {
    return res.status(401).json({ error: 'unauthorized' });
  }

  try {
    await runDatabaseBackup();
    res.json({ status: 'ok', timestamp: new Date().toISOString() });
  } catch (err) {
    console.error('Backup failed:', err);
    res.status(500).json({ status: 'error', message: err.message });
  }
});

Same idea in Laravel:

# Laravel
Route::post('/api/backup', function (Request $request) {
    abort_unless($request->header('X-Api-Key') === config('backup.api_key'), 401);

    try {
        Artisan::call('backup:run', ['--only-db' => true]);
        return response()->json(['status' => 'ok']);
    } catch (\Exception $e) {
        report($e);
        return response()->json(['status' => 'error'], 500);
    }
})->middleware('throttle:1,5');

In WatchCron, create a Cloud Cron job: POST to your endpoint, add the API key header, set the schedule. A 200 response means success. A 500 or timeout triggers an alert. No separate heartbeat needed.

What to monitor beyond "did it run"#

"Backup ran" and "backup worked" are different things. Three ways backups look fine but aren't:

Truncated dump. Disk fills up mid-write. The file is valid gzip but contains half your tables. The size check in the script catches this.

Wrong database. Connection string points somewhere else, or someone changed the password. mysqldump of an empty database produces a small but valid file. Set a minimum size based on your real DB. If production compresses to 500 MB, anything under 400 MB is a red flag.

Stale replica. You back up from a read replica that stopped syncing two weeks ago. File looks right, but the data is old. Monitor replication lag separately. We learned this the hard way.

Include file size in the response so monitoring can track trends:

res.json({
  status: 'ok',
  database: DB_NAME,
  file_size_mb: (fileSize / 1024 / 1024).toFixed(1),
  duration_seconds: elapsed,
  uploaded_to: s3Path
});

If backup takes 4 minutes one day and 45 the next, that's worth looking into before it becomes a timeout.

Picking the right schedule#

Daily at 3 AM works for most apps. Two things to think about.

How much data can you lose? If the database dies at 2:59 AM and the last backup was yesterday at 3 AM, that's 24 hours gone. For a blog, fine. For an e-commerce store with orders coming in around the clock, back up every 6 hours. Use a cron expression builder to get the syntax right.

When is traffic lowest? Backups add I/O pressure. A SaaS serving US customers is quiet at 4 AM EST. Same app serving Australia peaks at that exact time. Check analytics, don't guess.

# Daily at 3 AM UTC
0 3 * * *

# Every 6 hours
0 */6 * * *

# Weekdays at 2 AM, weekends at 6 AM
0 2 * * 1-5
0 6 * * 0,6

Test your restores#

A backup you've never restored is just a file. Hope is not a recovery strategy.

Test at least monthly. Spin up a throwaway database, restore, run one query:

# Test restore (MySQL)
mysql -e "CREATE DATABASE backup_test"
gunzip < latest_backup.sql.gz | mysql backup_test
mysql backup_test -e "SELECT COUNT(*) FROM users"
mysql -e "DROP DATABASE backup_test"

If the count roughly matches production, the backup works. We test our own database every two weeks. Found a corrupted dump once, six months in. Glad we caught it before someone actually needed that data.

When cloud cron is overkill#

One app, one VPS, one database? Crontab plus a heartbeat ping is enough. Set the cron schedule, add the curl ping, verify alerts work. Done.

Cloud Cron makes sense when you have managed databases without shell access. Or backups across multiple services and want one dashboard for all of them. Or when you've been burned by a backup that broke three months ago and nobody noticed.

For a side project, keep it simple. For real customer data, a dead man's switch on your backup job costs nothing and saves everything when it matters.

Frequently asked#

Depends on how much data you can afford to lose. Daily works for most apps. E-commerce stores with steady orders often back up every 4-6 hours. If 24 hours of lost data is unacceptable, shorten the interval.

Yes. Write a backup endpoint in your app (Laravel, Express, Django) and trigger it with a cloud cron service. It sends a POST request on schedule, your app runs the backup. No separate server needed.

Backblaze B2 at $0.005/GB per month. A 2 GB daily backup kept 30 days costs $0.30/month. S3 is about $1.38 for the same. Both use the S3 API, so switching is a one-line change.

Check file size after each dump. A real database compressed is at least several KB. Return the size in your endpoint's response so monitoring can track it. If the number drops suddenly, something broke.

Without monitoring, nothing happens. The backup stops and nobody knows until someone needs a restore. Add a heartbeat ping at the end of your script, or use a cloud cron service that checks the response code. A 500 or timeout triggers an alert.
Written by Vitalii Holben

Founder of WatchCron. Full-stack developer building monitoring tools that help teams keep cron jobs, uptime, and SSL certificates under control. Based in Europe, shipping code since 2014.

Start monitoring in under 2 minutes.

Free plan includes 20 checks · No credit card required
Pricing Docs
Log in Start free →