What Is the Difference Between H2 Database Backup and Export?

Mar 26, 2026

Problem

I recently worked on a project using H2 database and needed to implement a data management strategy. I kept seeing two different approaches mentioned in documentation: backup and export. At first, they seemed similar—both involve getting data out of the database—but I wasn’t sure when to use which one.

I tried using exports for my backup strategy, but later realized I might have been missing critical database components. This confusion led me to dig deeper into what each operation actually does and when to use them.

Purpose

This post shows the key differences between H2 database backup and export operations. Understanding this distinction is crucial for choosing the right approach for your specific use case—whether you need complete recovery capability or data portability.

The Core Difference

Let me start with a simple comparison:

┌─────────────────────────────────────────────────┐
│                  BACKUP                          │
├─────────────────────────────────────────────────┤
│ Purpose: Complete recovery                      │
│ Scope: Entire database instance                 │
│ Content: Tables + indices + metadata            │
│ Format: Binary (zip package)                    │
│ Use Case: Disaster recovery, migration          │
└─────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────┐
│                  EXPORT                          │
├─────────────────────────────────────────────────┤
│ Purpose: Data portability                       │
│ Scope: Specific tables or queries              │
│ Content: Data only (selective)                  │
│ Format: Text (CSV, SQL script)                  │
│ Use Case: Data sharing, analysis, partial move  │
└─────────────────────────────────────────────────┘

When to Use Backup

I use backup when I need to preserve the complete database state for recovery purposes. The backup operation creates a point-in-time snapshot that includes:

All tables and their data
Created indices
Database metadata
Schema information

Here’s how I create a backup:

// Create a complete backup of the database
BACKUP TO '/backups/mydb-20260326.zip';

This command creates a zip package containing everything needed to restore the database to its exact state at that moment. I use this approach for:

Disaster recovery: Restoring after data loss
Environment migration: Moving the entire database to a new server
Point-in-time snapshots: Capturing database state before major changes

When to Use Export

I use export when I need data portability rather than full recovery capability. Export operations focus on extracting specific data in formats that are easy to read, share, and process.

CSV Export

For exporting query results to CSV:

// Export specific data to CSV for analysis
CALL CSVWRITE(
  '/exports/active-users.csv',
  'SELECT * FROM users WHERE active = true'
);

I use CSV exports for:

Data analysis: Loading data into Excel or analytics tools
Data sharing: Sending subsets of data to other teams
Partial migration: Moving specific records between systems

SQL Script Export

For exporting table structure and data:

// Export specific table schema and data
SCRIPT SIMPLE TO '/exports/users-export.sql'
TABLE PUBLIC.users;

I use SQL script exports for:

Schema inspection: Reviewing table structure
Partial migration: Moving specific tables to another database
Version control: Tracking schema changes

Key Differences in Practice

Let me show a practical scenario. Suppose I have a user management database:

users (table)
  ├── id (primary key)
  ├── email
  └── created_at

idx_user_email (index)
idx_user_created (index)

Database metadata and settings

Backup Approach

When I run backup, I get everything:

BACKUP TO '/backups/userdb-full.zip';

Result: A complete package with tables, indices, and metadata. I can restore this to get the exact same database state.

Export Approach

When I export, I choose what to extract:

// Only export user data, no indices
CALL CSVWRITE(
  '/exports/users-data.csv',
  'SELECT id, email, created_at FROM users'
);

Result: A CSV file with user data only. No indices, no metadata, just the raw data I selected.

Common Mistakes I Avoided

Mistake 1: Using Export as Primary Backup

I initially thought exporting to SQL scripts was sufficient for backup. However, I learned that exports might miss:

Database-level settings
Index definitions (unless explicitly exported)
Constraints and triggers
Sequence values

For true backup capability, I use BACKUP TO.

Mistake 2: Using Backup When Only Specific Data is Needed

When I only needed to share a subset of data with a colleague, creating a full backup was overkill. The backup file was larger than necessary and contained sensitive data that shouldn’t be shared.

Instead, I used targeted exports:

// Export only what's needed
CALL CSVWRITE(
  '/exports/public-user-report.csv',
  'SELECT name, created_at FROM users WHERE public_profile = true'
);

Mistake 3: Not Understanding Point-in-Time vs Selective

Backups are point-in-time snapshots of the entire database. Exports can be filtered queries run at any time. This distinction matters:

Backup: “What was the entire database state at 3 PM?”
Export: “What user records match this criteria right now?”

Choosing the Right Approach

Here’s my decision process:

Need to restore database after failure?
  └─ Yes → Use BACKUP TO

Need to share specific data with others?
  └─ Yes → Use CSVWRITE or SCRIPT

Need to analyze data in external tools?
  └─ Yes → Use CSVWRITE

Need to migrate entire database to new server?
  └─ Yes → Use BACKUP TO

Need to extract specific tables?
  └─ Yes → Use SCRIPT TABLE tablename

Need complete recovery guarantee?
  └─ Yes → Use BACKUP TO

Recovery vs Portability

The fundamental difference comes down to:

Backup = Recovery Strategy

Preserves complete database state
Enables full restoration
Includes all database components
Used for disaster recovery

Export = Portability Strategy

Focuses on data extraction
Enables sharing and analysis
Selective in scope
Used for data movement and inspection

Summary

The key difference between H2 database backup and export is their purpose: backup preserves the complete database instance for recovery scenarios, while export focuses on data portability for sharing, analysis, and partial migration.

I use BACKUP TO when I need complete recovery capability—restoring after data loss, migrating entire environments, or creating point-in-time snapshots. I use CSVWRITE or SCRIPT when I need data portability—exporting specific tables or query results for analysis, sharing subsets of data, or partial migration.

Choosing the wrong approach can leave me without proper recovery capability (using exports as backup) or create unnecessary overhead (using backup when only specific data export is needed). Understanding this distinction has helped me build a more effective data management strategy.

Final Words + More Resources

My intention with this article was to help others share my knowledge and experience. If you want to contact me, you can contact by email: Email me

Here are also the most important links from this article along with some further resources that will help you in this scope:

👨‍💻 H2 Database Documentation

Oh, and if you found these resources useful, don’t forget to support me by starring the repo on GitHub!