BACKUP / RESTORE to disk

```
RESTORE TABLE test_db.table_table AS test_db.test_table_renamed FROM Disk('backups', '1.zip')
```

The backup archive for this backup has the following structure:

```
├── .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.

### Incremental backups to disk[​](#incremental-backups "Direct link to Incremental backups to disk")

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.

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

```
BACKUP TABLE test_db.test_table TO Disk('backups', 'd.zip')
```

```
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:

```
RESTORE TABLE test_db.test_table AS test_db.test_table2
FROM Disk('backups', 'incremental-a.zip');
```

### Securing a backup[​](#assign-a-password-to-the-backup "Direct link to Securing a backup")

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`.

```
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:

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

### Backups as tar archives[​](#backups-as-tar-archives "Direct link to Backups as tar archives")

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:

```
BACKUP TABLE test_db.test_table TO Disk('backups', '1.tar')
```

to restore from a tar archive:

```
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:

```
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`

### Compression settings[​](#compression-settings "Direct link to Compression settings")

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

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

### Restore specific partitions[​](#restore-specific-partitions "Direct link to Restore specific partitions")

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:

Setup

```
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;
```

```
   ┌─count()─┐
1. │       4 │
   └─────────┘
   ┌─partition_key─┬─count()─┐
1. │             1 │       1 │
2. │             2 │       1 │
3. │             3 │       1 │
4. │             4 │       1 │
   └───────────────┴─────────┘
```

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

```
BACKUP TABLE test_db.partitioned PARTITIONS '1', '4'
TO Disk('backups', 'partitioned.zip')
```

Run the following command to restore partitions 1 and 4:

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

- [Syntax](#syntax)- [Configure backup destinations for disk](#configure-backup-destinations-for-disk)
    - [Configure a backup destination for local disk](#configure-a-backup-destination)- [Configure a backup destination for S3 disk](#backuprestore-using-an-s3-disk)- [Usage examples of backup/restore to local disk](#usage-examples)
      - [Backup and restore a table](#backup-and-restore-a-table)- [Incremental backups to disk](#incremental-backups)- [Securing a backup](#assign-a-password-to-the-backup)- [Backups as tar archives](#backups-as-tar-archives)- [Compression settings](#compression-settings)- [Restore specific partitions](#restore-specific-partitions)

Was this page helpful?