Tutorial: Manage Database Change with 1-Click Data Rollback

 Bytebase allows taking Prior Backup before a data change is made. Bytebase stores the pre-snapshot of the affected rows, which allows you to revert that data change if needed.
The backup data is stored in your own database instance. Bytebase does not purge the backup data automatically. You can manually delete the data in bbdataarchive database/schema.

​
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

We are working on enabling backup and 1-click rollback for more SQL statements.

​
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.

​
Create backup

You can toggle Prior Backup switch before or after creating the issue. bb-prior-bk-on
If the switch is disabled, go to the database page and click Sync instance first.

​
1-click rollback

  1. Once the change has been rolled out. You can click the Rollback button to create the rollback issue. bb-prior-bk-rollback
  2. The rollback issue automatically populates the rollback statements. bb-prior-bk-rollback-issue

​
Inspect the backup data

You can go to SQL Editor to inspect the backup data. If the DML change contains no more than 5 statements, then each statements will be backed up to a separate table. bb-prior-bk-separate-table If the DML change contains more than 5 statements, then all statements will be backed up to a single table. bb-prior-bk-single-table-issue bb-prior-bk-single-table

​
Project backup settings

  • You can enable Prior Backup by default.
  • You can control whether you want to skip backup errors and continue changing the data.
prior-backup-default

​
Appendix: Setting Up bbdataarchive

​
MySQL

  1. Create the bbdataarchive Database: 
    CREATE DATABASE bbdataarchive;
  2. Grant Necessary Privileges: Replace your_user with the actual username. 
    GRANT ALL PRIVILEGES ON bbdataarchive.* TO 'your_user'@'%';
FLUSH PRIVILEGES;

​
PostgreSQL

  1. Create the bbdataarchive Schema: 
    CREATE SCHEMA bbdataarchive;
  2. Grant Necessary Privileges: Replace your_user with the actual username. 
    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 bbdataarchive User: 
    CREATE USER bbdataarchive IDENTIFIED BY password;
  2. Grant Connection Privileges: 
    GRANT CREATE SESSION TO bbdataarchive;
  3. Grant unlimited space quota to the bbdataarchive user on the specified tablespace: Replace tablespace_name with the 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 of the instance administrator. 
    GRANT CREATE ANY TABLE TO admin_user;
GRANT SELECT ANY TABLE TO admin_user;

​
SQL Server

  1. Create the bbdataarchive Database: 
    CREATE DATABASE bbdataarchive;
  2. Grant Necessary Privileges: Replace your_user with the actual username. 
    USE bbdataarchive;
GRANT CONTROL ON DATABASE::bbdataarchive TO your_user;