УКР Choose a server

Backups and standby site

Database backups: PostgreSQL, MS SQL Server and BAS databases

Updated 11 min read Читати українською

Contents

For administrators of a server with an accounting system, a website or another service with a database. The result: daily scheduled backups of PostgreSQL or MS SQL Server databases, verified by a test restore, and a clear routine for BAS databases.

What you will need#

  • Administrator rights on the server and in the DBMS: a PostgreSQL superuser or the sysadmin role in SQL Server.
  • A backup directory, ideally on a different disk from the database, and a place off the server for the next copy.

In the examples the database is called accounting and the user admin. The commands were checked against the PostgreSQL 18 and SQL Server 2025 documentation (the current stable versions in early October 2026) and are the same in earlier supported versions.

PostgreSQL: pg_dump on a schedule#

pg_dump exports one database in a consistent state without interrupting users. The custom format (-Fc) is compressed and can be restored in several parallel jobs. Roles and tablespaces are not part of such a dump — pg_dumpall --globals-only saves them.

The script#

Save the script as /usr/local/sbin/pg-backup.sh:

#!/bin/bash
set -euo pipefail
umask 077

DIR=/var/backups/postgresql
KEEP_DAYS=14
STAMP=$(date +%Y%m%d-%H%M)

# roles and tablespaces
pg_dumpall --globals-only -f "$DIR/globals-$STAMP.sql"

# each database in its own file
DBS=$(psql -XAtc "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate")
for DB in $DBS; do
    pg_dump -Fc -f "$DIR/$DB-$STAMP.dump.part" "$DB"
    mv "$DIR/$DB-$STAMP.dump.part" "$DIR/$DB-$STAMP.dump"
done

# runs only if every dump succeeded
find "$DIR" -maxdepth 1 -type f -mtime +"$KEEP_DAYS" -delete

The script writes each dump to a .part file and renames it only on success. With set -e it stops at the first error, so files older than 14 days are deleted only once the new backups exist. Make the script executable and create a directory that only postgres can access (the roles file holds password hashes):

sudo chmod 755 /usr/local/sbin/pg-backup.sh
sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql

The schedule#

You need two systemd files: the service runs the script as postgres, the timer starts it nightly at 01:30 server time.

# /etc/systemd/system/pg-backup.service
[Service]
Type=oneshot
User=postgres
ExecStart=/usr/local/sbin/pg-backup.sh

# /etc/systemd/system/pg-backup.timer
[Timer]
OnCalendar=*-*-* 01:30:00
Persistent=true

[Install]
WantedBy=timers.target

Enable the timer, run the service once by hand and check the journal:

sudo systemctl daemon-reload
sudo systemctl enable --now pg-backup.timer
sudo systemctl start pg-backup.service
sudo journalctl -u pg-backup.service -n 20 --no-pager

Cron can replace the timer: add the line 30 1 * * * postgres /usr/local/sbin/pg-backup.sh to /etc/cron.d/pg-backup.

Restoring into a separate database#

Restore a dump into a separate database next to the live one to verify the backup and rehearse a recovery.

Warning. The last command drops a database without confirmation — check its name.

sudo -u postgres createdb -T template0 accounting_check
sudo -u postgres pg_restore --exit-on-error -j 4 -d accounting_check /var/backups/postgresql/accounting-20261002-0130.dump
sudo -u postgres psql -d accounting_check -c "ANALYZE" -c "SELECT count(*) FROM pg_stat_user_tables"
sudo -u postgres dropdb accounting_check

The table count should match the live database. On a new server, as postgres, restore the roles first (psql -f globals-20261002-0130.sql postgres), then the database: pg_restore -C -d postgres accounting-20261002-0130.dump. The PostgreSQL version there must be the same or newer.

WAL archiving and pg_basebackup#

With only a nightly dump, a failure at 17:00 costs a day of work. Continuous archiving of the write-ahead log (WAL) closes that gap: the server copies every completed log segment to an archive, and pg_basebackup takes a base backup of the data directory. Together they restore the whole cluster to any point in time. You need this when losing a day of work is unacceptable or pg_dump of a large database takes hours. It is harder to set up and restores only onto the same major PostgreSQL version, so keep pg_dump as a second, independent method. See the PostgreSQL documentation.

MS SQL Server: full, differential and log backups#

Recovery model#

The recovery model — SIMPLE, FULL or the rarely needed BULK_LOGGED — determines which backups you need. Check it with SELECT name, recovery_model_desc FROM sys.databases;.

ModelTransaction logWhat you can restore
SIMPLE (default in Express)Truncated automatically; log backups are impossibleThe state at the last full or differential backup
FULL (default in Standard and Enterprise)Grows until a log backup is takenAny point in time, if all log backups exist

If going back to the nightly backup is enough, switch the database to SIMPLE: ALTER DATABASE [accounting] SET RECOVERY SIMPLE;. If you cannot afford to lose a day of work, stay on FULL and back up the log every 15–30 minutes. After switching from SIMPLE back to FULL, take a full backup at once: log backups are impossible without it.

Commands#

DECLARE @f nvarchar(260) = N'D:\Backup\SQL\accounting_full_'
    + FORMAT(SYSDATETIME(), 'yyyyMMdd_HHmm') + N'.bak';
BACKUP DATABASE [accounting] TO DISK = @f
    WITH COMPRESSION, CHECKSUM, INIT, STATS = 10;
RESTORE VERIFYONLY FROM DISK = @f WITH CHECKSUM;

CHECKSUM verifies page checksums and adds a checksum for the whole backup, and RESTORE VERIFYONLY checks that the file is complete and readable. For a differential backup (changes since the last full one) add DIFFERENTIAL to WITH. For a log backup replace BACKUP DATABASE with BACKUP LOG and the extension with .trn. Restore order: the full backup, the latest differential, then in sequence the log backups taken after it; every step except the last uses NORECOVERY.

The SQL Server service creates the file, so the directory must exist and be writable for its account: NT Service\MSSQLSERVER by default, NT Service\MSSQL$SQLEXPRESS for the named instance SQLEXPRESS.

Scheduling: SQL Server Agent or Task Scheduler#

In the Standard and Enterprise editions SQL Server Agent runs the schedule. In SQL Server Management Studio: SQL Server Agent → Jobs → New Job, on the Steps page a step of type Transact-SQL script (T-SQL) with the commands above, on the Schedules page the time. Typically three jobs: a full backup weekly or nightly, a differential daily, log backups through the day. The Maintenance Cleanup Task in maintenance plans removes old files.

Express has neither the Agent nor backup compression, so Windows Task Scheduler runs the schedule and sqlcmd runs the commands. Save the commands above, minus COMPRESSION and the comma after it, to C:\Scripts\backup-full.sql and create sqlbackup.cmd next to it:

@echo off
set BKP=D:\Backup\SQL
sqlcmd -S .\SQLEXPRESS -E -C -b -i C:\Scripts\backup-full.sql -o %BKP%\last-run.log
if errorlevel 1 exit /b 1
forfiles /P %BKP% /M *.bak /D -14 /C "cmd /c del @path" >nul 2>&1
exit /b 0

With the -b switch sqlcmd returns an error code if the backup fails; without it Task Scheduler always reports success. The -C switch is needed for a local connection to a server with a self-signed certificate: the sqlcmd shipped with SQL Server 2025 encrypts connections by default. Add the task from an administrator command prompt and run it by hand:

schtasks /Create /TN "SQL backup" /TR C:\Scripts\sqlbackup.cmd /SC DAILY /ST 23:30 /RU admin /RL HIGHEST
schtasks /Run /TN "SQL backup"
schtasks /Query /TN "SQL backup" /V /FO LIST

The first command asks for the password of admin, who needs the sysadmin role in SQL Server. The last one shows the run result — it must be zero once the task has finished.

Restoring into a separate database#

Warning. For a test, restore only under a different database name and into different files: RESTORE with the live database’s name and the REPLACE option overwrites it.

RESTORE FILELISTONLY FROM DISK = N'D:\Backup\SQL\accounting_full_20261002_2330.bak';

RESTORE DATABASE [accounting_check]
    FROM DISK = N'D:\Backup\SQL\accounting_full_20261002_2330.bak'
    WITH MOVE N'accounting' TO N'D:\SQLData\accounting_check.mdf',
         MOVE N'accounting_log' TO N'D:\SQLData\accounting_check_log.ldf';
DBCC CHECKDB ([accounting_check]) WITH NO_INFOMSGS;
DROP DATABASE [accounting_check];

The first command lists the logical file names (the LogicalName column) — put them into MOVE. If DBCC CHECKDB prints no messages, the data structure is intact. The SQL Server version must be the same or newer.

BAS accounting databases#

The platform’s administrator guide describes backups separately for the file-based and client-server variants of a database.

  • File-based database. Copy the whole database directory while nobody is working in it: the Configurator and all user sessions are closed, web-server connections included. A copy taken from an open database may fail to open.
  • Client-server database. The data lives in PostgreSQL or MS SQL Server, so back it up with the DBMS tools described above; users can keep working.
  • Export to a .dt file (Configurator: Адміністрування → Вивантажити інформаційну базу) is best kept as an additional method, for example before a configuration update. Export while nobody is working in the database. A large database takes a long time, and the file can be verified only by loading it into a separate empty database.

Move the backups off the server#

Files on the same server protect against a user’s mistake, not against losing the server. Under the 3-2-1 rule a copy must reach separate storage and a third place.

  • Transfer the dumps and .bak files, not the data directory of a running DBMS: a database may not restore from files simply copied while it was running.
  • On Linux, restic is a convenient way to copy the backup directory to storage: it encrypts data before sending it. For Windows there are the built-in Windows Server tools.
  • Encrypt everything that leaves the server and keep a copy of the repository password off the server: without it the backup cannot be opened.

On the main United Cloud lines a server includes backup space on separate storage (the size is on the server card). You set up the copying yourself; support will send the connection details.

How to check the result#

  1. In the morning the directory holds files with last night’s date and a plausible size.
  2. The run finished without errors: journalctl -u pg-backup.service, the Agent job history or the result in Task Scheduler.
  3. Once a month restore a fresh backup into a separate database and note how long it took. Attach a BAS copy as a separate infobase and open a report for the last working day.

Common mistakes#

  • Backups sit next to the database. A disk failure, ransomware or a mistaken command destroys both.
  • The transaction log grows with no log backups. The database is in the FULL model but only full backups are taken: the .ldf file grows until it fills the disk, and the database stops accepting changes. Set up log backups or deliberately switch to SIMPLE; never delete the log file.
  • Restores were never tested. RESTORE VERIFYONLY only confirms that the file is readable. Only a restore into a separate database shows whether a backup works.

What next#