Database Backup Script Generator
Generate bash backup and restore scripts for MySQL, PostgreSQL, SQLite, SQL Server and Oracle with real dump flags and no passwords on the command line.
The interactive tool loads here when JavaScript runs.
About this toolWhat is the Database Backup Script Generator?
The Database Backup Script Generator writes a pair of bash scripts for one database: a backup script that creates a timestamped dump and a restore script that loads it back. You pick the database (MySQL or MariaDB, PostgreSQL, SQLite, SQL Server or Oracle), enter the host, port, user and database name, and choose whether to include table structure, data, or both, and whether to compress.
Each script uses the database's own tool with flags that exist: mysqldump --single-transaction --quick, pg_dump with --format=custom for compressed dumps, SQLite's online .backup command, SQL Server's BACKUP DATABASE through sqlcmd, and Oracle Data Pump (expdp and impdp). Passwords are prompted for at run time and never written into the script or passed as a command-line argument where other users could see them in the process list.
The tool only generates text. It does not connect to your database, and what you type stays in the browser.
- MySQL and MariaDB, PostgreSQL, SQLite, SQL Server and Oracle
- Real flags only: --single-transaction, --format=custom, .backup, BACKUP DATABASE, expdp
- Structure only, data only, or both
- Passwords prompted at run time, never on the command line
- Exit codes checked so cron reports failures
- Generated in your browser; no connection to your database
Updated
How to use the Database Backup Script Generator
- Choose the Database Type. The port fills in with the default (3306, 5432, 1433 or 1521).
- Enter Host, Port, Username and Database Name. For SQLite, the database name is the file name; for Oracle, it is the service name such as ORCLPDB1.
- Tick Include table structure and Include data as needed, and Enable compression if you want gzip (or the compressed custom format for PostgreSQL).
- Add any extra flags under Custom Options, then click Generate Scripts.
- Copy or download the Backup Script and Restore Script, make them executable with chmod +x, and run the backup once by hand before scheduling it with cron.
Worked examples
Compressed PostgreSQL backup
Input
PostgreSQL, localhost:5432, user app, database shop, structure and data, compression on
Output
pg_dump -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" \ --format=custom \ --no-owner \ --file="$FILE" \ "$DB_NAME"
Custom format is compressed by default and is restored with pg_restore, which can restore single tables and run in parallel with -j. The file is named .dump because it is not plain SQL.
MySQL schema only
Input
MySQL, data unticked
Output
mysqldump --defaults-extra-file="$CNF" -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" \ --single-transaction \ --quick \ --no-data \ "$DB_NAME" > "$FILE"
The password goes into a temporary option file readable only by you and deleted on exit. --defaults-extra-file has to be the first option, which is why it comes before -h.
Background and insight
A logical dump (mysqldump, pg_dump, sqlite3 .dump) writes SQL or an archive that recreates the schema and data. It is portable across versions and easy to restore one table from, but slow for very large databases. A physical backup (copying data files, pg_basebackup, SQL Server .bak files, RMAN) is faster to restore and supports point-in-time recovery with archived logs. Most teams run both: physical backups for disaster recovery, logical dumps for migrations and spot restores.
Consistency is the part people get wrong. mysqldump --single-transaction takes a consistent snapshot of InnoDB tables without locking them, but it does not protect MyISAM tables. pg_dump is always consistent because it runs inside one snapshot transaction. Copying a live SQLite file with cp can catch it mid-write and miss the -wal file; the .backup command uses SQLite's online backup API and is safe while the application runs.
Some things are not in a database dump at all. pg_dump does not include roles or tablespaces (use pg_dumpall --globals-only), and mysqldump leaves out stored procedures and events unless you add --routines and --events. A backup you have never restored is a hope, not a backup: schedule a regular test restore into a scratch database.
Common use cases
Nightly backups on a small server
Generate a script, put it on the database host and schedule it with cron.
Before a risky migration
Take a quick logical dump you can restore from if the migration goes wrong.
Copying production to staging
Dump with the backup script and load into a staging database with the restore script.
Learning another database's tooling
See the equivalent of a familiar pg_dump command in mysqldump, sqlcmd or Data Pump.
Tips and limitations
- For unattended runs, replace the password prompt with ~/.pgpass for PostgreSQL or a ~/.my.cnf option file for MySQL, both with chmod 600.
- Keep at least one copy off the database server, for example in object storage with versioning turned on.
- SQL Server and Oracle write the backup on the database server, not on the machine running the script; the paths in those scripts are server paths.
- Names with quotes, brackets or spaces are escaped for each database: SQL Server names go in [brackets] with ] doubled and the .bak path has any ' doubled, and an Oracle schema that is not a plain identifier is double-quoted in a Data Pump parameter file. The .bak and script file names replace unusual characters with underscores.
- WITH COMPRESSION for SQL Server needs Standard or Enterprise edition, and Oracle COMPRESSION=ALL needs the Advanced Compression option.
- Restoring a schema-and-data dump into a database that already has the tables will fail or duplicate rows; restore into an empty database.
Sources and further reading
- pg_dump, PostgreSQL Global Development Group. Consistent logical dumps of one database
- pg_dumpall, PostgreSQL Global Development Group. --globals-only for roles and tablespaces
- Continuous Archiving and Point-in-Time Recovery (PITR), PostgreSQL Global Development Group. Physical base backups with archived WAL
- SQLite Backup API, SQLite. The online backup used by the .backup command
- Back up and Restore of SQL Server Databases, Microsoft. SQL Server backups and the case for test restores
Frequently asked questions
Does pg_dump lock the database while it runs?
No. pg_dump reads from a single snapshot, so it sees a consistent database while other sessions keep reading and writing. It does take light locks that block schema changes such as ALTER TABLE on the tables it is dumping until it finishes.
What does mysqldump --single-transaction do?
It starts a transaction with a consistent snapshot before dumping, so InnoDB tables are exported as of one moment without locking them. It does not protect MyISAM tables, and you should avoid DDL on the dumped tables while it runs.
Can I exclude indexes from a pg_dump or mysqldump backup?
No. Neither tool has an option to leave indexes out, because indexes are part of the table definitions; a --no-indexes flag does not exist in either tool. A data-only dump (--data-only or --no-create-info) omits all DDL, indexes included.
Is it safe to copy a SQLite database file while the app is running?
Not with cp. The copy can catch a write halfway and miss changes still in the -wal file. Use sqlite3 db ".backup 'copy.db'" or VACUUM INTO, which use SQLite's own locking to produce a consistent file.
Why isn't the password in the generated backup script?
Passwords in scripts end up in version control and backups, and passwords on the command line can be read by other users through ps. The scripts prompt for it, then pass it through a private option file or an environment variable that is cleared on exit.
How do I back up SQL Server from Linux or macOS?
Use sqlcmd, which runs BACKUP DATABASE on the server and writes a native .bak file to a path on the server. The generated script does exactly that and then runs RESTORE VERIFYONLY to check the file.
Is there an in-depth guide to the Database Backup Script Generator?
Yes. “Database Backups with pg_dump, mysqldump and Friends: What Actually Works” covers the background, how it works, common pitfalls and worked examples. Read the full guide.
Related Tools You May Like
Free, in-browser tools that pair well with this one.