Backups

Overview

Use the BACKUP DATABASE command to create database backups to cloud storage or the local filesystem. To automate backups on a schedule, use cron (VM or bare-metal deployments) or a Kubernetes CronJob (Operator deployments).

Prerequisites

• The BACKUP privilege on the database(s) to back up.

• The OUTBOUND privilege at the cluster level (required for cloud destinations).

• Network access from all cluster nodes to the backup destination.

• Valid credentials for your storage provider for cloud storage.

Backup Destinations

BACKUP DATABASE mydb TO S3 "my-bucket/backups/mydb"
  CONFIG '{
    "endpoint_url": "http://minio.example.com:9000",
    "region": "us-east-1"
  }'
  CREDENTIALS '{
    "aws_access_key_id": "minio_access_key",
    "aws_secret_access_key": "minio_secret_key"
  }';

BACKUP DATABASE mydb TO "/mnt/nfs/backups/mydb";

# Example: Verify the mount and permissions on each node.

mount | grep /backups
ls -ld /backups/  # Should show write permission for the memsql user.

Incremental Backups

Incremental backups reduce storage usage and backup duration by only capturing changes since the last full backup.

1. Create the initial baseline backup using WITH INIT:

   SQL

   Copy

   BACKUP DATABASE mydb WITH INIT TO S3 "my-bucket/backups/mydb_weekly"
     CONFIG '{"region": "us-east-1"}'
     CREDENTIALS '{
       "aws_access_key_id": "your_access_key_id",
       "aws_secret_access_key": "your_secret_access_key"
     }';

2. Create subsequent differential backups using WITH DIFFERENTIAL:

   SQL

   Copy

   BACKUP DATABASE mydb WITH DIFFERENTIAL TO S3 "my-bucket/backups/mydb_weekly"
     CONFIG '{"region": "us-east-1"}'
     CREDENTIALS '{
       "aws_access_key_id": "your_access_key_id",
       "aws_secret_access_key": "your_secret_access_key"
     }';

The differential backup must target the same path as the initial backup. When restoring, SingleStore applies the initial backup followed by the differential backup automatically.

Schedule Backups with cron

Use a cron job to schedule regular backups.

# Full backup every Sunday at 2:00 AM
0 2 * * 0 sdb-admin create-backup mydb -r "s3://my-bucket/backups/mydb?region=us-east-1" --backup-type full >> /var/log/singlestore-backup.log 2>&1

# Initial incremental backup every Monday at 2:00 AM
0 2 * * 1 sdb-admin create-backup mydb -r "s3://my-bucket/backups/mydb_incremental?region=us-east-1" --backup-type init --backup-suffix weekly >> /var/log/singlestore-backup.log 2>&1

# Differential backups Tuesday through Saturday at 2:00 AM
0 2 * * 2-6 sdb-admin create-backup mydb -r "s3://my-bucket/backups/mydb_incremental?region=us-east-1" --backup-type differential --backup-suffix weekly >> /var/log/singlestore-backup.log 2>&1

# Daily full backup at 2:00 AM
0 2 * * * singlestore -u admin -p'your_password' -e "BACKUP DATABASE mydb TO S3 'my-bucket/backups/mydb/$(date +\%Y-\%m-\%d)' CONFIG '{\"region\":\"us-east-1\"}' CREDENTIALS '{\"aws_access_key_id\":\"...\",\"aws_secret_access_key\":\"...\"}'" >> /var/log/singlestore-backup.log 2>&1

Schedule Backups with a Kubernetes CronJob

For Kubernetes deployments, use a CronJob to schedule backups with the singlestore/tools image. This approach does not require installing a SingleStore Toolbox on the Kubernetes nodes.

The following example schedules a full backup every day at 2:00 AM to an S3-compatible object store:

apiVersion: batch/v1
kind: CronJob
metadata:
  name: singlestore-backup
spec:
  schedule: "0 2 * * *"
  jobTemplate:
    spec:
      template:
        spec:
          serviceAccountName: tools
          containers:
            - name: backup
              image: singlestore/tools:alma-v1.11.6-1.17.2
              imagePullPolicy: IfNotPresent
              command:
                - sdb-admin
                - create-backup
                - mydb
                - -r
                - "s3://my-bucket/backups/mydb?region=us-east-1"
                - --backup-type
                - full
                - -c
                - /etc/memsql/toolbox-config.hcl
                - --yes
              volumeMounts:
                - name: toolbox-config
                  mountPath: /etc/memsql
                  readOnly: true
          volumes:
            - name: toolbox-config
              secret:
                secretName: singlestore-toolbox-config
          restartPolicy: Never
      backoffLimit: 2

Store the database credentials in a Kubernetes Secret:

apiVersion: v1
kind: Secret
metadata:
  name: backup-creds
type: Opaque
data:
  user: <base64-encoded-username>
  password: <base64-encoded-password>

To schedule differential backups, create a second CronJob that runs on a different schedule and specifies --backup-type differential.

Refer to sdb-admin create-backup for the complete list of backup options.

Restore a Backup

Use the RESTORE DATABASE command to restore a database from S3-compatible object storage. During restore, the target database cannot be queried until the restore has completed. When the restore completes, the database returns to the online state.

Before you restore a backup:

• Make sure the cluster can reach the backup location and has any required storage credentials.

• Use the same backup path that was used when the backup was created. A common restore error is specifying the wrong path.

• SingleStore does not support restoring a backup created on a newer version into an older version.

RESTORE DATABASE mydb
FROM S3 "my-bucket/backups/mydb"
CONFIG '{"region": "us-east-1"}'
CREDENTIALS '{
  "aws_access_key_id": "your_access_key_id",
  "aws_secret_access_key": "your_secret_access_key"
}';

RESTORE DATABASE mydb FROM "/mnt/nfs/backups/mydb";

Monitoring Backups

Query the information_schema.MV_BACKUP_HISTORY view to verify backup status:

SELECT database_name, backup_id, status, start_timestamp, end_timestamp
FROM information_schema.MV_BACKUP_HISTORY
ORDER BY start_timestamp DESC
LIMIT 10;

+------------------+-----------+---------+---------------------+---------------------+
| database_name    | backup_id | status  | start_timestamp     | end_timestamp       |
+------------------+-----------+---------+---------------------+---------------------+
| test_db          |     11003 | Success | 2026-04-18 18:00:08 | 2026-04-18 18:00:10 |
| memsql_example   |     11002 | Success | 2026-04-18 18:00:06 | 2026-04-18 18:00:08 |
| s2examples       |     11001 | Success | 2026-04-18 18:00:04 | 2026-04-18 18:00:06 |
| agentdb          |     11000 | Success | 2026-04-18 18:00:01 | 2026-04-18 18:00:04 |
| test_db          |     10003 | Success | 2026-04-11 18:00:08 | 2026-04-11 18:00:09 |
| memsql_example   |     10002 | Success | 2026-04-11 18:00:06 | 2026-04-11 18:00:07 |
| s2examples       |     10001 | Success | 2026-04-11 18:00:03 | 2026-04-11 18:00:05 |
| agentdb          |     10000 | Success | 2026-04-11 18:00:01 | 2026-04-11 18:00:03 |
| test_db          |      9003 | Success | 2026-04-04 18:00:07 | 2026-04-04 18:00:09 |
| memsql_example   |      9002 | Success | 2026-04-04 18:00:05 | 2026-04-04 18:00:07 |
+------------------+-----------+---------+---------------------+---------------------+

Best Practices

• Rotate backup paths: Include a date or timestamp in the backup path to avoid overwriting previous backups.

• Test restores regularly: Verify that backups can be restored successfully with RESTORE DATABASE.

• Use incremental backups for large databases: Reduces backup time and storage for columnstore-heavy workloads.

• Secure credentials: Store backup credentials in environment variables or a secrets manager rather than hardcoding them in scripts.

• Monitor for failures: Check MV_BACKUP_HISTORY or parse the cron log output for backup failures.