Two BridgeGate System Schedules exist to back up the Database.
Database Backup Transaction Tables – This backup is used to quickly restore the BridgeGate database excluding Transaction rows.
- This schedule creates a .sql or .zip backup file (database_backup_YYMMDD.sql) containing all stored procedures, functions, views, and all MySQL administrative tables except: bg_transaction,bg_workflow_item,bg_udt_admin_adapter,bg_udt_admin_system,bg_service_history,bg_schedule_history,bg_transaction_link.
Database Backup Transaction Tables:
- Creates a daily .sql or .zip file containing backup table data for each day as defined in the Active Transaction Retention Days control, starting with the previous days transactions. This schedule backs up the following tables: bg_transaction,bg_workflow_item,bg_udt_admin_adapter,bg_udt_admin_system,bg_service_history,bg_schedule_history,bg_transaction_link and puts the .sql or .zip file in the ACTIVE_TRANSACTION_LOCATION=”/bridgegate/db_history” as defined in the BridgeGate.xml.
- Moves all .sql or .zip Active Transaction files that are older than the Active Transaction Retention Days value and puts them in the Historical Transaction backup location HISTORICAL_TRANSACTION_LOCATION=”/bridgegate/db_history/backup” as defined in the BridgeGate.xml.
- Deletes all Historical Transaction Files from the Historical Transaction Location older than the Historical Transaction Retention Days value.
In BridgeGate 9.0 the database backup files are stored in several locations.
The locations are controlled by the bridgegate.xml <DB_ARCHIVE attribute settings
LOCATION is the location of the administrative tables( Accounts, Users) used to control BridgeGate. This backup has no transaction or workflow information
HISTORICAL_TRANSACTION_LOCATION is the location of the backup sql files for the transactions have been purged from the database but are help for historical processing. They can be loaded using the portal Restore Transaction History feature.
ACTIVE_TRANSACTION_LOCATION is the location of the backup sql files for the transactions that are currently in the database
Use the following steps to restore the database.
Using \bridgegate\db_history as the value for all attributes above. Change path to match your configuration
Step 1) shutdown bridgegate
Step 2) get mysql in a stable state without the bridgegate schema. This can be done by dropping the bridgegate schema or removing the data files or reinstalling mysql.
Step 3) with Mysql started load the latest administrative sql file mysql -u bridgegate -p <\bridgegate\db_history\database_backup_YYMMDD.sql
Step 4) Load the newest transaction sql file. mysql -u bridgegate -p < \bridgegate\db_history\YYYY\bg_transactions_YYMMDD.sql (Note this will give you one full day of transaction history)
Step 5) Start BridgeGate. The system is now ready
Step 6) Using the BridgeGate Portal Restore Transaction History page, load the remaining active transactions. This can be done off hours.