What is the difference between physical and logical backup? [closed]

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

Yeseniayeshiva

Answer 1

In my understanding, a logical backup is just an export of one or more tables from the database. If it contains all tables of the database, one can use it to restore the state of the database at the time when the logical backup was made. One can also use it to import the tables into a different database. For instance, a script with CREATE TABLE and INSERT statements would be a possible file format for a logical backup (as used by MySQL - Oracle has its own file format for export files).

A physical backup is a copy of the internal database files. Only this permits to use the log files to restore the database to the last second before the media failure (i.e. to a much later time than the time of the backup - as long as one has a copy of all log files that were written since the time of the backup). I.e. only this is the "real backup" one usually expects from a database that is constantly updated.

(Just for safety: Note that a copy of the internal database files while the database is running will be of no help, unless special precautions are taken: Since the copying takes some time, it will give an inconsistent view of the files. Check the manual of your DBMS for "hot backups" if you cannot shut down the DBMS before copying the files. It is also essential to protect the log files, e.g. by duplicating them on two independent disks. In Oracle, you must switch to ARCHIVELOG mode to make sure that the log files are not overwritten after some time. In general, being really prepared for a media failure needs a lot of knowledge and also practical tests on a different computer. A logical backup is probably simpler and there is less risk that it turns out to be completely unusable when one needs it, because the file format is simpler. However, long ago, I destroyed German national characters in an Oracle export, because at that time ASCII was the default character encoding.)

Gylys

Wasn't me, but the question is clearly off topic, not to mention 7 years old. Sometimes downvotes are used to hint you shouldn't be answering a question, regardless of your answer's quality. – Clytemnestra

You can always delete it. Just keep in mind the site rules when answering questions, especially old ones which may have been on topic at one time but are no longer. – Clytemnestra

I found this question when I searched with Google for the difference between logical and physical backup. After I understood it myself, I wrote this answer (which is what I personally would have liked to find). I am not going to delete it, but I will think twice before writing more answers. – Gylys

I upvoted you as it helped me – Precis

Thank you. In this way my answer no longer has a negative impact on my score. – Gylys