What is the difference between physical and logical backups in database administration?

Physical backups is the backup of an entire database including all objects and logins that is stored on physical device such as windows drive, mount points, NAS,SAN or to cloud. These backups are just like any files that could be moved, copied and removed. However, they persists on the disk and data could be retrieved from these backups whenever needed. On the other hand, logical backup means granular backup at object level. In SQL we don't have support of this however we can import and export database objects. There is no full recovery of database using logical backups. This is helpful when database is in TB's and you are going to modify single object, backing entire database may be time consuming so we can use logical backup here.

A physical backup of the database is a backup of all the physical files that belong to a database. This include the parameter file, control file(s), all the datafiles (datafiles, undo and temp), redo logs and archived logs. These are necessary to start up the database which preferably must have been taken when the database is offline (like a cold backup). This can be dumped on any system with the right CPU, memory allocated and right mount paths, and can used to bring up the DB instance. This is basically not ideal for backup in cases where lose of data is not tolerated or where a continuous transaction processing environment which cannot afford a database downtime. There is very little or no flexibility of available backup/restore options

Physical Backup** A physical backup is a copy of the entire database file system, including all data files, control files, and redo logs. This type of backup is typically created using operating system commands or tools that copy the files to a backup device or location. **Advantages of Physical Backups:** * Faster to create * Less resource-intensive * Captures the complete state and structure of the database * Ensures the consistency and recoverability of the database **Disadvantages of Physical Backups:** * Cannot be restored to different platforms or versions of the database software * Difficult to selectively restore specific objects or schemas * Not as portable as logical backups

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

Physical backups: => Takes the entire backup of the current state of your system including all the files, data and databases, and all other objects. => Used to create a new replicated database from scratch and quickly bring it up. Used for migration scenarios. => Takes more space. => Helps with Point In Time restore depending on the retention period. => Can be used for Disaster Recovery. Logical Backups: => Takes the backup of only the data and databases. => Takes less space comparatively. => Used for periodic and incremetal backup scenarios. => Can be used to restore databases/tables(structured) or collections(nosql) from accidental deletions.

physical backup is the backup of complete database lile datafiles, redologs we can also perform point in time recovery of database with the help of this backup while logical back is the backup of tables, procedures, functions we cannot perform point in time recovery with this backup

This is an option of database backup which is based on availability utilities within the Oracle database software or other vendor based. This can be used to capture all or part of a database and can be restored accordingly. This include backup using SQL statements, data pump, RMAN. They can be used to backup all or part of the database and can also be used to restore a part or all of database as well. These are also available to use in the development and management of other database features as well. There are a lot within this kind of backup which cannot be covered within a 750 words of writeup :)

Online logical backup is not consistent! In the best case it can be consistent for 1 table only. Some logical backups are binary. Like Oracle. Some are text. Like PostgreSQL. It is convenient way to do cross-platform migration of smaller databases.

Logical Backup** A logical backup is a copy of the logical structure and data of the database, typically created using SQL commands or database export tools. This type of backup contains a representation of the database objects, such as tables, views, and procedures, along with the data stored in those objects. **Advantages of Logical Backups:** * Portable and can be restored to different platforms or versions of the database software * Allows selective backup or restoration of specific objects or schemas * Can be used to transfer data between different database systems or formats **Disadvantages of Logical Backups:** * Slower to create and more resource-intensive than physical backups

Logical backup are the backup that can be taken any time intervals without downtime either a full backup or incremental or cumulative backup . as per my experience in an Oracle database it can logical backup can be rman backup ,dump backup or transportable backup . in real-time scenarios, a DBA will get a requirement to perform a full logical restore to dev for testing purposes or a partial/incremental backups to dev and in case of disaster recovery other than the DR we may need to restore the latest backup available advantage of logical backup is it can be compressed backup whereas disadvantage is front end system will be slow during backup due which it is performed during late night or nonworking hours .

Backups methods are physical,logical backup tools like third party. full backup - entire db backup with downtime. logical backup - full or incremental backup for backup and restore but use more resources during working hours not recommended third party backups like - veeam, commvault ,veritas , netbackup , cloud based backup like oci backup . finally export/import backup as dump backup

- Logical backups can only be taken via database vendor provided tools and apis (say for PostgreSQL database you will need an pg_dump). - Physical backups can be taken not only via database vendor tooling but also using filesystem snapshoots, or clonning possibilitites provided by OS / Storage hardware. - it is common misconception that physical backup requires downtime to be taken. In most cases this is misleading, and you can not only make snapshoot of attached disks, you can for example leverage streaming replication and make physical backups from replica, without increasing any load on master.

There`re several backup methods but depending on the circumstances, environment requirements: mission, SLA, performance, recovery models...the DBAs and other team members will need to decide the strategies. Most common ones are: ? Full Backup -captures the entire contents of the database at a specific point in time ? Incremental -capture changes made to the database since the last backup, mostly used where frequent full backups are unsuitable ? Differential -captures the changes made to the database since the last full backup ? Transaction Log Backup -captures the changes made to the database at the transaction level, common where point-in-time recovery is required The less common ones are Snapshot Backup, Logical Backup, Cloud-Based..

In my experience backup played a cruical role as a dba , i have seen disasters during upgrades and backup was only help to restore and make system up and running. I have used a number of backup tools and utilities and found that as a dba we should go with the first priority and use the pre-defined backup utilities and also a third backup for the complete db and OS or file system.

- Only if you ???? y??? ???????, you can say that you ???? ???????. - you need to test backups not only when they are taken, but also few times every year take backups from colder storage and test if they still can be restored - in more complicated system you will need to have both types of backups: logical for long term storage to save costs by not including indexes, and also keep around physical backups for as many days as you need to have point in time recovery. - physical backups will need WAL files for succesfull restore, some database systems know how to include them in backup, in some cases you will be able to do "recovery startup" using only whatever WAL was saved on disk already, but do not count on it.

the best thing to happen is to successfully restore the backup so make sure we always test the backup . for example, weekly or monthly backups perform a test by taking the latest backup as physical or logical backup or incremental backup to dev or test . check the timing it took for backup and restore and resource utilize, try multiple scenarios like human error in moving db file

1) do not invent the wheel if you are just starting as DBA, take off-shelf tool and learn to use it. 2) if you do script own solution, please don't forget about ???. - For point in time recovery (PITR) must keep Write Ahead Logs WAL. You need ALL WAL files between "start of taking physical backup" and "latest moment you plan to restore". - Good practice is to keep all WAL files (be sure your setup works also if replica is promoted to be new primar) in central storage location (writable by all potential primary instances) and also readable by all replicas in case they had network outage and need to catch'up a bit more. - you can then delete all files from that central location that are older than number of days you decided to have for PITR