WITH BACKUP option to RESTORE (point-in-time recovery of system-versioned tables)

WITH BACKUP extension

RESTORE may include an optional WITH BACKUP clause:

RESTORE

    { TABLE table_name [, table_name] ...

    | DATABASE database_name }

    FOR SYSTEM_TIME AS OF as_of_value

    [WITH BACKUP]

    [WAIT wait_seconds | NOWAIT];

When WITH BACKUP is specified, RESTORE must create backup copies of all target tables before modifying them.

The backup names are generated using:

• @@system_versioning_backup_prefix
• @@system_versioning_backup_suffix
• @@system_versioning_backup_type

System variables

The effective backup name is constructed as follows:

backup_name =

    @@system_versioning_backup_prefix

    + original_name

    + @@system_versioning_backup_suffix

Prefix and suffix values are used verbatim. Identifier quoting does not make a generated name valid if it exceeds the maximum identifier length.

TABLE mode

When @@system_versioning_backup_type is TABLE, backup tables are created in the same database as their source tables.

The backup table name is:

backup_table_name =

    @@system_versioning_backup_prefix

    + original_table_name

    + @@system_versioning_backup_suffix

Example:

SET @@system_versioning_backup_type = 'TABLE';

SET @@system_versioning_backup_prefix = '';

SET @@system_versioning_backup_suffix = '_before_restore';

RESTORE TABLE accounting.invoice

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

WITH BACKUP;

This creates:

accounting.invoice_before_restore

For a table list, one backup table is created for every target table in its original database.

DB mode

When @@system_versioning_backup_type is DB, a separate backup database is created.

The backup database name is:

backup_database_name =

    @@system_versioning_backup_prefix

    + original_database_name

    + @@system_versioning_backup_suffix

Backup tables retain their original table names.

Example:

SET @@system_versioning_backup_type = 'DB';

SET @@system_versioning_backup_prefix = '';

SET @@system_versioning_backup_suffix = '_before_restore';

RESTORE DATABASE accounting

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

WITH BACKUP;

This creates backup tables such as:

accounting_before_restore.invoice

accounting_before_restore.invoice_item

accounting_before_restore.payment

If RESTORE TABLE contains tables from multiple source databases, one backup database is created for each source database.

AUTO mode

When @@system_versioning_backup_type is AUTO, the effective mode depends on the RESTORE target:

Example:

SET @@system_versioning_backup_type = 'AUTO';

SET @@system_versioning_backup_suffix = '_before_restore';

RESTORE TABLE accounting.invoice

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

WITH BACKUP;

The backup is created as:

accounting.invoice_before_restore

For a database restore:

RESTORE DATABASE accounting

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

WITH BACKUP;

The backup database is:

accounting_before_restore

Backup contents

Each backup table must represent the source table exactly as it exists immediately before RESTORE starts.

The backup must include:

• The current rows
• Existing historical row versions
• System-versioning period definitions
• Columns, indexes, and table options required to restore the original table
• The table state before any RESTORE-generated changes

A single statement-level snapshot must be used for all backup tables.

Validation and failure handling

Before creating or modifying any objects, RESTORE WITH BACKUP must validate:

• All generated database and table names are valid
• Generated names do not exceed identifier-length limits
• No generated backup table or database already exists
• The user has privileges to create all required backup objects
• Sufficient historical data exists for the requested restore point
• All target tables can be restored
• The selected backup type is valid for the RESTORE target

If validation or backup creation fails, RESTORE must not modify any target table.

Existing backup objects must not be overwritten, truncated, or reused implicitly. A naming collision must produce an error identifying the conflicting object.

If restoration fails after all backups have been created, the restore changes must be rolled back. Successfully created backup objects should remain available for recovery and diagnostics.

Privileges

RESTORE WITH BACKUP requires:

• The privileges normally required by RESTORE
• CREATE privilege for every generated backup database
• CREATE privilege for every generated backup table
• Required privileges to copy the complete source-table definition and data

All privilege checks must complete before backup creation or target-table modification.

Replication

Backup creation must be binary logged together with RESTORE.

The source table list, resolved AS OF value, effective backup mode, and generated object names must be deterministic on replicas. Replicas must not independently evaluate different prefix, suffix, or backup-type values.

Additional acceptance criteria

1. RESTORE without WITH BACKUP does not create backup objects.
2. TABLE mode creates backup tables in each source table's database.
3. TABLE mode applies the configured prefix and suffix to table names.
4. DB mode creates backup databases using the configured prefix and suffix.
5. DB mode preserves original table names inside backup databases.
6. AUTO selects TABLE mode for RESTORE TABLE.
7. AUTO selects DB mode for RESTORE DATABASE.
8. A multi-database RESTORE TABLE in DB mode creates one backup database per source database.
9. Backup tables contain the complete pre-restore state.
10. Backup creation completes before target tables are modified.
11. Existing backup-object names cause an error without modifying target tables.
12. Invalid or overlong generated identifiers cause an error.
13. Failure to create any backup prevents the restore.
14. Backup naming and creation are deterministic under replication.