What is the difference between logical and physical backup? When should each be used?

A logical backup is a saving of data in the form of an SQL dump or another format that reflects the structure and content of the database (for example, CREATE TABLE commands, INSERT). Such a backup is convenient for migrations, restoring individual tables or data, and transferring between different database management system (DBMS) versions.

A physical backup is copying database files at the file system level (for example, a copy of data files, transaction logs). It is faster and allows restoring the database to the exact state at the time of the backup, including all internal structures.

When to use:

• Logical backup is suitable for small databases, when data needs to be transferred between different DBMSs or versions, or when readability and partial restoration are important.
• Physical backup is better for large databases, when speed of restoration and integrity at the whole database level are important, especially for high-load systems.