Restore a DB snapshot from Staging or Production

Last update: June 3, 2025

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

Adobe Commerce on cloud infrastructure, all supported versions

Choose the most appropriate for your case:

Method 1: Transfer the database dump to your local machine and import it.
Method 2: Import the database dump directly from the server.

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

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:

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:

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:

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

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.
Make sure you have set up the SSH tunnel to connect to the database remotely: SSH and sFTP: SSH tunneling in our developer documentation.

Connect to the database.

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

Drop the database; at the MariaDB prompt, enter:

(For Production)
drop database <cluster ID>;
(For Staging)
drop database <cluster ID_stg>;

Enter the following command to import the snapshot:

(For Production)

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)

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

The steps are:

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>).
To drop and re-create the cloud database, first connect to the database:
mysql -h 127.0.0.1 -P <db-port> -p -u <db-user> <db-name>
Drop the database; at the MariaDB prompt, enter:

(For Production)
drop database <cluster ID>;
(For Staging)
drop database <cluster ID_stg>;
After dropping the database, recreate the database:
create database [database_name];

Enter the following command to import the snapshot:

(For importing the database backup from Production)

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)

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)

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)

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

Restore a DB snapshot from Staging or Production

Affected products and versions

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

Method 2: Import the database dump directly from the server

Related reading