DocumentationCommerceCommerce KB

[PaaS only]{class="badge informative" title="Applies to Adobe Commerce on Cloud projects (Adobe-managed PaaS infrastructure) and on-premises projects only."}

Restore a DB snapshot from Staging or Production

Last update: Tue Jun 03 2025 00:00:00 GMT+0000 (Coordinated Universal Time)

This article shows how to restore a DB snapshot from Staging or Production on Adobe Commerce on Cloud Pro infrastructure.

NOTE
These methods will restore the full snapshot.
If you need to restore the snapshot partially—for example, only restoring the catalog tables while leaving the order tables intact—you must consult with your developer or DBA.

Affected products and versions

Choose the most appropriate for your case:

Method 1: Transfer the database dump to your local machine and import it meth2

NOTE
The format of the snapshot on Azure projects will be different and contains other databases that cannot be imported.
Before importing the snapshot, you must take additional steps to extract the appropriate database before proceeding with the dump import.

The steps are:

  1. Using SFTP, navigate to the location where the database snapshot has been placed, usually on the first server/node of your cluster (For example: /mnt/recovery-<recovery_id>).

    Azure-based projects:
    If your project is Azure-based (i.e., your project URL looks like https://us-a1.magento.cloud/projects/<cluster_id>), the snapshot will be placed in:

    • /mnt/shared/<cluster ID>/all-databases.sql.gz
    • /mnt/shared/<cluster ID_stg>/all-databases.sql.gz

    Azure-specific extraction steps

    For Production:

    code language-bash 
    cd /mnt/shared/<cluster ID>/
gunzip all-databases.sql.gz
head -n 17 all-databases.sql > <cluster ID>.sql
sed -n '/^-- Current Database: `<cluster ID>`/,/^-- Current Database: `/p' all-databases.sql >> <cluster ID>.sql gzip <cluster ID>.sql
zcat <cluster ID>.sql.gz | \
sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | \
mysql -h 127.0.0.1 \
-u $DB_USER \
--password=$MYSQL_PWD $DB_NAME \
--init-command="SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT ;SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS ;SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION ;SET NAMES utf8 ;SET @OLD_TIME_ZONE=@@TIME_ZONE ;SET TIME_ZONE='+00:00' ;SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 ;SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 ;SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' ;SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;"

    For Staging:

    code language-bash 
    cd /mnt/shared/<cluster ID_stg>/
gunzip all-databases.sql.gz
head -n 17 all-databases.sql > <cluster ID_stg>.sql
sed -n '/^-- Current Database: `<cluster ID_stg>`/,/^-- Current Database: `/p' all-databases.sql >> <cluster ID_stg>.sql
gzip <cluster ID_stg>.sql
zcat <cluster ID_stg>.sql.gz | \
sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | \
mysql -h 127.0.0.1 \
-u $DB_USER \
--password=$MYSQL_PWD $DB_NAME \
--init-command="SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT ;SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS ;SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION ;SET NAMES utf8 ;SET @OLD_TIME_ZONE=@@TIME_ZONE ;SET TIME_ZONE='+00:00' ;SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 ;SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 ;SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' ;SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;"

  2. Copy the database dump file (For example: <cluster ID>.sql.gz for Production or <cluster ID_stg>.sql.gz for Staging) to your local computer.

  3. Make sure you have set up the SSH tunnel to connect to the database remotely: SSH and sFTP: SSH tunneling in our developer documentation.

  4. Connect to the database.

    code language-bash 
    mysql -h <db-host> -P <db-port> -p -u <db-user> <db-name>

  5. Drop the database; at the MariaDB prompt, enter:

    (For Production)

    code language-bash 
    drop database <cluster ID>;

    (For Staging)

    code language-bash 
    drop database <cluster ID_stg>;

  6. Enter the following command to import the snapshot:

    (For Production)

    code language-bash 
    zcat <cluster ID>.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -h 127.0.0.1 -P <db-port> -p -u   <db-user> <db-name>

    (For Staging)

    code language-bash 
    zcat <cluster ID_stg>.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -h 127.0.0.1 -P <db-port> -p -u   <db-user> <db-name>

Method 2: Import the database dump directly from the server meth3

The steps are:

  1. Navigate to the location where the database snapshot has been placed, usually on the first server/node of your cluster (For example: /mnt/recovery-<recovery_id>).

  2. To drop and re-create the cloud database, first connect to the database:

    code language-bash 
    mysql -h 127.0.0.1 -P <db-port> -p -u <db-user> <db-name>

  3. Drop the database; at the MariaDB prompt, enter:

    (For Production)

    code language-bash 
    drop database <cluster ID>;

    (For Staging)

    code language-bash 
    drop database <cluster ID_stg>;

  4. After dropping the database, recreate the database:

    code language-bash 
    create database [database_name];

  5. Enter the following command to import the snapshot:

    (For importing the database backup from Production)

    code language-bash 
    zcat <cluster ID>.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -h 127.0.0.1 -p -u <db-user> <db-name>

    (For importing the database backup from Staging)

    code language-bash 
    zcat <cluster ID_stg>.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -h 127.0.0.1 -p -u <db-user> <db-name>

    (For importing a database backup from any other environment)

    code language-bash 
    zcat <database-backup-name>.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -h 127.0.0.1 -p -u <db-user> <db-name>

    (For importing a database backup from any other environment)

    code language-bash 
    zcat <database-backup-name>.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -h 127.0.0.1 -p -u <db-user> <db-name>

In our developer documentation:

recommendation-more-help
8bd06ef0-b3d5-4137-b74e-d7b00485808a