Backup and Restore in ClickHouse

System tables related to access management, such as users, roles, row\_policies,
settings\_profiles, and quotas, receive special treatment during backup and restore operations.
When these tables are included in a backup, their content is exported to a special
`accessXX.txt` file, which encapsulates the equivalent SQL statements for creating
and configuring the access entities. Upon restoration, the restore process
interprets these files and re-applies the SQL commands to recreate the users,
roles, and other configurations. This feature ensures that the access control
configuration of a ClickHouse cluster can be backed up and restored as part of
the cluster's overall setup.

This functionality only works for configurations managed through SQL commands
(referred to as ["SQL-driven Access Control and Account Management"](/docs/operations/access-rights#enabling-access-control)).
Access configurations defined in ClickHouse server configuration files (e.g. `users.xml`)
aren't included in backups and can't be restored through this method.

## General syntax[​](#syntax "Direct link to General syntax")

```
-- core commands
BACKUP | RESTORE [ASYNC]
--- what to backup/restore (or exclude)
TABLE [db.]table_name           [AS [db.]table_name_in_backup] |
DICTIONARY [db.]dictionary_name [AS [db.]name_in_backup] |
DATABASE database_name          [AS database_name_in_backup] |
TEMPORARY TABLE table_name      [AS table_name_in_backup] |
VIEW view_name                  [AS view_name_in_backup] |
[EXCEPT TABLES ...] |
ALL [EXCEPT {TABLES|DATABASES}...] } [,...]
--- 
[ON CLUSTER 'cluster_name']
--- where to backup or restore to or from
TO|FROM 
File('<path>/<filename>') | 
Disk('<disk_name>', '<path>/') | 
S3('<S3 endpoint>/<path>', '<Access key ID>', '<Secret access key>', '<extra_credentials>') |
AzureBlobStorage('<connection string>/<url>', '<container>', '<path>', '<account name>', '<account key>')
--- additional settings
[SETTINGS ...]
```

**See ["command summary"](/docs/operations/backup/overview#command-summary) for more details
of each command.**

### Command summary[​](#command-summary "Direct link to Command summary")

Each of the commands above is detailed below:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| **Command** **Description**|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `BACKUP` Creates a backup of specified objects|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `RESTORE` Restores objects from a backup|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `[ASYNC]` Makes the operation run asynchronously (returns immediately with an ID you can monitor)|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `TABLE [db.]table_name [AS [db.]table_name_in_backup]` Backs up/restores a specific table (can be renamed)|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `[PARTITION[S] partition_expr [,...]]` Only backup/restore specific partitions of the table|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `DICTIONARY [db.]dictionary_name [AS [db.]name_in_backup]` Backs up/restores a dictionary object|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `DATABASE database_name [AS database_name_in_backup]` Backs up/restores an entire database (can be renamed)|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `TEMPORARY TABLE table_name [AS table_name_in_backup]` Backs up/restores a temporary table (can be renamed)|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `VIEW view_name [AS view_name_in_backup]` Backs up/restores a view (can be renamed)|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `[EXCEPT TABLES ...]` Exclude specific tables when backing up a database|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `ALL` Backs up/restores everything (all databases, tables, etc.). Prior to version 23.4 of ClickHouse, `ALL` was only applicable to the `RESTORE` command.| `[EXCEPT {TABLES|DATABASES}...]` Exclude specific tables or databases when using `ALL`| `[ON CLUSTER 'cluster_name']` Execute the backup/restore across a ClickHouse cluster|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `TO|FROM` Direction: `TO` for backup destination, `FROM` for restore source| `File('<path>/<filename>')` Store to/restore from local file system|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `Disk('<disk_name>', '<path>/')` Store to/restore from a configured disk|  |  |  |  | | --- | --- | --- | --- | | `S3('<S3 endpoint>/<path>', '<Access key ID>', '<Secret access key>')` Store to/restore from Amazon S3 or S3-compatible storage|  |  | | --- | --- | | `[SETTINGS ...]` See below for complete list of settings | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

### Settings[​](#settings "Direct link to Settings")

**Generic backup/restore settings**