Scheduled ERP backups in two layers: database dumps and storage snapshots

TLDR

Every Acumatica instance you host yourself is yours to back up; vendor guidance converges on scheduled SQL-native backups, but they end up ad hoc. Ansible provisions two independent nightly layers — a SQL-native .bak per database and a whole-VM ZFS snapshot — with no manual routine. One restores a single database; the other rolls the whole instance back after a bad upgrade; neither replaces the other.

Who backs up what

On Acumatica’s SaaS cloud, backup is Acumatica’s job; every instance you host yourself is yours, including the self-hosted DEV/TEST instances from the previous post.

On Acumatica’s SaaS cloud, backup is Acumatica’s job. The SaaS FAQ describes automatic backups on a rolling retention schedule, plus transaction-log backups through the day. It also describes the limits of that arrangement. Those backups exist for Acumatica’s own disaster recovery, and the FAQ notes they “may not be suitable for individual customer restore situations”. A restore is a support request with a services fee, and anytime access to your own backup copies is a paid add-on.

Every instance you host yourself is your job. Acumatica’s System Administration Guide is written for exactly this administrator — the one who installs and maintains the instance on their own servers. That includes private-cloud production and the self-hosted DEV/TEST instances from the previous post.

What the guidance says

The vendor documentation rules out tenant snapshots as a backup and converges on scheduled, SQL-native backups — the gap is getting the work to run every night without depending on memory.

The first instinct is often the tenant snapshot feature, because it is built into the Acumatica UI. Acumatica’s snapshot documentation says no: snapshots move configuration and data between tenants, and for backup it points you at database management applications instead. A snapshot is only safe to take with the system locked in maintenance mode, which rules it out as a nightly routine. Practitioners in the Acumatica user group forums reach the same conclusion: a snapshot is a manual copy tool, not something to schedule as a backup.

For self-hosted instances, the recommended practice is ordinary SQL Server administration. Community guidance is the full recovery model on production databases, with scheduled full backups and transaction-log backups for point-in-time restore. Acumatica’s upgrade guide starts its pre-upgrade steps with backing up all databases and configuration files.

The written guidance converges on scheduled, SQL-native backups. The gap is not knowing what to do — it is getting the work to run every night without depending on memory.

Why backups stay ad hoc

Backups end up ad hoc when they depend on a person, the restore path goes unexercised, and one backup does not cover both a dropped table and a botched upgrade.

Backups are ad hoc when they depend on a person. Someone runs a manual database backup before a risky change, most of the time. The pre-upgrade backup is a checklist item, so it happens when someone remembers the checklist. The restore path has never been exercised, so nobody knows how long a recovery takes until they need one.

Two different accidents need two different recoveries, and a single backup covers neither well. A dropped table needs one database restored, cleanly. A botched upgrade needs the whole instance rolled back — Windows, SQL Server, and the databases together. A database backup alone cannot do the second, and a whole-machine rollback is a clumsy way to do the first.

Two independent layers provisioned by Ansible

Two independent layers provisioned by Ansible — a SQL-native .bak per database and a whole-VM storage snapshot — both running nightly with no manual routine.

Protect the instance with two independent layers, both provisioned by Ansible, both running nightly with no manual routine. The instances they protect are built by the same repo, kborovik/acumatica-devops; see docs/windows-vms.md and the previous post.

Layer	Scope	Consistency	Restores
SQL-native `.bak`	One user database	Application-consistent	A single database, to any SQL Server
Storage (ZFS) snapshot	Whole VM: OS + data + backup disks	Crash-consistent	The entire instance, in place

The .bak layer is the scheduled SQL-native backup the vendor documentation recommends, automated so it does not depend on anyone’s routine. The snapshot layer covers the case a database backup cannot: rolling the whole instance back.

Use the .bak layer for data integrity and portability. Use the snapshot layer for a fast whole-VM undo of an OS or application change.

The stack is a lab choice, not a requirement of the pattern. The snapshot layer needs storage with atomic snapshots and rollback, not ZFS specifically. A VMware or Hyper-V cluster backed by a NetApp array, or a similar storage system, employs the same architecture: scheduled storage snapshots of the VM’s disks underneath the SQL-native backups. The .bak layer is plain SQL Server and runs the same anywhere.

The nightly timeline

A SQL Agent job backs up each database at 02:00, then sanoid snapshots the backup share and every VM’s disks, so each snapshot captures a complete set of .bak files.

02:00  SQL Agent job backs up each user database to a local disk, then copies it to a share
03:15  snapshot of the backup share (14 kept)
03:30  one atomic recursive snapshot of every VM's disks (14 kept)

The SQL Agent job is created by the same Ansible role that installs SQL Server. The snapshots are created and pruned by sanoid, a snapshot scheduler for ZFS, provisioned on the host by Ansible. The 02:00 job finishes before the 03:15 share snapshot, so that snapshot always captures a complete set of .bak files.

Restore one database or roll back the whole VM

One database restores with RESTORE DATABASE from the share; a whole instance rolls back with zfs rollback after the VM is shut off.

To restore one database from the share:

RESTORE DATABASE AcumaticaDB
  FROM DISK = '\\<host>\mssql-backups\<name>\AcumaticaDB.bak'
  WITH REPLACE;

To roll back a whole instance after a bad upgrade — snapshot first, then roll back with the VM shut off:

sudo zfs snapshot -r upool/vms/acu-dev1@before-upgrade
sudo virsh shutdown acu-dev1
sudo zfs rollback upool/vms/acu-dev1@before-upgrade

Crash-consistency and single-host limits

A storage snapshot of a live database is only crash-consistent, and both layers live on one host — the .bak layer is the backup of record, and off-site replication is staged but not deployed.

A storage snapshot of a live database disk is only crash-consistent — the same state as a power loss. SQL Server crash-recovers from it, so it is a reliable rollback net, but it is not the database backup of record. The .bak layer is. That is why both layers exist, and why neither replaces the other.

Both layers also live on one ZFS pool on one host. They cover guest-level mistakes and a failed disk, not the loss of the pool or the host. The standard fix is ZFS replication to a second machine, and the strategy doc lists syncoid for that, staged but not deployed. A lab where every instance rebuilds from one command can accept that trade. A production instance cannot.

Remove the person-dependent routine

The problem is not missing knowledge but a routine that depends on a person; provisioning both layers with the same Ansible that builds the instances removes the routine.

The problem at the top is not missing knowledge. Acumatica documents what to do, and practitioners agree on it. The problem is that the guidance still depends on a person following a routine.

Provisioning both layers with the same Ansible that builds the instances removes the routine. Every instance gets the same schedule and the same retention on the day it is built, and the restore commands are short enough to rehearse. A rehearsed restore turns a backup into a reset button: snapshot, break the instance, roll back, repeat. The next post depends on that reset button — a pipeline that builds an Acumatica tenant from YAML needs an instance it can afford to break.

The full design, including the schedule, retention, and configuration knobs, is in docs/backup-strategy.md.