Skip to main content

Backup and Recovery for Babelfish on FoundryDB

· 5 min read
FoundryDB Team
FoundryDB Team
Engineering @ FoundryDB

Babelfish stores everything in a standard PostgreSQL database (babelfish_db). Your T-SQL schemas, tables, stored procedures (compiled to PL/pgSQL internally), triggers, and views are all PostgreSQL objects underneath. This means the backup story for Babelfish is exactly the backup story for PostgreSQL: pgbackrest, WAL archiving, and point-in-time recovery.

This post documents how backups work on a live Babelfish 4.8.0 instance, using confirmed results from FoundryDB staging.

If you have not yet provisioned a Babelfish instance, see Getting Started with Babelfish on FoundryDB first.

What Happens at Provisioning​

The moment your Babelfish service finishes provisioning, FoundryDB triggers an automatic full backup. You do not need to configure anything.

From a live provisioning run:

  • Backup method: pgbackrest
  • Backup type: full
  • Size: 5,822,544 bytes (approximately 5.6 MB for a fresh instance)
  • Duration: 69 seconds (17:47:30 to 17:48:39)
  • Encryption: AES-256-CBC with a per-instance key

Every instance gets its own encryption key, identified in the backup metadata. The key ID from the test instance was pgbr_478102bb. Backups are stored in the managed-database-backups S3 bucket under the path mssql/<instance-id>.

Default retention for automatic backups is 7 days.

Listing Backups via the API​

curl -u admin:PASSWORD \
  https://api.foundrydb.com/managed-services/SERVICE_ID/backups

Each backup record includes:

FieldExample
id8afd949a-1ea3-4102-8d2f-3c707f5fa878
backup_typefull or manual
statuspending, completed, or failed
backup_size_bytes6239072
started_at2026-04-14T17:51:13Z
completed_at2026-04-14T17:52:26Z
backup_methodpgbackrest
encryption_enabledtrue
encryption_algorithmAES-256-CBC
encryption_key_idpgbr_478102bb
retention_days7
expires_atderived from completed_at + retention
s3_path_prefixmssql/INSTANCE_ID

Triggering a Manual Backup​

Any time you want a backup outside the automatic schedule, POST to the backups endpoint:

curl -u admin:PASSWORD \
  -X POST https://api.foundrydb.com/managed-services/SERVICE_ID/backups \
  -H "Content-Type: application/json" \
  -d '{"backup_type":"full"}'

The response is immediate. FoundryDB queues the backup as an agent task and returns a task ID you can use for status polling:

{
  "backup_id": "8afd949a-1ea3-4102-8d2f-3c707f5fa878",
  "message": "Backup creation initiated",
  "status": "pending",
  "task_id": "876720b2-8a42-43ee-8ed4-009b0c47dc36"
}

The backup runs asynchronously on the VM. Poll the backups list and look for "status": "completed" against your backup_id.

From the live test, a manual backup taken after inserting test data produced:

  • Size: 6,239,072 bytes (approximately 5.9 MB, slightly larger because of the test data)
  • Duration: 73 seconds (17:51:13 to 17:52:26)
  • Same encryption settings as the automatic backup

What the Backup Contains​

pgbackrest operates at the PostgreSQL filesystem and WAL level. It backs up the underlying babelfish_db data directory. Because Babelfish stores everything in PostgreSQL, a backup captures the complete state of your Babelfish instance:

  • All schemas created over TDS (CREATE DATABASE in T-SQL becomes a PostgreSQL schema)
  • All tables and their data
  • Stored procedures (stored as PL/pgSQL functions in the sys schema)
  • Triggers and views
  • Babelfish system catalog entries in the sys and babelfish_* schemas

There is no separate Babelfish-specific backup format. A restore is a standard PostgreSQL restore.

Point-in-Time Recovery​

pgbackrest supports WAL archiving, which FoundryDB enables by default. WAL archiving means you can recover to any point in time between your oldest backup and the moment WAL shipping last completed, not just to a specific backup.

You do not interact with pgbackrest directly. Restores are initiated through the FoundryDB API. FoundryDB handles the pgbackrest restore and WAL replay internally, then brings the service back online.

After a restore, your Babelfish service comes up with the same T-SQL schemas and data as at the requested point in time. Your application reconnects on port 1433 and continues as before.

Encryption at Rest​

All backups are encrypted before upload to S3. The encryption is applied by pgbackrest using AES-256-CBC. Each Babelfish instance has a dedicated encryption key, so a compromised backup for one instance cannot be decrypted using another instance's key.

The key is identified in the backup metadata by encryption_key_id and is managed by FoundryDB. You do not need to supply or store keys yourself.

Backup Scheduling​

FoundryDB schedules automatic backups internally. The first full backup runs at provisioning time. Subsequent backups follow a schedule managed by the platform.

Manual backups can be triggered at any time via the API and are stored with the same encryption and retention settings as automatic backups, defaulting to 7-day retention unless you specify retention_days in the request body.

Get Started​

Babelfish on FoundryDB is available now. Provision a service via the FoundryDB API or dashboard. The first automatic backup runs within the first two minutes of the service reaching running state.