A 115 GB Bitrix24 backup without a single deal in it

The database backup weighed 115 GB, sat in the list of backups and looked complete. When we tried to restore it on a test server, it turned out to hold 288 tables out of about a thousand. No users, no deals, no tasks, no messages.

Nobody knew. The backup counted as working until the first attempt to restore it.

What was inside

The built-in Bitrix24 backup produced files of 115 to 120 GB. A respectable size, a fresh date, a "complete" mark. The backup had no b_user, b_crm_deal, b_im_message, b_tasks_* or b_disk_* tables. In other words, nothing a backup exists for.

The dump stopped at b_crm_access_attr_dynamic_1154. The only explanation was in the event log, under the BACKUP_ERROR filter:

Table b_crm_access_attr_dynamic_1160 is corrupted due to an internal MySQL error.

The table isn't corrupted, it doesn't exist

The Bitrix message sends you the wrong way. b_crm_access_attr_dynamic_1160 isn't damaged. It simply doesn't exist. SHOW CREATE TABLE returns error 1146, "table doesn't exist", and the MySQL data dictionary has no trace of it.

Here is how it happened:

1. At the start of a run, Bitrix builds a list of tables and works through it.
2. On a database this size, a full pass took about a week. The file itself shows it: the name says July 28, the last write was August 4.
3. During that week someone deleted a smart process. A routine operation that any CRM administrator does. Its permissions table went with it.
4. A few days later the export reached that table on its list, couldn't find it and stopped.
5. Tables are exported in alphabetical order, and the break came early. Everything that mattered never made it into the backup.

This isn't a one-off. Smart process IDs on this portal have more than fifteen gaps: they get deleted regularly, and any deletion during a week-long run breaks the backup again.

Why nobody saw it

A failed backup shows up only as an entry in the event log. The log is purged after its retention period, and there are no failure alerts. The administrator sees a file of the right size with the right date and has no reason to doubt it.

The owner sees even less. In the work plan, "backups configured" is a closed item, and it comes up again on the day a backup is really needed. That is the day you find out there is nothing to restore.

A complete backup can fail too

The next backup ran all the way through: 2,230 tables. Restoring it failed halfway:

ERROR 1062 (23000): Duplicate entry '7920399-1141386-2' for key 'b_crm_timeline_bind.PRIMARY'

A duplicate primary key means the same row was exported twice. Bitrix reads tables in chunks without a transaction while the portal keeps working. For tables without a numeric key, it reads by offset (LIMIT offset, N). A new row in the middle of the table shifts the offsets, and some rows get exported again. The export runs for hours, so the odds of a duplicate are high.

When restoring by hand you can work around it: replace INSERT with INSERT IGNORE, and the repeated row is dropped. Nothing is lost, it's the same row. But the point stands: a backup can contain every table and still not restore without manual work.

What to do about it

There is nothing to repair in the table. The options:

• Restart the backup. The table list is rebuilt and correct. But it's a week-long gamble: the next deleted smart process or pipeline breaks it again.
• Freeze deletions of smart processes, pipelines and infoblocks during the run. A workaround for one attempt. On a portal with hundreds of employees it won't hold.
• Make the run shorter. This is the real fix. On this portal, the business process log b_bp_tracking took 182 GB, while the working data (deals, tasks, messages, users) was about 15 GB. Without that log, the backup goes from a week to about an hour, and the window for this failure almost closes.

And one rule that always holds: a backup is verified only once you have restored from it. File size, date and a green mark mean nothing.

How to check your backup

Quick check: are the main tables in the dump at all? If there are no users, you can stop there:

grep -c '^DROP TABLE IF EXISTS `b_user`' dump.sql

Count the tables in the dump and compare with the live database:

LC_ALL=C grep -ohE '^DROP TABLE IF EXISTS `[^`]+`' dump.sql | sort -u | wc -l

Check that the permission tables match the smart processes that actually exist. If there are more or fewer tables than live types, the next run may trip:

SELECT ENTITY_TYPE_ID, TITLE FROM b_crm_dynamic_type ORDER BY ENTITY_TYPE_ID;

SELECT TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE 'b_crm_access_attr_dynamic_%';

And once a month or once a quarter, restore a backup on a separate server. It's the only check after which you can say you have a backup.