Accounting, ERP, CRM, and line-of-business applications all keep their most important data in a database. Copying the database files while the server is running is not a reliable backup. Each database engine has its own tools for taking consistent backups, and the right approach depends on how much data you can afford to lose.

Start with two numbers

RPO (recovery point objective) is how much data you can afford to lose — five minutes, an hour, a day. RTO (recovery time objective) is how quickly the system must be back. They decide everything below.

Microsoft SQL Server

  • Full backups copy the whole database, typically nightly or weekly.
  • Differential backups capture changes since the last full backup and keep restores quick.
  • Transaction log backups, taken every few minutes, are what make point-in-time recovery possible. They require the database to use the full recovery model; under the simple model you can only restore to your last full or differential backup.
  • Use backup checksums and run RESTORE VERIFYONLY, but remember that only a real restore proves a backup is usable.

MySQL and MariaDB

  • Logical backups with mysqldump (using --single-transaction for InnoDB tables) are simple and portable, and suit small and medium databases.
  • Physical backups with tools such as Percona XtraBackup or MariaDB Backup are faster for large databases.
  • Binary logs, kept alongside your backups, let you replay changes and recover to a specific point in time.

Rules that apply to every database

  • Follow 3-2-1: three copies, on two kinds of storage, one off-site.
  • Encrypt backups and restrict who can access them — a stolen backup is a data breach.
  • Keep at least one copy that ransomware cannot delete or encrypt, such as immutable or offline storage.
  • Monitor every backup job and alert on failures, not just on successes.
  • Test restores on a schedule. Restore to a separate server, check the application works, and time it against your RTO.

We design, run, and test database backups for SQL Server, MySQL, MariaDB, and Oracle — and we restore them regularly, so the first time you rely on a backup is never the first time it has been tested.

Related service: Database Management — Oracle, Microsoft SQL, and MySQL solutions.

Get a free IT assessment All resources