# 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 ## S3-Compatible Object Storage Store backups in an S3-compatible object storage service (for example, MinIO and Wasabi), specify the `endpoint_url` configuration option in the following command: ```sql 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" }'; ``` When you back up to S3-compatible object storage, the credentials specified in the `BACKUP DATABASE` statement control write access. Ensure that the IAM user or service account has the required permissions on the target bucket or container. ## Local Filesystem Store backups on a local or shared filesystem using the following command: ```sql BACKUP DATABASE mydb TO "/mnt/nfs/backups/mydb"; ``` Ensure that all cluster nodes (aggregators and leaves) have write access to the backup path. Mount a shared filesystem, such as NFS, to the same path on every node. Ensure that the `memsql` OS user has write permissions on the mount. ```shell # 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 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 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. > **📝 Note**: Incremental backups apply only to columnstore tables. Rowstore data receives a full copy on each backup regardless of the backup type. ## Schedule Backups with cron Use a `cron` job to schedule regular backups. ## Toolbox The following example schedules a weekly full backup, an initial incremental backup, and differential backups using the `sdb-admin` `create-backup` command: ```shell # 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 ``` Refer to `sdb-admin create-backup` for more information. ## Use the `singlestore` Client Schedule backups by running the `BACKUP DATABASE` statement from the `singlestore` client: ```shell # 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 ``` Refer to [SingleStore Client](https://docs.singlestore.com/db/v9.0/user-and-cluster-administration/cluster-management-with-tools/singlestore-client.md) for more information. ## 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: ```yaml 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: ```yaml apiVersion: v1 kind: Secret metadata: name: backup-creds type: Opaque data: user: 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 from S3-compatible Object Storage If a database was backed up to S3-compatible object storage, restore it from the same bucket and path and provide the required configuration and credentials: ```sql 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" }'; ``` For S3-compatible providers, use the same connection details that were used for the backup, including any required endpoint configuration. ## Restore from a Local or Shared Filesystem If a database was backed up to a local or shared filesystem, restore it from the same path: ```sql RESTORE DATABASE mydb FROM "/mnt/nfs/backups/mydb"; ``` Make sure the restore path points to the backup you want to recover and is accessible from the cluster nodes. ## Restore a Differential Backup Set If the backup path contains an initial backup and a later differential backup, restore from that same path. SingleStore automatically applies the initial backup followed by the differential backup during restore. ## Verify the Restored Database After the restore finishes, verify that the restored database is usable: * Confirm that the database is back online and that you can connect to it. * Confirm that the expected tables are present. * Test restore procedures regularly as part of your backup process. ## Monitoring Backups Query the `information_schema.MV_BACKUP_HISTORY` view to verify backup status: ```sql SELECT database_name, backup_id, status, start_timestamp, end_timestamp FROM information_schema.MV_BACKUP_HISTORY ORDER BY start_timestamp DESC LIMIT 10; ``` ```output +------------------+-----------+---------+---------------------+---------------------+ | 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. *** Modified at: July 17, 2026 Source: [/db/v9.0/reference/singlestore-operator-reference/backups/](https://docs.singlestore.com/db/v9.0/reference/singlestore-operator-reference/backups/) (An index of the documentation is available at /llms.txt)