Database Backup Strategies: SQL Server, PostgreSQL, MySQL

Summary: Database backup is a different discipline from file-server backup; it works in layers — full, differential, and transaction-log level. For SQL Server the Full + Diff + Log Backup chain, for PostgreSQL pg_dump + WAL archiving, and for MySQL mysqldump + binary log replication are the standard, SME-ready approaches. A correctly built backup chain delivers sub-minute point-in-time recovery ("rewind to 14:23"); a poorly built chain teaches you in the middle of a real loss that the server you thought was backing up nightly actually failed 6 months ago.

In SMEs the answer to "do you have database backups?" is usually "yes, we back up at midnight." Push deeper: "is the backup successful?" — "I don't know, no error alert came in." "Have you ever tried to restore it?" — "no." In that triad, the SME that believes it has a backup is unwittingly exposed to the risk of backup loss. And database backups differ from file backups: they must be consistent, must not leave transactions half-finished, and must keep the log chain unbroken for point-in-time recovery.

In this article we cover backup strategies at SME scale for three common database engines (SQL Server, PostgreSQL, MySQL). The audience is IT managers, sysadmins, and decision-makers who want to turn the phrase "database backup" into concrete steps.

Why Is Database Backup Special?

A file-server backup is simple: copy the file, store it. A database backup is much more complex.

Database Backup Challenges

• Consistency: No half-completed transactions at the moment the backup is captured
• Size: A single DB can be tens or hundreds of GB
• Continuous writes: The DB is constantly changing — capturing a clean snapshot is technical
• Point-in-time recovery (PITR): "Rewind to 14:23" is the expectation
• Transaction log: Every operation is also logged separately
• Encrypted tables: If TDE is on, the backup must also be encrypted with the right key

For these reasons, the DB engine's native backup tools are used; "just copy the file" is not enough.

Backup Types — General Concepts

All DB engines offer three core backup types:

Full Backup

The entire database. Large, but self-contained. The restore can be done from this backup alone.

Differential Backup

All blocks changed since the last full backup. Smaller than a full, but grows over the week. Restore: full + the most recent diff.

Transaction Log Backup

Log files that record individual operations. Very small, taken very often (15 min, 1 hr). The foundation of point-in-time recovery.

A Typical Chain

Monday 02:00    — Full Backup
Tuesday 02:00   — Diff Backup
Wednesday 02:00 — Diff Backup
Thursday 02:00  — Diff Backup
Friday 02:00    — Diff Backup
Saturday 02:00  — Diff Backup
Sunday 02:00    — New Full Backup

Throughout the day:
Every 15 min    — Transaction Log Backup

This chain gives users weekly full + daily diff + 15-minute log protection. Recovery point: 15 minutes; recovery time: 30 minutes to a few hours.

SQL Server (Microsoft) Backups

The most widely deployed DB engine in Turkish SMEs. The native backup tools are mature.

Choosing the Recovery Model

SQL Server has three recovery models:

Model	Log Backup	PITR	SME Fit
Simple	No	No	Development/test
Bulk-Logged	Limited	Limited	ETL-heavy workloads
Full	Yes	Yes	Recommended for production

For production, the Full recovery model is standard. Log backups become mandatory.

Full Backup Command

BACKUP DATABASE [SirketDB]
TO DISK = 'D:\Backups\SirketDB_Full_20260503.bak'
WITH COMPRESSION, CHECKSUM, INIT;

Differential Backup

BACKUP DATABASE [SirketDB]
TO DISK = 'D:\Backups\SirketDB_Diff_20260503.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;

Transaction Log Backup

BACKUP LOG [SirketDB]
TO DISK = 'D:\Backups\SirketDB_Log_20260503_1430.trn'
WITH COMPRESSION, CHECKSUM;

Maintenance Plans or Ola Hallengren Scripts

Instead of manual commands, use SQL Server Maintenance Plans or Ola Hallengren's community scripts (the most widely used). They provide an automated chain, retention management, and failure alerts.

Restore — Point-in-Time

-- 1. Full restore (NORECOVERY)
RESTORE DATABASE [SirketDB]
FROM DISK = 'D:\Backups\SirketDB_Full.bak'
WITH NORECOVERY, REPLACE;

-- 2. Latest diff (NORECOVERY)
RESTORE DATABASE [SirketDB]
FROM DISK = 'D:\Backups\SirketDB_Diff.bak'
WITH NORECOVERY;

-- 3. Log backups in order, last log with STOPAT for the target time
RESTORE LOG [SirketDB]
FROM DISK = 'D:\Backups\SirketDB_Log_1430.trn'
WITH STOPAT = '2026-05-03T14:23:00', RECOVERY;

PostgreSQL Backups

Gaining ground in SMEs as the open-source DB engine of choice.

Backup Methods

Method	Description	SME Fit
pg_dump	Logical backup, SQL format	Small-to-medium DBs
pg_basebackup	Physical backup, filesystem copy	Large DBs
WAL archiving	Continuous archiving	Required for PITR
pg_dumpall	The entire cluster (users, roles included)	Annual snapshots

pg_dump

pg_dump -U postgres -d sirket_db -F c -f sirket_db_20260503.dump

-F c is the custom format — compressed, with parallel restore support.

Continuous Archiving + Base Backup

For production, pg_dump alone is not enough; the WAL archiving + base backup combination provides PITR.

postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

Base backups are periodic (weekly), WAL is continuous.

pgBackRest / Barman

In production, the tools of choice are pgBackRest or Barman:

• Automated full + incremental + WAL
• Retention policies
• Encryption and compression
• Restore-test commands
• At SME scale, they run even inside Docker

Restore — PITR

# Base backup restore
tar xf base_backup.tar -C /var/lib/postgresql/data

# recovery.conf or postgresql.auto.conf
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-05-03 14:23:00'

MySQL / MariaDB Backups

Common for e-commerce and web applications.

Backup Methods

Method	Description
mysqldump	Logical backup, SQL format
mysqlpump	A more modern alternative to mysqldump
XtraBackup (Percona)	Physical, consistent backup
Binary log (binlog)	Replication + PITR
MyDumper	Parallel logical backup

mysqldump

mysqldump --single-transaction --quick --routines --triggers \
  --events --hex-blob --master-data=2 \
  -u root -p sirket_db > sirket_db_20260503.sql

--single-transaction is critical for a consistent snapshot (InnoDB).

PITR with Binlog

my.cnf:

log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 14

Binlogs track transaction-level events. Full + binlog enables PITR.

Restore — PITR

# 1. Full restore
mysql -u root -p sirket_db < sirket_db.sql

# 2. Replay the binary log up to a specific time
mysqlbinlog --stop-datetime="2026-05-03 14:23:00" \
  /var/log/mysql/mysql-bin.000123 | mysql -u root -p sirket_db

Percona XtraBackup

For large DBs, mysqldump is not enough (it can take hours). XtraBackup provides a hot physical backup — the DB does not stop.

Backup Frequency and Retention

A typical SME-scale policy:

Backup Type	Frequency	Retention
Full backup	Weekly (Sunday 02:00)	4 weeks
Differential	Daily (02:00)	1 week
Transaction log	Every 15 minutes	24 hours
Monthly snapshot	1st of each month	12 months
Annual snapshot	1st of each year	7 years (statutory financial)

Backup Verification

The backup ran; but can it actually be opened? CHECKSUM and restore tests:

CHECKSUM and VERIFY

• SQL Server: WITH CHECKSUM and RESTORE VERIFYONLY
• PostgreSQL: pg_basebackup CHECKSUM, restore test
• MySQL: mysqldump output check, mysqlbinlog --verify

Restore Drill — Monthly

A real restore:

1. Restore the latest full into the test environment
2. Apply diff and log
3. Does the DB come up? Do a few queries work?
4. Measure the RTO
5. Write up the result

An untested backup is not a backup.

Where to Store Backups

The storage location for backups is critical:

Wrong: The Same Server

Storing backups on the DB server's own disk — a disk failure takes both down at once.

Wrong: A NAS in the Same Domain

If the domain admin password is captured, both the DB and the backups are reachable.

Right: 3-2-1 + Immutable

• Local backup on the DB server (fast restore)
• Backup NAS / backup server
• Immutable cloud tier (covered in a previous article)
• Monthly offline disk

TDE (Transparent Data Encryption) with Backups

For DBs with sensitive data and TDE enabled:

• The backup file is also encrypted
• The restore needs the correct key
• The key backup must be stored separately (HSM, secret manager)
• Lost key = lost backup

What Yamanlar Bilişim Offers

Our DB backup support areas at SME scale:

• Audit of existing DB backup architecture
• SQL Server / PostgreSQL / MySQL backup automation
• Ola Hallengren / pgBackRest / XtraBackup setup
• Point-in-time recovery configuration
• Monthly restore tests and reporting
• Immutable cloud integration
• TDE and key management
• Annual DR drill

Frequently Asked Questions

Conclusion

Database backup is one of the most critical links in an SME's cyber resilience. A properly built Full + Diff + Log chain gives you the flexibility to roll back to any point; monthly restore tests prove its correctness; an immutable cloud tier provides the last line of defense against ransomware. SQL Server, PostgreSQL, MySQL — each has its own tooling; applying standard practices at SME scale is not hard, it just requires regular operational discipline.

At Yamanlar Bilişim, we design backup strategies tailored to your DB size and engine and build the automation around them — moving your database from "hopefully it can be restored" to "tested every month, always ready."