MariaDB Tutorial / Restore a Backup into a Separate Database and Verify It

Verify a backup by restoring it into a different empty database and comparing definitions, rows, constraints and business totals. Inspect database-switching and destructive statements in the file first, then stop on errors so a partial restore is not confused with a complete one.

Inspect the dump rather than trust only the connection database

CREATE DATABASE, USE and fully qualified object names in a dump can direct work outside the database selected at connection. Review the trusted source, versions and generation options before running it. Dumps can contain DROP TABLE statements, so an existing data-bearing database is not a suitable restoration test target. Compare source-file conditions with Back Up an InnoDB Database with mariadb-dump and the committed-order restoration criteria in Build, Secure, Back Up, and Verify an Order Database.

Check the MariaDB documentation for the syntax and conditions used here: mariadb dump.

Prepare a separate source fixture and a new dump

An administrator must confirm restore47_source and restore47_target are unused. The complete source fixture is independent of the preceding backup article. If using that article’s file instead, use its actual table names and baselines rather than mix the two exercises.

CREATE DATABASE restore47_source CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE restore47_source;
CREATE TABLE c47_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c47_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c47_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c47_order_customer FOREIGN KEY (customer_id)
  REFERENCES c47_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c47_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c47_item_order FOREIGN KEY (order_id)
  REFERENCES c47_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c47_item_product FOREIGN KEY (product_id)
  REFERENCES c47_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c47_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c47_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c47_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c47_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

The SQL retains Korean sample names and city strings, while the explanations use stable customer and product IDs. Products 1–4 are a notebook, pen, mug and lamp. Customers 1 and 3 have the Seoul city value; customers 2 and 4 have Busan and Daejeon values. Preserve the supplied UTF-8 literals when reproducing the results.

Grant access only to the learning databases, create the empty target and write a new backup file. Prepare C:/mariadb-lab, ensure the filename is unused and check the dump command’s exit status before proceeding:

GRANT ALL PRIVILEGES ON restore47_source.* TO 'course_user'@'127.0.0.1';
CREATE DATABASE restore47_target CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL PRIVILEGES ON restore47_target.* TO 'course_user'@'127.0.0.1';

New-Item -ItemType Directory -Path 'C:/mariadb-lab' -Force | Out-Null
$courseBackupPath='C:/mariadb-lab/restore47-source.sql'
if (Test-Path -LiteralPath $courseBackupPath) { throw 'Choose a new backup filename.' }
mariadb-dump --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 --single-transaction --quick --skip-lock-tables --result-file=$courseBackupPath restore47_source
if ($LASTEXITCODE -ne 0) { throw 'Backup failed; do not restore this file.' }
Get-Item -LiteralPath $courseBackupPath | Select-Object Name,Length
Get-FileHash -LiteralPath $courseBackupPath -Algorithm SHA256

Ubuntu shell alternative: choose this or the Windows PowerShell version. The subshell confines the restrictive umask and noclobber settings to this backup operation.

mkdir -p "$HOME/mariadb-lab"
(
  umask 077
  set -C
  backup_file="$HOME/mariadb-lab/restore47-source.sql"
  if mariadb-dump --protocol=tcp --host=127.0.0.1 --port=3306 \
      --user=course_user --password --default-character-set=utf8mb4 \
      --single-transaction --quick --skip-lock-tables restore47_source > "$backup_file"; then
    test -s "$backup_file" || exit 1
    sha256sum "$backup_file"
  else
    printf '%s\n' 'Backup failed; preserve the partial file for diagnosis.' >&2
    exit 1
  fi
)

An existing destination causes failure rather than overwrite. Do not restore a partial file after a failed command; diagnose it and retry with a new filename. Size and SHA-256 provide evidence, not proof of recoverability.

For a TLS connection error, inspect server/client versions, certificate names, the trusted CA and any required client certificate using the official TLS connection requirements. Apply the appropriate option file or certificate options to backup and restoration clients too; do not routinely disable verification to bypass an error.

Verify the empty target and use compatible clients

Open a client to the target, inspect VERSION and DATABASE and verify SHOW TABLES is empty before SOURCE. The client option below stops source-file processing on an error. Read and trust the SQL contents before execution:

mariadb --abort-source-on-error --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 restore47_target

SELECT VERSION(),DATABASE();
SHOW TABLES;
SOURCE C:/mariadb-lab/restore47-source.sql;

Recent mariadb-dump output can contain a sandbox directive that older MariaDB or MySQL clients do not understand. Choose a compatible MariaDB dump/client combination rather than routinely delete an unfamiliar directive. Client compatibility is distinct from whether the file exists.

For Ubuntu restoration, open the same target using the following command, then replace the SOURCE path with the actual login name and absolute file path from your backup.

mariadb --abort-source-on-error --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 restore47_target

SELECT DATABASE();
SHOW TABLES;
SOURCE /home/learner/mariadb-lab/restore47-source.sql;

Run only one platform’s restoration example for a target. The source-error option stops later statements in the file; it does not undo objects or committed changes created earlier. After a failure, correct the cause and retry against a newly prepared empty target.

Compare object definitions, row contents and business totals

SELECT TABLE_NAME,TABLE_TYPE FROM information_schema.TABLES
WHERE TABLE_SCHEMA='restore47_target' ORDER BY TABLE_NAME;
SELECT (SELECT COUNT(*) FROM restore47_target.c47_customers) AS customers,
 (SELECT COUNT(*) FROM restore47_target.c47_products) AS products,
 (SELECT COUNT(*) FROM restore47_target.c47_orders) AS orders_count,
 (SELECT COUNT(*) FROM restore47_target.c47_items) AS detail_rows;
SELECT SUM(i.quantity*i.unit_price) AS paid_goods
FROM restore47_target.c47_orders o
JOIN restore47_target.c47_items i ON i.order_id=o.order_id WHERE o.status='paid';
SHOW CREATE TABLE restore47_target.c47_items;

Expect four base tables, counts of 4, 4, 4 and 6, and paid goods of 39000.00. Inspect primary and foreign keys and quantity/price checks through SHOW CREATE TABLE. Equal counts do not prove equal content: compare source rows by key and run the intended business queries too.

Real systems with views, routines, triggers and events require those definitions and behavior to be checked as well. Do not assume a database dump recreates every account, grant or server setting. Verify necessary defining and invoking accounts separately.

Stop at errors and identify partially restored objects

Syntax, privilege, DEFINER or character-set errors may happen after some objects already exist. Do not use --force to continue and then report success. Record the error and partial state, fix the cause and repeat verification in a newly prepared empty target.

Do not arbitrarily disable foreign-key checks to hide failures. Inspect any settings the dump itself saves and restores. A complete DDL restore is not one ordinary rollbackable transaction. Operational cutover, blocking application access and preventing follow-up writes require a separate controlled procedure.

Keep reproducible recovery evidence before cleanup

Record the dump hash, client and server versions, actual target, execution time and validation queries and outputs. Keep the source and backup until checks finish. Before removing a practice database, establish that it was created for this exercise and retain anything needed. No automatic DROP DATABASE command is included here.