RESTORE statement for point-in-time recovery of system-versioned tables

Implement a new SQL statement named RESTORE that restores the current data of system-versioned tables to a specified historical point.

The statement must support:

• One table
• A list of tables
• All system-versioned tables in a database
• Timestamp-based restore points
• Transaction-ID-based restore points where transaction-precise history is supported

RESTORE must preserve existing history. Restoration is performed as logical DML: required INSERT, UPDATE, and DELETE operations create new system-versioned row versions.

Rationale

System-versioned tables retain previous row versions and allow applications to query the table as it existed at a specific point using FOR SYSTEM_TIME AS OF. However, there is no direct SQL statement for restoring that historical snapshot as the new current state.

Administrators currently need to implement this manually by:

• Querying historical rows into temporary or staging tables
• Comparing historical and current states
• Generating the required INSERT, UPDATE, and DELETE operations
• Coordinating changes across related tables
• Handling locking, privileges, constraints, replication, and failures
• Ensuring that a partially completed restore can be rolled back

Alternatively, administrators can use dump or physical-backup workflows. These approaches require external tools and may involve exporting data, preparing backups, importing tables, stopping the server, or restoring more data than required.

A native RESTORE statement would:

• Provide a simple and explicit point-in-time recovery operation
• Allow recovery of one table, a related set of tables, or an entire database
• Apply one consistent AS OF point across all target tables
• Guarantee atomic restoration instead of relying on application-side scripts
• Preserve changes made after the requested point as system-versioned history
• Make accidental UPDATE and DELETE recovery faster and less error-prone
• Avoid restoring unrelated databases or replacing the complete data directory
• Integrate restore behavior with MariaDB locking, privileges, constraints, binary logging, replication, and diagnostics

The feature complements FOR SYSTEM_TIME AS OF: the existing clause reads a historical state, while RESTORE makes that state current again without discarding the audit trail.

Proposed syntax

RESTORE

    { TABLE table_name [, table_name] ...

    | DATABASE database_name }

    FOR SYSTEM_TIME AS OF as_of_value

    [WAIT wait_seconds | NOWAIT];

as_of_value:

      timestamp_expression

    | TRANSACTION transaction_id

Examples

Restore one table:

RESTORE TABLE accounting.invoice

FOR SYSTEM_TIME AS OF TIMESTAMP '2026-09-28 10:30:00.000000';

Restore multiple tables:

RESTORE TABLE accounting.invoice,

              accounting.invoice_item,

              accounting.payment

FOR SYSTEM_TIME AS OF TIMESTAMP '2026-09-28 10:30:00.000000';

Restore all system-versioned tables in a database:

RESTORE DATABASE accounting

FOR SYSTEM_TIME AS OF TIMESTAMP '2026-09-28 10:30:00.000000';

Restore using a transaction ID:

RESTORE TABLE accounting.invoice

FOR SYSTEM_TIME AS OF TRANSACTION 12345;

Fail immediately if required locks cannot be acquired:

RESTORE DATABASE accounting

FOR SYSTEM_TIME AS OF TIMESTAMP '2026-09-28 10:30:00.000000'

NOWAIT;

Functional requirements

• RESTORE changes current table data but does not restore historical table definitions.
• The current table schema remains unchanged.
• Existing historical rows must not be deleted or rewritten.
• Rows missing from the historical snapshot must be deleted from the current state.
• Rows present only in the historical snapshot must be inserted into the current state.
• Rows whose values differ must be updated.
• Changes produced by RESTORE must create normal system-versioned history.
• The AS OF expression must be evaluated once per statement.
• The same resolved restore point must be used for every target table.
• Explicitly listed tables must be system-versioned base tables.
• RESTORE DATABASE must target all system-versioned base tables in the specified database.
• RESTORE DATABASE must ignore ordinary tables, views, sequences and temporary tables.
• Duplicate table names in the explicit target list must produce an error.
• The statement must reject a restore point for which sufficient history is unavailable.
• TRANSACTION restore points must be rejected for tables that do not support transaction-precise history.
• Generated system-period columns must not be assigned directly.
• WAIT and NOWAIT must control metadata-lock acquisition.

Atomicity

For multiple-table and database restores, all target tables must be restored as one atomic operation:

• Resolve and validate all targets before modifying data.
• Acquire metadata locks in deterministic order.
• Roll back changes to all target tables if any table fails.
• Reject non-transactional target tables unless statement-level rollback can be guaranteed.
• NOWAIT must fail without modifying data if any required lock cannot be acquired.

Privileges

Introduce a dedicated RESTORE privilege.

The user must have:

• RESTORE privilege for each target table or database
• SELECT access to historical table data
• Required INSERT, UPDATE and DELETE privileges on target tables

Privilege validation must complete before any data is modified.

Foreign keys and triggers

• Foreign-key constraints must be validated against the final restored state.
• Tables should be processed in an order that does not expose an intermediate inconsistent state.
• Define whether user triggers execute during RESTORE.
• If triggers execute, any trigger failure must roll back the entire statement.
• RESTORE must not permit foreign_key_checks to silently produce an invalid final state.

Replication and logging

• RESTORE must be binary logged.
• Replicas must produce the same final current state and history.
• Statement-based logging must be rejected or switched to row-based logging if deterministic replay cannot be guaranteed.
• The resolved AS OF value should be logged rather than re-evaluating a nondeterministic expression on replicas.

Result and diagnostics

On success, expose:

• Number of restored tables
• Number of inserted rows
• Number of updated rows
• Number of deleted rows
• Number of warnings

Suggested errors:

Table 'db.table' is not system-versioned

AS OF point predates retained history for table 'db.table'

TRANSACTION AS OF is not supported by table 'db.table'

RESTORE of multiple tables requires transactional storage engines

Duplicate table 'db.table' in RESTORE target list

Unable to acquire required metadata lock

Acceptance criteria

1. Parser accepts RESTORE TABLE with one table.
2. Parser accepts RESTORE TABLE with multiple comma-separated tables.
3. Parser accepts RESTORE DATABASE.
4. Timestamp and transaction-ID restore points are supported.
5. Restored current rows equal the result of FOR SYSTEM_TIME AS OF for the requested point.
6. Data modified after the restore point remains accessible as history.
7. RESTORE itself creates new row versions.
8. Multi-table restore is atomic.
9. Database restore processes only system-versioned base tables.
10. Invalid or insufficient historical data produces an error without partial changes.
11. WAIT and NOWAIT behave consistently with other metadata-locking statements.
12. Required privilege checks occur before data modification.
13. RESTORE is deterministic under replication.
14. Server documentation and SQL grammar documentation are updated.

Test coverage

Add MTR tests covering:

• Single-table restore
• Multiple-table restore
• Whole-database restore
• Timestamp and transaction-ID restore points
• INSERT, UPDATE and DELETE reconstruction
• Empty table at the requested point
• Empty current table
• Purged or incomplete history
• Non-versioned target table
• Mixed versioned and non-versioned table list
• Duplicate table names
• Generated and invisible columns
• Primary-key changes
• Unique-key conflicts
• Foreign-key relationships
• Trigger behavior
• Transaction rollback after an error
• Metadata-lock conflicts
• WAIT and NOWAIT
• Insufficient privileges
• Statement and row-based replication
• Crash recovery during RESTORE
• Concurrent DML against target tables
• ALTER TABLE attempted during RESTORE

Documentation

Document:

• Complete syntax
• Supported restore-point expressions
• Data-only semantics
• History-preservation behavior
• Atomicity guarantees
• Locking behavior
• Privilege requirements
• Foreign-key and trigger behavior
• Replication behavior
• Limitations caused by schema changes or purged history