Automated Drupal database backups with Drush
Database loss can turn a routine Drupal content update into an emergency, especially when a site carries years of editorial work, memberships, taxonomy and custom application data. Automated backups reduce the interval between failure and recovery, but a file that merely exists on the same server is not enough. A sound setup creates a consistent database dump, verifies it, schedules the job and keeps an off-site copy that remains usable when the web server is unavailable.
Drush provides a practical way to produce that dump through its sql:dump command. The output is a logical SQL export, so it can move between compatible environments and can be compressed without depending on a provider’s snapshot system. It covers the database only: Drupal configuration normally belongs in version-controlled code, while uploaded images and documents need a separate files backup. Treating those layers as one job creates false confidence.
The workflow below assumes shell access, Composer-managed Drupal code and permission to create a protected backup directory. Test the whole process on staging before scheduling production. Keep the Drupal version, Drush version, PHP version and database engine aligned with the project’s support policy, since restoring a dump into a mismatched environment can create migration work at the worst possible time.
Australian teams should account for local operating conditions rather than copy an overseas cron example blindly. Many agencies in Sydney, Melbourne and Brisbane run client sites on cPanel hosting, managed VPS instances or Australian cloud regions, so backup scripts should work across those arrangements. Time zones such as Australia/Sydney and Australia/Melbourne change between AEST and AEDT, while Australia/Brisbane does not observe daylight saving. Backup windows, monitoring and support handovers should follow those realities and any contractual data-residency requirements.
Plan a recoverable backup strategy
Start by defining the acceptable recovery point and recovery time. The recovery point determines how much recent activity may be lost; the recovery time determines how quickly the site must return after an incident. A publishing site may tolerate a 24-hour interval, while a booking system with deadline-driven transactions may need hourly snapshots and a tested off-site process.
A Drush export is a logical backup rather than a storage-level snapshot. It can contain the selected database consistently, compress well and be restored to another server. The trade-off is that large databases can take longer to import. Host snapshots can be faster for a whole-server recovery, but they remain tied to the hosting platform and its retention settings.
Choose one protected directory outside the public web root for temporary and recent dumps, then copy each completed backup to separate storage. Record who owns the process, how long backups are retained and who receives a failed-job alert. Do not use drush sql:sync as a substitute for backups; it is designed to copy one database into another and can overwrite the destination.
Prepare Drush and database access
Install Drush through Composer so its version is resolved against the project’s PHP and Drupal constraints. Check the result and run drush status from the Drupal installation directory. If Composer reports that the current platform cannot satisfy the newest Drush release, use the project’s dependency constraints rather than forcing an incompatible package.
composer require --dev drush/drush
./vendor/bin/drush status
./vendor/bin/drush sql:dump --result-file=/tmp/drupal-check.sql
The final command confirms that Drush can connect and read the database. Remove the temporary file after checking it. For a multisite installation, use a site alias such as @production so the command always selects the intended database. drush sql:connect is also useful for inspecting the effective connection, although its output should not be shared publicly because it may expose sensitive connection details.
Drush normally obtains the active database connection from Drupal’s settings.php. A command-line-only alias can contain its own db-url, but the alias file should be outside version control and readable only by the backup account. Passwords should never be placed in the crontab, a world-readable script or a committed YAML file. A permissions error during sql:dump often indicates that the cron user, the Drupal files owner and the database user have been configured differently.
Build a reliable backup script
A script should fail immediately when an important step fails, create a temporary file and move it into place only after the export completes. The umask setting prevents new files from becoming readable by other accounts on a shared host.
#!/usr/bin/env bash
set -Eeuo pipefail
umask 077
DRUPH=/var/www/example/public/vendor/bin/drush
DRUPH_ROOT=/var/www/example/public
BACKUP_DIR="$HOME/drwell-backups"
RETENTION_DAYS=14
install -d -m 700 "$BACKUP_DIR"
TIMESTAMP="$(date -u +%Y%m%dT%H%M%SZ)"
FINAL_SQL="$BACKUP_DIR/drupal-$TIMESTAMP.sql.gz"
TEMP_SQL="$(mktemp "$BACKUP_DIR/.drupal-$TIMESTAMP.XXXXXX")"
TEMP_GZ="$TEMP_SQL.gz"
cleanup() {
rm -f "$TEMP_SQL" "$TEMP_GZ"
}
trap cleanup EXIT
"$DRUPH" --root="$DRUPH_ROOT" sql:dump --result-file="$TEMP_SQL"
test -s "$TEMP_SQL"
gzip -9 "$TEMP_SQL"
mv "$TEMP_GZ" "$FINAL_SQL"
gzip -t "$FINAL_SQL"
(
cd "$BACKUP_DIR"
sha256sum "$(basename "$FINAL_SQL")" > "$(basename "$FINAL_SQL").sha256"
)
find "$BACKUP_DIR" -maxdepth 1 -type f \
\( -name 'drupal-*.sql.gz' -o -name 'drupal-*.sql.gz.sha256' \) \
-mtime "+$RETENTION_DAYS" -delete
printf 'Backup completed: %s\n' "$FINAL_SQL"
Adjust the paths and retention period before deployment. Linux provides sha256sum; macOS systems generally use shasum -a 256 instead. Run bash -n to check the script’s syntax, execute it manually and confirm that the resulting compressed file passes gzip -t. A successful exit code and a non-empty file are stronger evidence than a log line alone.
Schedule backups with cron
Install the script outside the Drupal code directory, such as /usr/local/bin/backup-drupal.sh, and restrict it to the account that will run it. On a cPanel host, place it in the account’s home directory and use the hosting control panel’s cron feature instead of assuming a system crontab is available.
30 2 * * * /usr/local/bin/backup-drupal.sh >> /var/backups/drupal/backup.log 2>&1
The chosen time should avoid traffic peaks, deployment windows and overlapping maintenance jobs. Decide whether the schedule represents UTC or Australian local time. Cron implementations that support CRON_TZ can use a setting such as CRON_TZ=Australia/Sydney, but shared servers may use their own panel timezone. Scheduling in UTC avoids daylight-saving drift, while a local schedule is easier for Australian administrators to interpret.
Rotate the cron log so it cannot consume the disk. Also run the script manually before a Drupal core or contributed-module upgrade, even when the nightly job is working. Scheduled backups protect against unexpected loss; an on-demand dump provides a clear recovery point immediately before a planned schema or configuration change.
Store and protect backup copies
Local backups protect against accidental deletion and some application failures, but they do not survive loss of the server, account or hosting provider. Upload each completed dump to encrypted object storage, another provider or a separate machine. An S3-compatible bucket, rclone destination or managed backup service can provide this second layer, with the upload script reporting failures separately from the database export.
Australian hosting plans often include basic snapshots, although retention limits and database-size allowances may not suit a growing Drupal site. Local object-storage pricing can also separate storage from transfer costs, so compare the complete operational expense. Where a client contract requires Australian data residency, confirm that the selected bucket, support access and disaster-recovery copies remain in an approved location.
Keep retention rules short on the web server and longer in off-site storage. For example, retain daily dumps for two weeks, weekly dumps for two months and monthly dumps for a year, subject to the organisation’s policy. Test deletion commands against a copied directory before adding them to a live script. The recovery runbook should also link to relevant Drupal references, including Sofcase Drupal guidance, so administrators can verify the surrounding maintenance procedure.
Restore a Drupal database safely
Always restore into an isolated staging environment first. Confirm that the target server has a matching Drupal codebase, PHP version and database engine, then restore the dump into an empty database. Restoring over an existing Drupal installation can combine incompatible schemas and produce misleading errors.
For a MySQL or MariaDB target, a restricted client configuration can keep the password out of the command history:
gzip -dc /secure/path/drupal-20250115T023000Z.sql.gz \
| mysql --defaults-extra-file=/root/.my.cnf drupal_staging
After the import, clear stale caches and check whether database updates are pending. Run drush cache:rebuild, review drush updatedb:status and execute required updates only when the code version is intentionally newer than the dumped database. Then test login, content editing, taxonomy, search, custom modules and any transaction-driven feature.
The restored database does not contain uploaded files or necessarily include configuration stored only in the database. Restore those layers from their own backups or version-controlled deployment before testing the complete site. Record the time required for the import and application checks, because that measured figure is more useful than an estimated recovery time.
Prove reliability and maintain safeguards
A checksum proves that a file has not changed since it was written; it does not prove that the database can be recovered. Perform a full restore test at least quarterly and after significant Drupal, PHP or database-engine upgrades. Use a current dump, document every command and record the elapsed time, missing dependencies and manual steps involved.
Different backup methods suit different operational needs:
| Backup approach | Recovery speed | Portability | Main limitation | Best fit |
|---|---|---|---|---|
| Drush SQL dump with cron | Moderate | High | Requires scripting and periodic restore tests | Controlled Drupal infrastructure |
| Host database snapshot | Fast | Low to moderate | Tied to provider features and retention limits | Quick recovery on the same platform |
| Managed database backup | Varies by provider | Usually high | Subscription cost and provider dependency | Teams seeking reduced administration |
| Off-site compressed dump | Slower for large databases | High | Transfer time and manual upload unless automated | Portable disaster recovery |
The operating checklist should remain small enough to follow during an incident:
- Test a complete restore on a scheduled basis and record the actual recovery time.
- Keep at least one encrypted copy in a separate account, provider or approved region.
- Rotate database, Drush and storage credentials according to access policy.
- Monitor job exit codes, backup age, checksum failures and upload errors.
- Document backup ownership, restoration steps and the people authorised to perform them.
A backup is complete only when a restored copy opens correctly and passes the site’s key editorial, administrative and application checks.