Type of Database Backup – Physical and Logical

Pre-requisites: Various types of Backup

A database backup is a copy of storage that is stored on a server. Backup is used to prevent unexpected data loss. If original data gets lost, then with the help of a backup, it is easy to gain access to the data again.

There are two types of database backup.

• Physical backup 
•  Logical backup
  

Physical Backup: 

Physical database backups are backups of physical files that are used to store and recover databases. These include different data files, control files, archived redo logs, and many more. Typically, physical backup data is kept in the cloud, offline storage, magnetic tape, or on a disc. 

There are two methods to perform a physical backup :
1. Operating system utilities
2. Recovery manager 

This type of backup is useful when the user needs to restore the complete database in a short period. It is beneficial to provide details of transactions and changes made in databases. It is considered the foundation of the recovery mechanism. This form of backup has the drawback of slowing down database operations.

Advantages:

• It is useful when the user needs to restore the complete database in a short period. 
• They provide details of transactions and changes made in databases.

Disadvantage:

• This slows down database operations.

Logical Backup: 

It contains logical data which is retrieved from the database. It contains a view, procedure, function, and table. This is useful When users want to restore or transfer a copy of the database to a different location. Logical backups are not as secure as physical backups in preventing data loss. It only provides structural details. Every week, complete logical backups should be performed. Logical backups are used as a supplement to a physical backup.

Advantages:

• This is useful when the user needs to restore the complete database to the last time.
• It was more complex and provides granular recovery capabilities.

Disadvantages:

• Critical for recovery of special components.
• less secure compared to physical backup.
• It only provides structural details.

Physical Backup Vs Logical Backup:

Physical Backup	Logical Backup
Physical database backups are backups of physical files that are used to store and recover databases.	Logical database backups are backups of logical files that are retrieved from the database.
It contains data files, control files, and archived redo logs.	It contains a view, a procedure, a function, and a table.
It copies data files when data is running or stopped.	Using the EXPORT keyword Logical backup is done
A user needs to restore the complete database in a short period of time.	This is useful when users want to restore or transfer a copy of the database to a different location.
More secure than logical backup.	Less secure as compared to Physical backup.

Both backup strategies are beneficial. The user can select their technology based on their requirements and organizational demands.