Backup Restore

Note: This feature is available in Community, Enterprise, Ultimate and Team editions only.

This article provides an in-depth guide to the Backup and Restore features available in DBeaver. DBeaver supports a wide range of databases for backup and restore operations:

• Classic databases:
  • PostgreSQL
  • MySQL
  • MariaDB
  • TiDB
  • SingleStore
  • EnterpriseDB
  • Greenplum
  • YugabyteDB
  • CrateDB
  • Materialize
  • TimescaleDB
  • Yellowbrick
• Cloud databases:
  • AlloyDB
  • Google Cloud SQL for PostgreSQL
  • Google Cloud SQL for MySQL

The native backup and restore differs from the standard DBeaver Data Transfer feature by focusing on creating and applying database backups using specialized utilities. These tools are tailored for high-performance interactions with databases, potentially accelerating the backup and restore processes compared to general data transfer methods.

These functions can be accessed from the Context Menu's Tools or the Main Menu's Database -> Tools.

Different database systems possess distinct settings for backup and restore processes. This section presents the settings specific to MySQL and PostgreSQL.

To initiate a backup:

1. Select the desired database.

2. Right-click on the database and choose Tools -> Dump database.

3. The Dump window will appear. Select the necessary objects and click Next.

4. The Export configuration tab will be displayed. Here you can find the following checkboxes and fields:

Option/Field	Description
Execution Method	Method for execution. The default is Normal (no locks).
Execution Method options:	Normal (no locks): This is the default method. It performs the dump without acquiring any locks on the tables. Suitable for databases where minimal disruption is required during the dump process.
	Online backup in single transaction: This method ensures the consistency of the database by performing the dump within a single transaction. It's ideal for databases that support transactional storage engines like InnoDB. The backup occurs without interrupting other database operations but requires a higher isolation level.
	Lock all tables: As the name suggests, this method locks all tables in the database during the dump process. It ensures complete consistency of the dump but may interrupt other database operations. Suitable for databases or situations where you want a consistent snapshot and are okay with potential disruptions.
No CREATE statements	Do not include CREATE statements in the dump.
Add DROP statements	Include DROP statements before CREATE statements.
Disable keys	Disable keys during the dump process.
Extended inserts	Use extended INSERT statements in the dump.
Dump events	Include events in the dump.
Additional comments	Include comments in the dump.
Remove DEFINER	Remove DEFINER clause from the dump.
Dump binaries in hex	Display binary data as HEX in the dump.
Structure only	Only dump table structures, not data.
Output folder	Specify the directory where the dump file will be saved. For Ultimate Edition, Team Edition, and CloudBeaver versions, users have the option to save to a remote file system using the Browser remote file system button .
Extra command args	Provide additional command-line arguments. See details in the Extra command arguments section.
Authenticate	Click to open the Authentication window where you can fill in the Username/Password for override.
Reset to default	Reset credentials to their default values.
Override host credentials	Use different host credentials, if necessary.
Local Client	Open a window to specify the path to the Local client. More about setting it up.

Note: The particular set of configuration options depends on the database type.

5. After configuring the settings, click Start.

6. Upon successful completion, a notification will appear with information about the process.

7. The backup file can be found in the folder specified during the Export configuration step.

To initiate a restore:

1. Select the desired database.

2. Right-click on the database and choose Tools -> Restore database.

3. The Restore window will appear. In this window you have to provide the path to the SQL file you wish to restore from in the designated field. Example path: /path/to/file.sql

Below are the settings for the restore process:

Tip: In the input field for specifying the path to the SQL file, you can choose a file from a local directory or use the Browser remote file system button to select a file from a remote file system. This functionality is accessible > to users of the Ultimate Edition, Team Edition, and CloudBeaver.

5. After configuring the settings, click Start.

6. Upon successful completion, a notification will appear with information about the process.

To initiate a backup:

1. Select the desired database.

2. Right-click on the database and choose Tools -> Backup.

3. The Dump window will appear. Select the necessary objects and click Next.

4. The Backup settings tab will be displayed. Here you can find the following checkboxes and fields:

Setting	Description
Format	Choose the format for the dump. Options include Directory, Tar, Custom, and Plain.
Format options:	Directory: Splits the output into one file per table. Suitable for larger databases as it facilitates parallel restoration.
	Tar: Produces archive in tar format. Useful for backups since it can be read by standard tools.
	Custom: A flexible format that allows selective restore and other operations.
	Plain: Produces plain-text SQL script file. It can be used with psql for restoring.
Compression	Set the compression level for the dump, ranging from 0 (no compression) to 9 (maximum compression).
Encoding	Select the character encoding for the dump. The available encodings depend on database configuration and locale.
Use SQL INSERT instead of COPY for rows	Use the INSERT command instead of the COPY command for row data.
Do not backup privileges (GRANT/REVOKE)	Exclude privilege commands (GRANT/REVOKE) from the dump.
Discard objects owner	Exclude the ownership information from the dump.
Add drop database statement	Include a statement in the dump to drop the database before restoring.
Add create database statement	Include a statement in the dump to create the database when restoring.
Output folder	Specify the directory where the dump file will be saved. For Ultimate Edition, Team Edition, and CloudBeaver versions, users have the option to save to a remote file system using the Browser remote file system button.
File name pattern	Define the naming pattern for the dump file, with variables like ${database} and ${timestamp} to customize the filename.
Extra command args	Provide additional command-line arguments. See details in the Extra command arguments section.
Authentication	Click to open the Authentication window where you can fill in the Username/Password for override.
Reset to default	Reset credentials to their default values.
Override host credentials	Use different host credentials, if necessary.
Local Client	Specify the path to the Local client.

Note: The particular set of configuration options depends on the database type.

5. After configuring the settings, click Start.

6. Upon successful completion, a notification will appear with information about the process.

7. The backup file can be found in the folder specified during the Backup settings step.

When performing a Global PostgreSQL database Backup, the entire database is dumped, including roles and tablespaces. This differs from standard backup procedures where only specific schemas and their contents can be selected. Additionally, multiple databases can be chosen for backup at once in the global method.

To initiate a global backup:

1. Select the desired database.

2. Right-click on the database and choose Tools -> Global Backup.

3. The Global Dump window will appear. Select the necessary objects and click Next.

4. The Backup settings tab will be displayed. Here you can find the following checkboxes and fields:

Setting	Description
Encoding	Set the character encoding for the backup.
Dump only the object definitions, not data	Includes only the structure of the database objects, excluding the data.
Dump only global objects, no databases	Includes only global objects and excludes individual databases.
Dump only roles	Includes only user roles.
Dump only tablespaces	Captures only tablespaces without the databases.
Do not backup privileges (GRANT/REVOKE)	Excludes privilege statements like GRANT and REVOKE.
Discard objects owner	Excludes the ownership information of the database objects.
Dump passwords for roles	Includes passwords associated with the user roles.
Output folder	Specify the directory where the dump file will be saved. For Ultimate Edition, Team Edition, and CloudBeaver versions, users have the option to save to a remote file system using the Browser remote file system button .
File name pattern	Define the naming pattern for the dump file, with variables like ${database} and ${timestamp} to customize the filename.
Extra command args	Provide additional command-line arguments. See details in the Extra command arguments section.
Authenticate	Click to open the Authentication window where you can fill in the Username/Password for override.
Reset to default	Reset credentials to their default values.
Override host credentials	Use different host credentials, if necessary.
Local Client	Specify the path to the Local client.

5. After configuring the settings, click Start.

6. Upon successful completion, a notification will appear with information about the process.

7. The backup file can be found in the folder specified during the Global backup settings step.

To initiate a restore:

1. Select the desired database.

2. Right-click on the database and choose Tools -> Restore.

Setting	Description
Format	Choose the format for the restore operation. Options include Directory, Tar, Custom, and Plain.
Format options:	Directory: Assumes the input consists of one file per table. Suitable for larger databases as it supports parallel restore.
	Tar: Assumes the input is an archive in tar format. This is useful when restoring backups made in tar format.
	Custom: A flexible input format that allows selective restore and other operations.
	Plain: Assumes the input is a plain-text SQL script file. Suitable for scripts that can be executed with psql.
Clean (drop) database objects before recreating them	Drop database objects before recreating them from the backup.
Create database	Create a new database from the backup.
Discard objects owner	Exclude ownership information during the restore.
Backup file	Specify the path to the backup file that you wish to restore. For Ultimate Edition, Team Edition, and CloudBeaver versions, use the Browser remote file system button to select a backup file from a remote file system via Cloud Storage.
Extra command args	Provide additional command-line arguments. See details in the Extra command arguments section.
Authentication	Click to open the Authentication window where you can fill in the Username/Password for override.
Reset to default	Reset credentials to their default values.
Local Client	Specify the path to the Local client.

3. After configuring the settings, click Start.

4. Upon successful completion, a notification will appear with information about the process.

In DBeaver, besides the standard backup and restore operations, there's an additional capability to run scripts directly through the Execute script command. This functionality uses native database clients.

Steps to use the Execute script command:

1. Right-click on the desired database in the navigator tree.
2. Navigate to Tools -> Execute script.
3. A window will appear prompting you to provide the path to your script.

Tip: In the input field for specifying the path to the SQL file, you can choose a file from a local directory or use the Browser remote file system button to select a file from a remote file system. This functionality is accessible > to users of the Ultimate Edition, Team Edition, and CloudBeaver.

The Execute script command provides an alternative way to perform database operations that may not be covered by the traditional restore functionality. This includes running complex scripts or batch operations that require direct execution in the database's native environment.

The Extra command args field allows you to input additional command-line arguments to further customize the dump process. This offers more specific control over the operation.

For example, when configuring backup/restore for PostgreSQL, you may want to exclude specific tables. To exclude a table named employee_data, use the --exclude-table option. Enter --exclude-table=employee_data in the Extra command args field during the setup process.

By doing this, the resulting process won't contain any data or structure from the employee_data table.

Note: Always consult the database's official documentation for a comprehensive list and explanation of available command-line arguments.

You can set up both restore and dump operations as tasks. To save an operation as a task, click the Save task button during the dump/restore process. For detailed instructions and features related to task management in DBeaver, refer to Task management article.