Technical Articles › Backup
Backup

Database Backup Strategy: SQL Dumps and Point-in-Time Recovery (PITR)

Published on 11.07.2026

A reliable backup strategy is essential for maintaining the availability and integrity of database systems. Different backup methods serve different purposes and operate at different levels within the database infrastructure. This article explains the differences between infrastructure backups and client backups, outlining when each approach should be used.

The focus is on Point-in-Time Recovery (PITR) using Binary Logs as a server-level backup mechanism and SQL dumps as logical backups created at the database or table level. While these technologies address different recovery objectives, they complement each other as part of a comprehensive backup strategy.

The article also introduces the lnbrt Backup Tool by Linetz, which simplifies the centralized management and restoration of SQL dump backups for MariaDB, MySQL, and PostgreSQL, making it a valuable complement to server-level infrastructure backup solutions.

Database backups are one of the fundamental building blocks of a reliable IT infrastructure. Whether databases are running on physical servers, virtual machines, or cloud platforms, hardware failures, software defects, configuration errors, and human mistakes can lead to unexpected data loss or service interruptions. A well-designed backup strategy is therefore essential to ensure business continuity and minimize downtime.

Relational database management systems such as MySQL, MariaDB, and PostgreSQL provide different backup mechanisms, each designed for specific recovery scenarios. These methods differ not only in their implementation but also in the level at which they operate and the recovery capabilities they provide.

In general, database backup strategies can be divided into two categories:

  • Infrastructure Backups, which protect the complete database server and its transaction history.
  • Client Backups, which create logical exports of individual databases or tables.

Both approaches serve different purposes and should be considered complementary components of a comprehensive backup strategy.


Infrastructure Backups – Protecting the Entire Database Server

Infrastructure backups are designed to protect the complete state of a database server rather than individual databases. Besides the database contents themselves, these backups often include server configuration, user accounts, permissions, and other server-related metadata.

One of the most important technologies in this area is Point-in-Time Recovery (PITR). PITR combines regular full backups with continuously recorded transaction logs, allowing administrators to restore the database server to a specific point in time.

For MySQL and MariaDB, this functionality is based on Binary Logs (Binlogs).

Binary Logs record every data-changing operation performed on the database server. Every INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP statement is written sequentially to the Binlogs, creating a complete history of all modifications that occur after a full backup.

Unlike SQL dumps, Binary Logs operate at the server level. They are not limited to individual databases or tables but capture changes across every database hosted by the database server. This server-wide perspective is what classifies Binlog-based backups as infrastructure backups.

Restoring a database using Point-in-Time Recovery typically involves two steps. First, the latest full backup is restored. Afterwards, the Binary Logs are replayed until the desired recovery point has been reached. This allows administrators to recover the system to virtually any moment before a failure or accidental modification occurred.

For example, if a table is accidentally deleted at 2:32 PM, the database server can be restored to 2:31:59 PM, effectively eliminating data loss caused by the mistake.

Because of its ability to minimize data loss and provide highly accurate recovery, Point-in-Time Recovery has become the standard backup strategy for production environments where availability and data integrity are critical.


Client Backups – Logical Database and Table Backups

Unlike infrastructure backups, client backups operate on a logical level. They are typically created using database client utilities such as mysqldump, mariadb-dump, or pg_dump.

Instead of copying physical database files, these tools connect to the database server and generate SQL statements that recreate both the database structure and its contents. The resulting SQL file contains commands such as CREATE DATABASE, CREATE TABLE, INSERT, CREATE INDEX, VIEW, TRIGGER, and stored procedures, allowing the database to be recreated on another system.

One of the main advantages of client backups is their flexibility. SQL dumps can be created at different levels depending on the intended use case. Administrators may export a single table, an entire database, or multiple selected databases in one operation.

Since the backup is stored as logical SQL statements rather than physical storage structures, SQL dumps are platform-independent and can easily be migrated between servers running different operating systems or storage configurations.

Client backups are particularly useful when individual databases need to be archived, transferred to another server, restored independently, or used in development and testing environments. They also provide a convenient method for creating backups before application upgrades or schema modifications.

However, SQL dumps have an important limitation. They always represent the exact state of the selected database objects at the time the export is created. Any changes made after the dump has been generated are not included. As a result, SQL dumps do not support Point-in-Time Recovery, and data modifications between two backup runs cannot be recovered.

For databases with a high transaction rate, relying solely on SQL dumps may therefore result in significant data loss if a failure occurs between backup intervals.


Infrastructure Backups versus Client Backups

Although both methods are intended to protect database data, they operate at different levels and address different recovery objectives.

Infrastructure backups work at the server level. Binary Logs continuously capture every transaction executed on the database server, regardless of which database is affected. Their primary objective is to restore the complete database server to a specific point in time with minimal data loss.

Client backups, on the other hand, operate at the database or table level. Administrators explicitly choose which databases or tables should be exported. This selective approach makes SQL dumps highly suitable for migrations, development environments, customer-specific backups, and the restoration of individual databases.

Infrastructure backups prioritize business continuity, disaster recovery, and minimal Recovery Point Objectives (RPO). Client backups emphasize flexibility, portability, and ease of administration.

Rather than replacing one another, both approaches complement each other within a professional backup strategy.


Choosing the Appropriate Backup Strategy

The appropriate backup method depends largely on the operational requirements of the database environment.

For production systems with continuously changing data, Point-in-Time Recovery should form the foundation of the backup strategy. Enterprise applications such as ERP systems, e-commerce platforms, CRM solutions, and financial databases typically process transactions around the clock. In these environments, losing several hours of data is often unacceptable. By combining regular full backups with Binary Logs, administrators can restore the system to almost any desired point in time while minimizing data loss.

SQL dumps are better suited for scenarios where flexibility is more important than continuous transaction protection. They are an excellent choice for migrating databases between servers, creating development or testing environments, archiving customer databases, or exporting individual tables. Their platform-independent format also makes them particularly useful for long-term storage and selective restoration.

In modern enterprise environments, these technologies are rarely viewed as competing solutions. Instead, they are combined to achieve both comprehensive disaster recovery and flexible database management. Infrastructure backups provide continuous protection of the entire database server, while client backups simplify migration, administration, and selective restoration tasks.


Managing Client Backups with the lnbrt Backup Tool

While selecting the appropriate backup strategy is essential, efficient management of backup and restore operations is equally important. As the number of database servers and customer databases increases, manually creating, monitoring, and restoring backups quickly becomes a time-consuming administrative task.

The lnbrt Backup Tool by Linetz is designed to simplify the management of logical database backups. The solution focuses specifically on client backups and provides centralized management of SQL dump backups and restores for MariaDB, MySQL, and PostgreSQL.

Since SQL dumps operate at the database or table level, the lnbrt Backup Tool is particularly well suited for environments where individual databases must be backed up, archived, migrated, or restored independently of the entire database server. The generated dump files can be managed centrally and restored selectively whenever required.

By automating recurring backup tasks, the tool helps reduce administrative effort while ensuring that regular backups are performed consistently. In the event of data loss, individual databases—or, depending on the contents of the dump, specific tables—can be restored without affecting the remaining databases hosted on the server.

← All articles More from „Backup“

 

Copyright © 2026 Linetz – Hosting and IT-Service Hendrik Borgmeyer.