Tutorial: Manage Database Change with 1-Click Data Rollback

Bytebase provides Prior Backup functionality that captures a snapshot of data before changes are applied. This safety mechanism stores the original state of affected rows, enabling you to revert data changes with 1-Click when necessary. You can perform multi-task rollbacks to efficiently revert multiple related changes across databases in one operation.
Backup data is stored within your own database instance in a dedicated bbdataarchive location. Bytebase does not automatically purge this backup data, so you can manually clean up the bbdataarchive database or schema as needed.

Supported Databases

DatabasePrerequisitesPrior Backup1-click Rollback
MySQLCreate a bbdataarchive database on the instance where your database is located.
PostgreSQLCreate a bbdataarchive schema on the database.
OracleCreate a bbdataarchive schema on the database.
SQL ServerCreate a bbdataarchive database on the instance where your database is located.

Supported Operations

Bytebase continues to expand backup and rollback support for additional SQL statement types.

Feasibility Conditions

Prior backup is feasible when meeting all of the following conditions:
  • The SQL statement size is less than 2M.
  • No mixed UPDATE/DELETE on the same table.
  • No mixed DDL/DML.
1-click rollback is feasible when meeting all of the following conditions:
  • For UPDATE, the changed table has primary key and primary key columns are not updated.

Enable Prior Backup

You can enable the Prior Backup option either before or after creating a database change issue. bb-issue-backup-on
If the Prior Backup switch appears disabled, navigate to the database page and click Sync instance to refresh the database metadata.

Perform 1-Click and Multi-Task Rollback

  1. After your database change has been successfully deployed, click the Rollback available link to initiate the rollback process. bb-issue-done-1
  2. You may see multiple rollbackable changes available, depending on your deployment scenario.
    This could include a single change applied to multiple databases, multiple changes to one database, or a combination of both scenarios.
    bb-issue-done-2
  3. Select which changes you want to rollback - you can choose individual changes or rollback all of them at once. This multi-task rollback capability allows you to efficiently revert multiple related changes in a single operation. bb-rollback-select
  4. Bytebase automatically generates the appropriate rollback SQL statements based on the backup data. bb-rollback-issue-1 bb-rollback-issue-2

Inspect Backup Data

You can examine the stored backup data using Bytebase’s SQL Editor to verify what information has been preserved. bb-sql-editor-test bb-sql-editor-prod

Project Backup Settings

Configure backup behavior at the project level for consistent data protection:
  • Default backup: Enable Prior Backup by default for all changes in the project
  • Error handling: Control whether to skip backup errors and proceed with data changes prior-backup-default

Appendix: Setting Up bbdataarchive

Before using Prior Backup functionality, you must create the bbdataarchive storage location in your database instance. Follow the instructions for your specific database engine:

MySQL

  1. Create the backup database:
    CREATE DATABASE bbdataarchive;
    
  2. Grant necessary privileges: Replace your_user with the actual username that Bytebase uses to connect to your database.
    GRANT ALL PRIVILEGES ON bbdataarchive.* TO 'your_user'@'%';
    FLUSH PRIVILEGES;
    

PostgreSQL

  1. Create the backup schema:
    CREATE SCHEMA bbdataarchive;
    
  2. Grant necessary privileges: Replace your_user with the actual username that Bytebase uses to connect to your database.
    GRANT ALL PRIVILEGES ON SCHEMA bbdataarchive TO your_user;
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA bbdataarchive TO your_user;
    

Oracle

  1. Create the backup user:
    CREATE USER bbdataarchive IDENTIFIED BY password;
    
  2. Grant connection privileges:
    GRANT CREATE SESSION TO bbdataarchive;
    
  3. Grant unlimited space quota: Replace tablespace_name with your actual tablespace name.
    GRANT QUOTA UNLIMITED ON tablespace_name TO bbdataarchive;
    
  4. Grant privileges to the instance administrator: Replace admin_user with the actual username that Bytebase uses as the instance administrator.
    GRANT CREATE ANY TABLE TO admin_user;
    GRANT SELECT ANY TABLE TO admin_user;
    

SQL Server

  1. Create the backup database:
    CREATE DATABASE bbdataarchive;
    
  2. Grant necessary privileges: Replace your_user with the actual username that Bytebase uses to connect to your database.
    USE bbdataarchive;
    GRANT CONTROL ON DATABASE::bbdataarchive TO your_user;