Database Backups 101: Logical vs Physical Backups

Databases are important to all businesses, therefore, it is fundamental to have a good backup policy in place and put it into implementation right from the start. Prior to deciding on the database backup method, it is first important to decide on what type of data needs to be backed up and how it might affect the overall uptime of the business.

Thinking this way is a good rule of thumb when drafting a backup strategy to ensure that recovery occurs quickly that the data is secure and, of course, that you minimize the downtime.

Database backups can be categorised as either a physical or logical backup, and in this blog we will explain the differences.

What is a Physical Database Backup?

Physical database backups are a backup of database directories and files (such as data files, data controls and archived logs). The backups should always be stored in a separate, dedicated storage (such as a disk, tape, or in the cloud). This type of backup is useful when you need to restore the complete database within a short period of time. You can have either a full or incremental backup for your physical backup.

A full physical backup will contain a complete set of data files, archived files, and transaction files. This is needed if you want to perform a full database restoration with minimal application errors and data loss.

It’s always advisable to have a full physical backup done on a weekly or monthly basis. It is best to take them in complete downtime, (meaning there are no database interactions) this is to avoid any inconsistent data showing up during the data recovery. Should you only need to have a backup of the recent transactions, then you should perform an incremental physical backup, which does not require any downtime. This type of backup might slow down the database processes, however.

What is a Logical Database Backup?

A Logical Backup contains copies of information about a database (such as tables, schemas, and procedures) and are commonly exported out as binary files using EXPORT/ IMPORT tools. If you need to restore or move a copy of the database to another environment (platform) then, logical backups are a great option.

With the help of an appropriate import tool, the logical data elements (such as tables, schemas, and procedures) in the backup can be restored at a lowest level with all the interdepencies elements on a specific database platform.

The data of a table is also exported using Structure Query Language (SQL) and this is stored in a binary format as well. It does not contain any instance-related information (any location or environment information like a physical backup) and can be restored on any other machines or environments and is not suitable for any OS-level restoration because it only contains the structural level information of a database.

Full logical backups should be done on a weekly basis and it is commonly used as a substitute if the full, physical backup is not available.

Physical Database Backups vs Logical Database Backups

Both types of backups hold copies of the content in a different format. The format plays an important role in the size backup, hence where storage size is of concern, the logical backup can be given a priority and performed at a frequent interval.

For a complete system restoration, physical backups are a good option as it has the complete information at the backup point (hence it requires less restoration time).

If the database is to be restored at a different location or environment, then a logical backup would be the best choice.

Pros & Cons of Physical & Logical Database Backups

With logical backups, you can backup a selected set of data (saves time and storage). This type of backup also allows filters to exclude certain data during the export and is resilient to errors caused by storage media like tapes. If there is a missing link point, the logical backup has the ability to rebuild the structure.

The downside of this type of backup is that it does not have the file system information (making the restoring process tougher).

The primary benefit of a physical backup is it is simple and fast, as it does not care about the format of data. It merely makes a mirror copy and loads it to another device.

The negative side of physical backups is that it is typically only used to recreate the system at restoration and will not be able to do a full restore if there are any missing files. Should this scenario arise, you need to ensure the backup is recreated with the missing files and then proceed to the restore process.

Conclusion

In summary a logical database backup is a copy of the logical information of a database extracted and stored in a binary format. A physical database backup is a copy of the complete database contents , structure, tables and all relevant transactions you need to restore your database.

So which is the best? This really depends on the backup and restoration planning needed to meet the organisational needs. It’s always best to have both types of backups, however, the restoration and retention should clearly specify the frequency method and the data being included in the backup right from the start.