title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/engines/database-engines/backup.md)

Database backup allows to instantly attach table/database from [backups](/docs/operations/backup/overview) in read-only mode.

Database backup works with both incremental and non-incremental backups.

## Creating a database[​](#creating-a-database "Direct link to Creating a database")

```
CREATE DATABASE backup_database
ENGINE = Backup('database_name_inside_backup', 'backup_destination')
```

Backup destination can be any valid backup [destination](/docs/operations/backup/disk#configure-backup-destinations-for-disk) like `Disk`, `S3`, `File`.

With `Disk` backup destination, query to create database from backup looks like this:

```
CREATE DATABASE backup_database
ENGINE = Backup('database_name_inside_backup', Disk('disk_name', 'backup_name'))
```

**Engine Parameters**

- `database_name_inside_backup` — Name of the database inside the backup.
- `backup_destination` — Backup destination.

## Usage example[​](#usage-example "Direct link to Usage example")

Let's make an example with a `Disk` backup destination. Let's first setup backups disk in `storage.xml`:

```
<storage_configuration>
    <disks>
        <backups>
            <type>local</type>
            <path>/home/ubuntu/ClickHouseWorkDir/backups/</path>
        </backups>
    </disks>
</storage_configuration>
<backups>
    <allowed_disk>backups</allowed_disk>
    <allowed_path>/home/ubuntu/ClickHouseWorkDir/backups/</allowed_path>
</backups>
```

Example of usage. Let's create test database, tables, insert some data and then create a backup:

```
CREATE DATABASE test_database;

CREATE TABLE test_database.test_table_1 (id UInt64, value String) ENGINE=MergeTree ORDER BY id;
INSERT INTO test_database.test_table_1 VALUES (0, 'test_database.test_table_1');

CREATE TABLE test_database.test_table_2 (id UInt64, value String) ENGINE=MergeTree ORDER BY id;
INSERT INTO test_database.test_table_2 VALUES (0, 'test_database.test_table_2');

CREATE TABLE test_database.test_table_3 (id UInt64, value String) ENGINE=MergeTree ORDER BY id;
INSERT INTO test_database.test_table_3 VALUES (0, 'test_database.test_table_3');

BACKUP DATABASE test_database TO Disk('backups', 'test_database_backup');
```

So now we have `test_database_backup` backup, let's create database Backup:

```
CREATE DATABASE test_database_backup ENGINE = Backup('test_database', Disk('backups', 'test_database_backup'));
```

Now we can query any table from database:

```
SELECT id, value FROM test_database_backup.test_table_1;

┌─id─┬─value──────────────────────┐
│  0 │ test_database.test_table_1 │
└────┴────────────────────────────┘

SELECT id, value FROM test_database_backup.test_table_2;

┌─id─┬─value──────────────────────┐
│  0 │ test_database.test_table_2 │
└────┴────────────────────────────┘

SELECT id, value FROM test_database_backup.test_table_3;

┌─id─┬─value──────────────────────┐
│  0 │ test_database.test_table_3 │
└────┴────────────────────────────┘
```

It is also possible to work with this database Backup as with any ordinary database. For example query tables in it:

```
SELECT database, name FROM system.tables WHERE database = 'test_database_backup':

┌─database─────────────┬─name─────────┐
│ test_database_backup │ test_table_1 │
│ test_database_backup │ test_table_2 │
│ test_database_backup │ test_table_3 │
└──────────────────────┴──────────────┘
```

- [Creating a database](#creating-a-database)- [Usage example](#usage-example)

Was this page helpful?