Skip to content

Re-Create ClickHouse Backups#

This procedure applies when you have valid SQL Server backups from a production environment but do not have the corresponding ClickHouse backups, and it is not possible to generate them now because the production system continues to generate new data.

Since the data stored in SQL Server and ClickHouse are time-dependent, restoring SQL Server backups alone would lead to inconsistencies between the two data sources.

Ideally, both SQL Server and ClickHouse backups should be generated at the same time. However, if this is not feasible, follow the steps below to re-create compatible ClickHouse backups for environment migration or recovery purposes.

Prerequisites#

  • Access to the SQL Server backups of the source (production) environment.
  • The MES installation package of the same version as the production environment (for example, 11.2.0).

Procedure#

Step 1: Install a Temporary Customer Environment#

  1. Create a new customer environment using the same MES version as the one where the SQL Server backups were generated. For example, if the backups were taken from version 11.2.0, follow the instructions in the Installation guide.

  2. During the Package Configuration step, set the Database Mode to MES Analytics, ensuring that all ClickHouse databases and objects are created during the installation.

  3. Do not use the existing SQL Server backups during this installation, as the objective of this step is to create a clean environment that replicates the schema, not the data.

Step 2: Generate ClickHouse Backups#

  1. Once the environment installation is completed and all Integration Entries have been successfully processed, generate the ClickHouse database backups. Follow the procedure described in ClickHouse Database Backup and Restore.

  2. Ensure that backups are created for all ClickHouse databases (for example, MES, CDM, ODS, and DWH, depending on the MES version).

  3. After the backups have been successfully created, this temporary environment can be uninstalled if desired.

Step 3: Install the Target Environment#

  1. Proceed to install the target environment (that is, the copy of the production environment). To do so, use the existing SQL Server backups and the newly generated ClickHouse backups.

  2. Verify that both the SQL Server and ClickHouse databases have been correctly restored and that all integrations complete successfully.

Step 4: Populate ClickHouse with Historical Data#

  1. After the installation, follow the steps described in the Data Processing guide to populate the ClickHouse databases with data from the SQL Server ODS.

  2. When defining the date range for data processing, include all historical data present in the SQL Server backups as well as any new data generated since then. This ensures that CDM generation remains consistent and complete.

Warning

This process is intended only as a recovery alternative when the original ClickHouse backups are unavailable. It is recommended to always generate SQL Server and ClickHouse backups simultaneously in production systems to maintain data consistency across analytical layers. For more information, ClickHouse Database Backup and Restore.