After the previous lesson's warning about DROP DATABASE's lack of undo, the obvious next question is how to actually back a database up. The honest answer: unlike almost everything else in this course, backing up isn't one portable SQL statement, it's a different tool entirely depending on the database.
Learning Objectives
- Use SQL Server's BACKUP DATABASE statement.
- Know that MySQL, MariaDB, and PostgreSQL back up via a separate command-line tool, not a SQL statement.
- Explain SQLite's uniquely simple backup approach.
SQL Server: An Actual SQL Statement
SQL Server is the one database in this course where backing up genuinely is a SQL statement, run directly against the server, writing a .bak file to the specified path.
BACKUP DATABASE beacon_robotics TO DISK = 'C:\Backups\beacon_robotics.bak';
The Rest: Command-Line Tools, Not SQL
| Database | Real Backup Method |
|---|---|
| SQL Server | BACKUP DATABASE (an actual SQL statement) |
| MySQL / MariaDB | mysqldump, a separate command-line tool |
| PostgreSQL | pg_dump, a separate command-line tool |
| Oracle | Data Pump (expdp) or RMAN, separate tools |
| SQLite | Copy the database file itself |
mysqldump -u root -p beacon_robotics > beacon_robotics_backup.sql
pg_dump beacon_robotics > beacon_robotics_backup.sql
Both commands run in a regular terminal, not inside a SQL client like a query editor, they're operating system commands that connect to the database, not SQL statements at all. Each produces a .sql file full of CREATE TABLE and INSERT statements capable of rebuilding the entire database from scratch.
SQLite: Just Copy the File
Since a SQLite database is literally one file, backing it up is literally copying that file, no special tool, no SQL statement, no export format, just an ordinary file copy. (For a database actively being written to, SQLite's own backup API or the .backup command in its CLI handles this more safely than a raw file copy, but the underlying idea is the same.)
cp beacon_robotics.db beacon_robotics_backup.db
Try It Yourself
Write the command that backs up a MySQL database named beacon_robotics to a file named backup.sql.
mysqldump -u root -p beacon_robotics > backup.sql
Frequently Asked Questions
Why doesn't MySQL have a BACKUP DATABASE SQL statement like SQL Server does?
It's a genuine design difference between database systems, not a missing feature so much as a different architecture, MySQL's backup tooling lives outside the SQL language entirely, as a separate client program (mysqldump) that connects to the server and exports its own SQL script.
Can a mysqldump or pg_dump output file be used to restore the database?
Yes, that's its whole purpose, the output file is a complete, ordinary SQL script of CREATE TABLE and INSERT statements, run it against an empty database (or through a corresponding restore command) to rebuild everything.
Common Beginner Mistakes
Searching for a BACKUP DATABASE statement that works on MySQL
It doesn't exist, mysqldump is the real tool, and it's a separate command-line program, not something typed into a SQL query editor at all.
Assuming a SQLite file copy taken while the database is actively being written to is always safe
It can be, in practice, risky, a write in progress mid-copy can produce a corrupted backup. SQLite's own backup API, or stopping writes briefly first, is the safer approach for a database under active use.
Best Practices
- Know your specific database's real backup method before needing it urgently, it's not something to figure out during an actual emergency.
- Test a restore occasionally, not just the backup step, a backup that's never been restored successfully is an unverified assumption.
- Automate and schedule backups rather than relying on remembering to run them manually.
Interview Questions
Why is backing up a database not a single portable SQL statement across different database systems?
Each database system takes a different architectural approach: SQL Server exposes BACKUP DATABASE as an actual SQL statement, while MySQL/MariaDB (mysqldump) and PostgreSQL (pg_dump) rely on separate command-line tools outside the SQL language, and SQLite's backup is simply copying its one database file. There's no ANSI SQL standard for "backup" the way there is for SELECT or JOIN.
What does mysqldump actually produce, and how would you use its output?
It produces a plain .sql text file containing CREATE TABLE and INSERT statements that can fully reconstruct the database's structure and data. Running that file against an empty database (for example, via the mysql command-line client) restores everything it contains.
Summary
Backing up a database isn't portable SQL, SQL Server's BACKUP DATABASE is an actual statement, MySQL/MariaDB and PostgreSQL rely on separate command-line tools (mysqldump, pg_dump), Oracle uses Data Pump or RMAN, and SQLite's backup is simply copying its one file.
What's Next?
CREATE TABLE next, the statement that actually builds the departments, employees, projects, and project_assignments tables this course has been querying since Module 4.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.