ClickHouse DB::Exception: Cannot restore database

The "DB::Exception: Cannot restore database" error is raised when ClickHouse fails to restore a database from a backup. The CANNOT_RESTORE_DATABASE error code covers a range of situations where the restore process cannot proceed -- typically because the target database already exists, there is a schema conflict, or the database engine is incompatible.

Impact

The entire database restore fails, leaving the target server without the expected data. This can be critical during disaster recovery, migration, or environment provisioning workflows. Depending on the cause, some tables within the database may have partially restored while others did not, which can leave the system in an inconsistent state.

Common Causes

1. Target database already exists -- ClickHouse will not overwrite an existing database during restore unless explicitly told to.
2. Database engine mismatch -- the backup contains a database created with one engine (e.g., Replicated) but the target environment expects or supports a different engine.
3. Insufficient privileges -- the user executing the restore lacks CREATE DATABASE or CREATE TABLE permissions.
4. Conflicting database settings -- the backup's database-level settings are incompatible with the target server's configuration.
5. ZooKeeper or Keeper path conflicts -- for Replicated databases, the ZooKeeper path already has metadata from a different database or cluster.

Troubleshooting and Resolution Steps

1. Check if the database already exists:

   SHOW DATABASES;
   

   If it does and you want to replace it, drop it first:

   DROP DATABASE IF EXISTS my_db;
   

2. Use the allow_non_empty_tables setting if you want to restore into an existing database:

   RESTORE DATABASE my_db FROM Disk('backups', 'my_backup')
   SETTINGS allow_non_empty_tables = 1;
   

3. Restore with a different database name using the AS clause:

   RESTORE DATABASE my_db AS my_db_restored FROM Disk('backups', 'my_backup');
   

4. Check user permissions:

   SHOW GRANTS FOR current_user();
   -- Needs CREATE DATABASE, CREATE TABLE, INSERT privileges
   

5. For Replicated database conflicts, clear the ZooKeeper path or use a different one:

   -- Check what paths are in use
   SELECT * FROM system.zookeeper WHERE path = '/clickhouse/databases/my_db';
   

6. Review the database engine in the backup and ensure the target server supports it:

   # Check the backup metadata for the CREATE DATABASE statement
   cat /var/lib/clickhouse/backups/my_backup/metadata/my_db.sql
   

7. Adjust the restore command to use a compatible engine if needed:

   CREATE DATABASE my_db ENGINE = Atomic;
   RESTORE DATABASE my_db FROM Disk('backups', 'my_backup')
   SETTINGS allow_non_empty_tables = 1;
   

Best Practices

• Before restoring, always check whether the target database already exists and decide whether to drop it or restore alongside it with a different name.
• In replicated environments, coordinate restores to avoid ZooKeeper path conflicts.
• Test restore procedures in a non-production environment to uncover engine and configuration mismatches early.
• Grant restore users the minimum necessary privileges: CREATE DATABASE, CREATE TABLE, and INSERT.
• Document the database engine and settings used in production so you can match them during restore.

Frequently Asked Questions

Q: Can I restore a database backup into an existing database without dropping it first?
A: Yes, but only if you use settings like allow_non_empty_tables = 1. Without this, ClickHouse refuses to restore into a database that already has tables to prevent accidental data overwrites.

Q: What happens if I restore a Replicated database on a server without ZooKeeper?
A: The restore will fail because the Replicated database engine requires a running ZooKeeper or ClickHouse Keeper instance. You would need to either set up Keeper or change the database engine.

Q: Can I change the database engine during restore?
A: Not directly in the RESTORE command. However, you can create the database with the desired engine first, then restore into it. ClickHouse will use the existing database engine rather than the one from the backup.

Q: Why did only some tables restore while others failed?
A: Individual table-level errors (schema conflicts, engine issues) may cause specific tables to fail while others succeed. Check the ClickHouse error log for per-table error details.