A backup is useful when you can restore the data you need within the time your business allows. A file called backup.sql does not prove that. You need a consistent capture method, protected storage, compatible tools, and a tested recovery procedure. This reference explains logical and physical backups, recovery point and recovery time objectives, and safe restore testing across common SQL engines. It provides a read-only verification query rather than executing backup or restore commands. Actual recovery operations can overwrite data, so they belong to a deliberately selected isolated target and a reviewed operational procedure.
Define how much loss and downtime are acceptable
Recovery point objective, or RPO, describes the tolerated amount of data loss measured in time. Recovery time objective, or RTO, describes how quickly service should be restored. A nightly backup may fit a low-change internal catalog but be unacceptable for a busy order system. Ask which data and services must recover together, including uploads, configuration, secrets, and background job state. A database backup alone cannot restore files stored elsewhere. Record who owns the recovery process and how operators access the required credentials during an outage. Objectives should be realistic and tested, not copied from another organization. Backup frequency, transaction-log retention, and infrastructure choices follow those requirements.
Distinguish logical and physical backups
A logical backup represents schemas and data as SQL or a structured dump format that database tools can restore. It can be flexible for migration and selective recovery, but large restores may be slower. A physical backup captures database storage under an engine-supported consistency procedure and can suit large systems or point-in-time recovery. Simply copying active database files may produce an inconsistent set unless the engine's backup method or a valid snapshot process is used. Tool and server version compatibility matters. Neither style automatically includes every role, extension, external file, or encryption key. Inventory the required objects and metadata so the restore plan accounts for them.
Use each engine's supported tooling
PostgreSQL provides logical dump tools and physical backup methods, with continuous archiving options for point-in-time recovery. MySQL offers logical dumps and engine-specific physical and log-based approaches. SQL Server has its own BACKUP and RESTORE commands, recovery models, and transaction-log requirements. SQLite provides a backup API and documented techniques for safe copies; a live database in WAL mode can involve related files, so an arbitrary copy of only the main file is not a universal procedure. Choose the selected engine's documented method and supported tool version. Hosted services may supply managed backups but still require you to understand retention, restore granularity, permissions, and the time needed to create a recovered instance.
Protect the backup as sensitive production data
Backups can contain passwords hashes, personal records, tokens, and confidential business information. Encrypt and access-control them under the data policy, protect encryption keys separately, and monitor successful transfer to the intended storage. Keep copies outside the same failure domain where appropriate. A backup stored only on the database disk does not help if that disk is lost. Apply retention rules balancing recovery needs and privacy obligations. Avoid putting credentials in command arguments or world-readable files. Review who can download or restore a backup because those permissions can grant access equivalent to the original database. Deletion or expiration should be verifiable rather than assumed.
Restore into an isolated target and validate meaning
A restore test should use a clearly identified disposable environment, not overwrite production casually. Record the backup timestamp, source version, tool version, and target. Restore schema, data, roles or grants where needed, and external dependencies under the plan. Check row counts, representative records, foreign-key relationships, constraints, and application queries. A completed restore command proves only part of the process. Confirm the application can authenticate and perform controlled operations against the restored environment without contacting live payment or mail services. Measure elapsed recovery time and identify missing documentation. Remove or protect the restored copy afterward because it contains real data even if the server is labeled test.
Exercise point-in-time and operational recovery
A full backup plus valid transaction logs can support recovery to a selected point under the engine's method. That requires a complete usable log chain, retention, and tested tooling. It can help recover from an accidental deletion before the next backup, but selecting the correct recovery point is an operational decision. Practice a scenario with known markers and verify which records exist after recovery. Keep a runbook accessible during an outage and avoid relying on the unavailable production system to retrieve it. Monitor backup job failures, storage capacity, and restore-test age. Replication improves availability in some designs but can replicate accidental deletion too; it is not a replacement for a reviewed backup and recovery policy.
Practice task and worked solution
Create a recovery test plan for a small shop. Record RPO and RTO, choose the engine's supported backup method, and include order rows, uploaded receipts, roles, required extensions, and secret-access procedures. The solution restores into an isolated instance with outbound mail and payment calls disabled. Verify representative orders, counts, constraints, receipt availability, and application reads. Measure time from initiating recovery to a usable checked application. Then test a known transaction marker around a point-in-time target if the backup strategy supports it. Document failures and update the runbook. Do not equate a successful backup upload with a proven restore.
Worked PostgreSQL logical backup and isolated restore
The following commands illustrate a PostgreSQL custom-format dump and a restore into a newly created test database. They are shell commands, not SQL statements. Use a compatible pg_dump and pg_restore version, a source account permitted to read the required objects, and a controlled test server where you are allowed to create shop_restore_check. Configure connection settings and credentials through the environment's protected authentication mechanism rather than placing a password in the command line. The source database is named shop. Choose a fresh target name and verify the host before running; the commands here are displayed only and are not executed by this lesson.
pg_dump --format=custom --file=shop.backup shop
createdb shop_restore_check
pg_restore --dbname=shop_restore_check --no-owner shop.backuppg_dump captures the database objects covered by its options; it does not automatically supply every cluster-wide role or external receipt file. The custom archive is read by pg_restore. The target is a separate newly created database, and --no-owner avoids trying to recreate source ownership assignments during this test. Roles referenced by grants may still need controlled preparation or an appropriate documented privilege policy. Inspect each command's exit status and diagnostics before treating the next step as successful. Do not add --clean or redirect the restore to production casually.
Connect to shop_restore_check with the test application's permitted identity and run the verification queries above against its customers and orders. Compare counts with recorded expectations for the dump snapshot, check representative records and constraints, and test the application with outbound side effects disabled. Expected operational result: a readable archive and a separate restored database containing the covered schema and data. Archive size, row counts, duration, and warnings depend on the source and environment. Protect both the archive and restored data, and remove the isolated copy through the organization's reviewed lifecycle after the test.
Continue learning
Review transactions, security, and table creation.
The PostgreSQL backup chapter distinguishes backup approaches and recovery considerations; the SQLite backup API documents its supported copy mechanism.
Database Backup and Restore Basics Examples
Each example shows the code, the output it produces and what each part does.
Check restored parent-child integrity
This read-only generic query assumes restored customers and orders tables. It identifies orphan orders that should not exist under an enforced relationship.
A healthy fixture with enforced order-customer relationships returns no rows from the orphan query. Counts should match the recorded backup expectations under the selected snapshot. Empty orphan output alone does not verify every field or application behavior.
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL
ORDER BY o.order_id;
-- Additional verification:
SELECT COUNT(*) AS customer_count FROM customers;
SELECT COUNT(*) AS order_count FROM orders;Orphan query: no rows expected
customer_count: compare with recorded backup snapshot
order_count: compare with recorded backup snapshotDatabase Backup and Restore Basics FAQ
Is replication a backup?
No. A replica copies every change, including mistakes such as an accidental DELETE, within seconds. Keep a separate recovery strategy with backups taken at points in time, stored separately, so you can return to a state before the mistake.
Can I copy active database files?
Not safely with an ordinary file copy, because files change while being copied and the result may be inconsistent. Use the engine's supported consistency method, such as pg_dump, mysqldump, SQLite's .backup command or a snapshot feature.
Does a completed restore prove the application works?
No. A restore that finishes without errors can still be missing data or have broken permissions. Validate data, for example row counts and recent records, and application behaviour, by running key workflows against the restored copy.
Should restore tests use production targets?
No. Use a deliberately isolated environment, so a mistake in the test can't overwrite live data or send emails to real customers. Treat the restored copy as sensitive, because it contains production data.
How often should I back up a database?
Base it on how much data the business can afford to lose, called the recovery point objective. If losing a day is acceptable, nightly backups may do; if not, add continuous archiving of the transaction log, such as PostgreSQL's WAL archiving or MySQL's binary logs, for point-in-time recovery.
Database Backup and Restore Basics Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
A replica immediately receives an accidental DELETE. What additional protection is needed?
Database Backup and Restore Basics Quiz
2 questions to check what you learned.
What does RPO describe?
- The tolerated data-loss interval
- The maximum duration of restoring service after a failure
- The retention period of the oldest backup only
- The amount of RAM needed by the backup tool
What best proves a backup is usable?
- A completed upload without attempting recovery
- A tested restore with meaningful data and application checks
- A backup filename that includes the current date
- A replica being online without testing historical recovery