> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Details backup/restore to or from a local disk

# BACKUP / RESTORE to disk

<h2 id="syntax">
  Syntax
</h2>

```sql theme={null}
-- core commands
BACKUP | RESTORE 
--- 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 ...]
[ASYNC]
```

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

<h2 id="configure-backup-destinations-for-disk">
  Configure backup destinations for disk
</h2>

<h3 id="configure-a-backup-destination">
  Configure a backup destination for local disk
</h3>

In the examples below you will see the backup destination specified as `Disk('backups', '1.zip')`.\
To use the `Disk` backup engine it is necessary to first add a file specifying
the backup destination at the path below:

```text theme={null}
/etc/clickhouse-server/config.d/backup_disk.xml
```

For example, the configuration below defines a disk named `backups` and then adds that disk to
the **allowed\_disk** list of **backups**:

```xml highlight={4,10-13} theme={null}
<clickhouse>
    <storage_configuration>
        <disks>
            <backups>
                <type>local</type>
                <path>/backups/</path>
            </backups>
        </disks>
    </storage_configuration>
    <backups>
        <allowed_disk>backups</allowed_disk>
        <allowed_path>/backups/</allowed_path>
    </backups>
</clickhouse>
```

<h3 id="backuprestore-using-an-s3-disk">
  Configure a backup destination for S3 disk
</h3>

It is also possible to `BACKUP`/`RESTORE` to S3 by configuring an S3 disk in the
ClickHouse storage configuration. Configure the disk like this by adding a file to
`/etc/clickhouse-server/config.d` as was done above for the local disk.

```xml theme={null}
<clickhouse>
    <storage_configuration>
        <disks>
            <s3_plain>
                <type>s3_plain</type>
                <endpoint></endpoint>
                <access_key_id></access_key_id>
                <secret_access_key></secret_access_key>
            </s3_plain>
        </disks>
        <policies>
            <s3>
                <volumes>
                    <main>
                        <disk>s3_plain</disk>
                    </main>
                </volumes>
            </s3>
        </policies>
    </storage_configuration>

    <backups>
        <allowed_disk>s3_plain</allowed_disk>
    </backups>
</clickhouse>
```

`BACKUP`/`RESTORE` for S3 disk is done in the same way as for local disk:

```sql theme={null}
BACKUP TABLE data TO Disk('s3_plain', 'cloud_backup');
RESTORE TABLE data AS data_restored FROM Disk('s3_plain', 'cloud_backup');
```

<Note>
  * This disk shouldn't be used for `MergeTree` itself, only for `BACKUP`/`RESTORE`
  * If your tables are backed by S3 storage and the types of the disks are different,
    it doesn't use `CopyObject` calls to copy parts to the destination bucket, instead,
    it downloads and uploads them, which is very inefficient. In this case prefer using
    the `BACKUP ... TO S3(<endpoint>)` syntax for this use-case.
</Note>

<h2 id="usage-examples">
  Usage examples of backup/restore to local disk
</h2>

<h3 id="backup-and-restore-a-table">
  Backup and restore a table
</h3>

Run the following commands below to create the test database and table we will be
making a backup and restoration of in this example:

<Accordion title="Setup commands">
  Create the database and table:

  ```sql theme={null}
  CREATE DATABASE test_db;

  CREATE TABLE test_db.test_table (
      id UUID,
      name String,
      email String,
      age UInt8,
      salary UInt32,
      created_at DateTime,
      is_active UInt8,
      department String,
      score Float32,
      country String
  ) ENGINE = MergeTree()
  ORDER BY id;
  ```

  Preprocess and one thousand rows of random data:

  ```sql theme={null}
  INSERT INTO test_table (id, name, email, age, salary, created_at, is_active, department, score, country)
  SELECT
      generateUUIDv4() as id,
      concat('User_', toString(rand() % 10000)) as name,
      concat('user', toString(rand() % 10000), '@example.com') as email,
      18 + (rand() % 65) as age,
      30000 + (rand() % 100000) as salary,
      now() - toIntervalSecond(rand() % 31536000) as created_at,
      rand() % 2 as is_active,
      arrayElement(['Engineering', 'Marketing', 'Sales', 'HR', 'Finance', 'Operations'], (rand() % 6) + 1) as department,
      rand() / 4294967295.0 * 100 as score,
      arrayElement(['USA', 'UK', 'Germany', 'France', 'Canada', 'Australia', 'Japan', 'Brazil'], (rand() % 8) + 1) as country
  FROM numbers(1000);
  ```

  Next you will need to create a file specifying the backup destination at the
  path below:

  ```text theme={null}
  /etc/clickhouse-server/config.d/backup_disk.xml
  ```

  ```xml theme={null}
  <clickhouse>
      <storage_configuration>
          <disks>
              <backups>
                  <type>local</type>
                  <path>/backups/</path> -- for MacOS choose: /Users/backups/
              </backups>
          </disks>
      </storage_configuration>
      <backups>
          <allowed_disk>backups</allowed_disk>
          <allowed_path>/backups/</allowed_path> -- for MacOS choose: /Users/backups/
      </backups>
  </clickhouse>
  ```

  <Note>
    If clickhouse-server is running you will need to restart it for the changes to
    take effect.
  </Note>
</Accordion>

To backup the table you can run:

```sql title="Query" theme={null}
BACKUP TABLE test_db.test_table TO Disk('backups', '1.zip')
```

```response title="Response" theme={null}
   ┌─id───────────────────────────────────┬─status─────────┐
1. │ 065a8baf-9db7-4393-9c3f-ba04d1e76bcd │ BACKUP_CREATED │
   └──────────────────────────────────────┴────────────────┘
```

The table can be restored from the backup using the following command if the table is empty:

```sql title="Query" theme={null}
RESTORE TABLE test_db.test_table FROM Disk('backups', '1.zip')
```

```response title="Response" theme={null}
   ┌─id───────────────────────────────────┬─status───┐
1. │ f29c753f-a7f2-4118-898e-0e4600cd2797 │ RESTORED │
   └──────────────────────────────────────┴──────────┘
```

<Note>
  The above `RESTORE` would fail if the table `test.table` contains data.
  The setting `allow_non_empty_tables=true` allows `RESTORE TABLE` to insert data
  into non-empty tables. This will mix earlier data in the table with the data extracted from the backup.
  This setting can therefore cause data duplication in the table, and should be used with caution.
</Note>

To restore the table with data already in it, run:

```sql theme={null}
RESTORE TABLE test_db.test_table FROM Disk('backups', '1.zip')
SETTINGS allow_non_empty_tables=true
```

Tables can be restored, or backed up, with new names:

```sql theme={null}
RESTORE TABLE test_db.test_table AS test_db.test_table_renamed FROM Disk('backups', '1.zip')
```

The backup archive for this backup has the following structure:

```text theme={null}
├── .backup
└── metadata
    └── test_db
        └── test_table.sql
```

Formats other than zip can be used. See ["Backups as tar archives"](#backups-as-tar-archives)
below for further details.

<h3 id="incremental-backups">
  Incremental backups to disk
</h3>

A base backup in ClickHouse is the initial, full backup from which the following
incremental backups are created. Incremental backups only store the changes
made since the base backup, so the base backup must be kept available to
restore from any incremental backup. The base backup destination can be set with setting
`base_backup`.

<Note>
  Incremental backups depend on the base backup. The base backup must be kept available
  to be able to restore from an incremental backup.
</Note>

To make an incremental backup of a table, first make a base backup:

```sql theme={null}
BACKUP TABLE test_db.test_table TO Disk('backups', 'd.zip')
```

```sql theme={null}
BACKUP TABLE test_db.test_table TO Disk('backups', 'incremental-a.zip')
SETTINGS base_backup = Disk('backups', 'd.zip')
```

All data from the incremental backup and the base backup can be restored into a
new table `test_db.test_table2` with command:

```sql theme={null}
RESTORE TABLE test_db.test_table AS test_db.test_table2
FROM Disk('backups', 'incremental-a.zip');
```

<h3 id="assign-a-password-to-the-backup">
  Securing a backup
</h3>

Backups written to disk can have a password applied to the file.
The password can be specified using the `password` setting.

<Note>
  Password protection is only supported for ZIP archives (`.zip`, `.zipx`).
  The backup path must end with `.zip` or `.zipx` for the password to be accepted.
  Using a password with any other format - including tar archives and non-archive paths - will
  result in a `BAD_ARGUMENTS` error: `Password is not applicable, backup cannot be encrypted`.
</Note>

```sql theme={null}
BACKUP TABLE test_db.test_table
TO Disk('backups', 'password-protected.zip')
SETTINGS password='qwerty'
```

To restore a password-protected backup, the password must again
be specified using the `password` setting:

```sql theme={null}
RESTORE TABLE test_db.test_table
FROM Disk('backups', 'password-protected.zip')
SETTINGS password='qwerty'
```

<h3 id="backups-as-tar-archives">
  Backups as tar archives
</h3>

Backups can be stored not only as zip archives, but also as tar archives.
The functionality is the same as for zip, except that password protection isn't
supported for tar archives. Additionally, tar archives support a variety of
compression methods.

To make a backup of a table as a tar:

```sql theme={null}
BACKUP TABLE test_db.test_table TO Disk('backups', '1.tar')
```

to restore from a tar archive:

```sql theme={null}
RESTORE TABLE test_db.test_table FROM Disk('backups', '1.tar')
```

To change the compression method, the correct file suffix should be appended to
the backup name. For example, to compress the tar archive using gzip run:

```sql theme={null}
BACKUP TABLE test_db.test_table TO Disk('backups', '1.tar.gz')
```

The supported compression file suffixes are:

* `tar.gz`
* `.tgz`
* `tar.bz2`
* `tar.lzma`
* `.tar.zst`
* `.tzst`
* `.tar.xz`

<h3 id="compression-settings">
  Compression settings
</h3>

The compression method and level of compression can be specified using
setting `compression_method` and `compression_level` respectively.

```sql theme={null}
BACKUP TABLE test_db.test_table
TO Disk('backups', 'filename.zip')
SETTINGS compression_method='lzma', compression_level=3
```

<h3 id="restore-specific-partitions">
  Restore specific partitions
</h3>

If specific partitions associated with a table need to be restored, these can be specified.

Let's create a simple partitioned table into four parts, insert some data into it and then
take a backup of only the first and fourth partitions:

<Accordion title="Setup">
  ```sql theme={null}
  CREATE IF NOT EXISTS test_db;
         
  -- Create a partitioend table
  CREATE TABLE test_db.partitioned (
      id UInt32,
      data String,
      partition_key UInt8
  ) ENGINE = MergeTree()
  PARTITION BY partition_key
  ORDER BY id;

  INSERT INTO test_db.partitioned VALUES
  (1, 'data1', 1),
  (2, 'data2', 2),
  (3, 'data3', 3),
  (4, 'data4', 4);

  SELECT count() FROM test_db.partitioned;

  SELECT partition_key, count() 
  FROM test_db.partitioned
  GROUP BY partition_key
  ORDER BY partition_key;
  ```

  ```response theme={null}
     ┌─count()─┐
  1. │       4 │
     └─────────┘
     ┌─partition_key─┬─count()─┐
  1. │             1 │       1 │
  2. │             2 │       1 │
  3. │             3 │       1 │
  4. │             4 │       1 │
     └───────────────┴─────────┘
  ```
</Accordion>

Run the following command to back up partitions 1 and 4:

```sql theme={null}
BACKUP TABLE test_db.partitioned PARTITIONS '1', '4'
TO Disk('backups', 'partitioned.zip')
```

Run the following command to restore partitions 1 and 4:

```sql theme={null}
RESTORE TABLE test_db.partitioned PARTITIONS '1', '4'
FROM Disk('backups', 'partitioned.zip')
SETTINGS allow_non_empty_tables=true
```