BackupZit

SQL Server, PostgreSQL and MySQL backup with point-in-time restore

How to protect Microsoft SQL Server, PostgreSQL and MySQL/MariaDB databases with BackupZit: full backups with the database server's own tools, log backups every few minutes, retention, an off-site copy — and restoring a single database to the minute before a wrong DELETE, as a new database next to the original or in place.

What you will set up

A file backup of a running database server is rarely consistent, and it can only bring back the state of the last night. BackupZit backs up databases with the server's own tools instead, from the BackupZit agent on the database machine: BACKUP DATABASE on SQL Server, pg_basebackup and pg_dump on PostgreSQL, mysqldump on MySQL and MariaDB. Full backups run on the job's schedule; optional log backups every few minutes store the transaction logs, the archived WAL or the binary logs, so a single database can be restored to any point in time between them. BackupZit is free and open source (AGPLv3); everything below is part of the free product.

Requirements

  • A BackupZit console and a storage target (an SMB or SFTP share, S3, a hardened repository …). The 3-2-1 guide shows how to choose and protect it.
  • The BackupZit agent on the database machine itself: the agent backs up the database servers on its own machine, not remote ones.
  • Supported database servers:
    • Microsoft SQL Server on Windows, any edition from 2012 on, including Express;
    • PostgreSQL on Linux;
    • MySQL or MariaDB on Linux.
  • For point-in-time restore: log backups switched on in the job, plus the preparation for each engine below (recovery model, WAL archiving or binary log).

Step 1 — Prepare SQL Server

  1. Install the agent on the Windows machine that runs SQL Server.
  2. The agent runs as NT AUTHORITY\SYSTEM and connects with Windows authentication. Give that account the sysadmin role, once per instance:
    sqlcmd -S .\SQLEXPRESS -E -Q "ALTER SERVER ROLE sysadmin ADD MEMBER [NT AUTHORITY\SYSTEM]"
  • Each run writes one backup file per database (BACKUP DATABASE / BACKUP LOG … WITH CHECKSUM) into the instance's default backup folder, stores the files and deletes them. That folder needs free space for the largest database while the run lasts.
  • With log backups on, the full backups start the log chain. Without them, full backups are taken COPY_ONLY, so they do not disturb other backup or maintenance plans.
  • Log backups are taken only for databases in the full or bulk-logged recovery model; databases in the simple model get full backups only.

Step 2 — Prepare PostgreSQL

The agent works as the postgres account over the local socket — there is nothing to configure for backups. A full backup stores the whole cluster (pg_basebackup), the roles (pg_dumpall --globals-only) and a pg_dump of each database.

Point-in-time recovery needs WAL archiving. With log backups on, the first full backup sets it up with ALTER SYSTEM: archive_mode = on and an archive_command that copies finished WAL segments into /var/lib/backupzit-pg/wal; the log backups store them and remove them there. archive_mode takes effect after one restart of PostgreSQL — the run tells you:

systemctl restart postgresql

Already archiving WAL with another tool? If archive_command is already in use, BackupZit leaves it alone and backs up without point-in-time recovery. If you later stop the log backups (job disabled or deleted), switch archiving off again with ALTER SYSTEM RESET archive_mode and a restart, so the WAL folder does not fill up.

Step 3 — Prepare MySQL or MariaDB

The agent connects as root over the local socket (socket authentication, the default on Debian and Ubuntu), or with the credentials in /root/.my.cnf. A full backup stores a mysqldump of each database — consistent, with routines, events and triggers — together with the binary log position it matches.

Point-in-time recovery needs the binary log. Enable it once in the server configuration, create the folder for the log (owned by the mysql account) and restart the server — for example in /etc/mysql/mariadb.conf.d/60-binlog.cnf:

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7

The log backups then store each closed binary log. Keep the server's own expiry longer than the log backup interval: a gap is reported, and restores cannot cross it.

Step 4 — Create the database job

In the console open Backup jobs → New backup job, enter a name, choose the Agent of the database machine and the Storage target, and select the kind Databases. Then fill in:

FieldWhat to enter
Database serverMicrosoft SQL Server (Windows), PostgreSQL (Linux) or MySQL / MariaDB (Linux). It cannot be changed later.
Instance or portSQL Server: the instance name, e.g. SQLEXPRESS, empty for the default instance. PostgreSQL: the port, empty for 5432. MySQL/MariaDB: not used (local socket).
DatabasesOne name per line, or empty for all user databases — databases created later are then included automatically.
Back up the logs every (minutes)e.g. 15 for transaction logs (SQL Server), archived WAL (PostgreSQL) or binary logs (MySQL). Leave it empty if restoring to the time of a full backup is enough.
Also back up the system databases master, model and msdbSQL Server, when all databases are backed up.
ScheduleThe schedule of the full backups, e.g. At specific times, daily at 22:00. The log backups run in between at the interval above.

Leave Encrypt backups (AES-256) on. Database backups are deduplicated — unchanged data is not uploaded again — and encrypted like every backup. After the first run, the run page lists what each backup holds, per database.

Need the database backed up before the file server of the same application? Give the second job the schedule After another job, so it starts as soon as the database job has finished.

Step 5 — Retention and RPO

Under Keep backups set how many restore points to keep — the most recent N plus daily, weekly and monthly ones. For database jobs retention counts the full backups; log backups are kept as long as the full backup they build on. So with 14 daily full backups and log backups every 15 minutes you can go back to any minute of the last two weeks, and to the monthly full backups beyond that.

Set Recovery point objective (RPO), hours to how old the newest good backup may get, e.g. 26 for a daily job. A job that misses it is shown on the dashboard and reported.

Step 6 — Off-site copy

One copy of the backups belongs in another place. Create a second job of the kind Backup copy, under Copy the backups of select the database job, and as Storage target an off-site or immutable target (S3 with Object Lock, Azure Blob Storage or a hardened repository).

  • The agent of the database machine copies the existing backups; the database is not read again.
  • Only data not yet at the destination is sent, re-encrypted with the destination's key.
  • The schedule After each backup of the source job (the default) keeps the copy current; the copy has its own retention.

Immutability, encryption and the recovery key are explained step by step in the 3-2-1 backup guide with immutable copies.

Step 7 — Restore a database

Open a full backup run in Activity (section Restore a database), or use the Restore wizard and choose Database. Then choose:

  • Database: the database from the backup. For PostgreSQL there is also Whole PostgreSQL cluster — as a separate instance on a free port.
  • Restore on: the same server or another one with the same database server, plus the SQL Server instance or PostgreSQL port to restore into.
  • As a new database named e.g. sales_restored — the original stays untouched and you can copy rows back — or Under its original name, e.g. on another server or after the database was lost. Tick replace it to overwrite an existing database; its users are disconnected first.

Under State to restore pick one of three:

OptionResult
As of the full backupThe database as it was when the full backup was taken.
Latest: the full backup and every log backup after itThe newest state the backups hold.
At a point in timeAny time between the full backup and the newest usable log backup — the table below shows Log backups reach until for each database.

Enter the time as the console shows it, in the console server's time zone; the agent converts it to the database server's time zone. Then click Restore database.

What happens per engine

  • SQL Server: the full backup is restored, and for latest or a point in time also the log backups after it, up to that time.
  • PostgreSQL, as of the full backup: from its pg_dump into the running server. At a point in time or latest: the agent unpacks the cluster backup into a temporary instance on a free port (socket only, no network, no archiving), replays the WAL up to that time, dumps the database from it into the running server and removes the temporary instance. The machine needs free space for the cluster for a while.
  • PostgreSQL, whole cluster: restored the same way as a separate instance that keeps running on a free port; the run tells you its port, its data directory (below /var/lib/backupzit-pg) and how to connect to it and stop it.
  • MySQL/MariaDB: the dump is loaded into a new database (or replaces an existing one). At a point in time or latest, the changes of that database recorded in the binary logs after the dump are replayed up to that time — also when the database gets a new name. The replay is not written to the server's binary log.

Recovering from a wrong DELETE: restore the database as a new database at a point one minute before the mistake, check it, and copy the lost rows back into the original. Production keeps running the whole time.

Step 8 — Restore tests

A database backup is only proven when it has been restored. Under Settings → Restore tests, Test every job's newest backup automatically (weekly by default) restores a sample from each job's newest backup and verifies it against the stored checksums, and Check the stored data (monthly by default) reads and verifies every piece of data in each repository — which finds a failing NAS disk or bit rot early. Test restore and Check data on a job's page start them at once.

These tests check the stored data, not whether the database server accepts it. For a real test, restore a database regularly as a new database, e.g. sales_restored, At a point in time, run a few queries against it and delete it again. On another machine with the same database server it also proves that you can move to new hardware.

Step 9 — Alerts

Set up Settings → Email notifications and keep Failures on: you get one email per failed run with the reason — full backups, log backups, copies, restores and restore tests. Settings → Chat & webhooks sends the same to Slack, Microsoft Teams or any HTTP endpoint. On the dashboard, Needs attention lists failed jobs, missed schedules and RPOs, offline agents and storage that is running full.

Watch also for the messages specific to databases: the PostgreSQL run that asks for the restart after enabling WAL archiving, and a reported gap in the MySQL binary logs, which a point-in-time restore cannot cross.

Command line

The same backups and restores are available on the agent's command line, e.g. for scripts:

backupzit-agent sql list    [--instance SQLEXPRESS]
backupzit-agent sql restore --repo ... [--instance I] --db NAME [--as NEW] [--replace] [--at "2026-10-04 14:59:00"] SNAPSHOT|latest
backupzit-agent pg list     [--port 5432]
backupzit-agent pg restore  --repo ... --db NAME|"(cluster)" [--as NEW] [--replace] [--at TIME | --latest] SNAPSHOT|latest
backupzit-agent mysql list
backupzit-agent mysql restore --repo ... --db NAME [--as NEW] [--replace] [--at TIME | --latest] SNAPSHOT|latest

Each engine also has a backup command; the full syntax is in the chapter Databases (SQL Server, PostgreSQL, MySQL) of the user guide.

Limits to know

  • The agent backs up the database servers on its own machine only; remote database servers are not supported.
  • SQL Server: no differential backups and no Always On availability groups. Databases in the simple recovery model get full backups only, so no point-in-time restore for them.
  • PostgreSQL point-in-time recovery needs WAL archiving and one restart; if another tool already uses archive_command, only full-backup restores are possible.
  • MySQL/MariaDB point-in-time recovery needs the binary log; users and privileges (the mysql system database) are not part of the dumps, and restores cannot cross a gap in the binary logs.
  • Without log backups, a database can only be restored to the time of a full backup.
  • Single tables are not restored on their own: restore the database under a new name and copy the rows.
  • SQL Server needs free space for the largest database in its default backup folder during each run; a PostgreSQL point-in-time restore needs free space for the cluster for a while.

Next steps

Download BackupZit, see the database features and all storage targets, or read the chapter Databases (SQL Server, PostgreSQL, MySQL) in the user guide in the console. To protect the copies against ransomware, follow the 3-2-1 guide with immutable copies; to bring back the whole Windows server the database runs on, see Windows bare-metal restore. Questions are welcome in GitHub Discussions; companies can get commercial support from MaxZit.