Backup and restore is a common phrase in database conversations and often used to refer to the process of backing up data for protection purposes-restoring it after data loss from an unplanned shutdown, accidental deletion, or data corruption. However, there are other scenarios when you might want to backup or restore your databases or objects within them.
After reading this post, you will be able to:
- Describe common backup and restore scenarios,
- Explain the difference between physical and logical backup,
- List the objects that you can back up and restore, and
- Explain what you need to consider when backing up databases and their objects.
As a data engineer/Data Ninja, you are likely to perform data backup and restore operations to transfer data from one database to another. This may be to facilitate a change of RDBMS, to share data with or load data from a business partner, or to create a copy of the data for use in another location or system, such as development or test. When backing up databases, you can perform either logical or physical backups.
Difference between Logical backups and physical backups:
| Logical Back up | Physical Back up | |
| Content | Creates a file containing DDL (such as create table) and DML commands (such as insert) that recreate the objects and data in the database. As such, you can use this file to recreate the database on the same or another system. | Creates a copy of all the physical storage files and directories that belong to a table, database, or other objects, including the data files, configuration files, and log files to aid point-in-time recovery. |
| Space | when you perform a logical backup and restore, you reclaim any wasted space from the original database because the restore process creates a clean version of the tables. | You can't reclaim the wasted space. |
| Performance | Generating logical backups can take a long time for large databases and may impact the performance of other queries that are concurrently running. | Physical backups are often smaller and quicker than logical backups; they are useful for large or important databases that require fast recovery times. They are similar to backing up any other types of files on your physical system. |
| Granular | Logical backups enable you to backup granular objects. For example, you can back up an individual database or table; however, you cannot use it to backup log files or database configuration settings. You typically use import, export, dump, and load utilities to perform logical backups. | Physical Backup is less granular objects. For Ex, you won't be able to backup an individual database or table. |
You can choose exactly which parts of a database you want to backup. Depending on the RDBMS you are using and type of backup you are performing, you can back up a whole database, the contents of a schema, one or more tables from a database, a subset of data from one or more tables in a database, or a collection of other objects in the database.
It is essential to check these when using backup and restore as part of your disaster recovery plans, as an invalid back up or an inability to restore can result in data loss. You should also ensure that you secure the transfer and storage location of your back up files at the same level that you secure the data in your database.
When performing backups, some RDBMSs support additional options that you can use: You may be able to configure a compression level for the backup files. Compressing the files will reduce the output file size which can be useful for large databases or if you are backing up to a remote location; however, it comes at a cost of time taken to perform the backup and the restore procedures. You may also be able to encrypt the backup files, to reduce the risk of any data being compromised. But again, this will increase the time taken for the backup and restore.
Summary:
In this post, you understood that: You can use backup and restore for data recovery and other purposes. Physical backups create a copy of the raw database storage files and directories whereas logical backups extract the data from a database and save it in a special format. You can backup whole databases or objects within them. You should always check that your backup is safe and usable and that your restore plan works. You can use backup options to compress or encrypt your files.
No comments:
Post a Comment