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

Prior backup is feasible when meeting all of the following conditions:
  • UPDATE or DELETE
  • No mixed UPDATE/DELETE on the same table.
  • The SQL statement size is less than 2M.
  • 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;