What is the difference between physical and logical backup?

Question

I was reading about backup. I understood what physical backup is. But I am not able understand what logical backup is? how does it work?

Pictorial representation of the working would help.

Thanks in advance

Answer 1

Logical vs. Physical (Basic difference):
Logical backup is using SQL statements. Export using exp tool is logical.
Physical backup is copying the data files either when the database is up and running (HOT BACKUP) or when the database is shutdown (COLD BACKUP)

In other words,

• physical backup is to copy for backing up all the physical files that belongs to database.(like data files,control files,log files, executables etc).
• In logical backup, you don't take the copies of any physical things,you only extract the data from the data files into dump files.(ex : using export )

Read This Article

Physical Backup

The operating system saves the database files onto tape or some other media. This is useful to restire the system to an earlier point whenever needed.

Logical Backup

In logical backup technique, the IMPORT/EXPORT utilities are used to create the backup of the database. A logical backup backs-up the contents of the database. A logical backup can be used to restore the database to the last backup. However, unlike physical back, it should not be used to create an OS back up copy because restoring using this approach would make it possible to correct the damaged datafiles. Therefore in these situations physical backups should be preferred.

more types as... Cold & Hot backups under Physical Backup, is also explained there.

---

Logical vs. Physical Database Backups :

Once you’ve made a decision about your uptime requirements, you need to make decisions about what kind of data you will be backing up from your databases.

1. Physical Files, such as text files, are stored as a single document on your hard drive. Although databases consist of many complex elements, these are usually aggregated into simple files on your server’s hard drive. These files can easily be backed up just like any other files on your system.
2. Logical Data Elements such as tables, records and their associated meta data are stored across many different locations. Backups for tables and other logical database elements usually require special tools that are designed to work with your specific database platforms. Although these types of backups are more complex, they offer more granular recovery capabilities. This is especially true when doing point-in-time recovery of tables that involve complex transactions and inter-dependencies.

Logical database backups are critical for granular recovery of specific components. And Physical backups are useful for full disaster recovery scenarios. The choice between Logical and Physical database backups should be covered as part of your Recovery Point Objectives. (RPOs)