Data Backup in DBMS  

In a computer system we have primary and secondary memory storage. Primary memory storage devices – RAM is a volatile memory which stores disk buffer, active logs, and other related data of a database. It stores all the recent transactions and the results too. When a query is fired, the database first fetches in the primary memory for the data, if it does not exists there, then it moves to the secondary memory to fetch the record. Fetching the record from primary memory is always faster than secondary memory.  What happens if the primary memory crashes? All the data in the primary memory is lost and we cannot recover the database.

In such cases, we can follow any one the following steps so that data in the primary memory are not lost.

  • We can create a copy of primary memory in the database with all the logs and buffers, and are copied periodically into database. So in case of any failure, we will not lose all the data. We can recover the data till the point it is last copied to the database.
  • We can have checkpoints created at several places so that data is copied to the database.

Suppose the secondary memory itself crashes. What happens to the data stored in it? All the data are lost and we cannot recover. We have to think of some alternative solution for this because we cannot afford for loss of data in huge database.

There are three methods used to back up the data in the secondary memory, so that it can be recovered if there is any failure.

  • Remote Backup : Database copy is created and stored in the remote network. This database is periodically updated with the current database so that it will be in sync with data and other details. This remote database can be updated manually called offline backup. It can be backed up online where the data is updated at current and remote database simultaneously. In this case, as soon as there is a failure of current database, system automatically switches to the remote database and starts functioning. The user will not know that there was a failure.
  • In the second method, database is copied to memory devices like magnetic tapes and kept at secured place. If there is any failure, the data would be copied from these tapes to bring the database up.
  • As the database grows, it is an overhead to backup whole database. Hence only the log files are backed up at regular intervals. These log files will have all the information about the transaction being made. So seeing these log files, database can be recovered. In this method log files are backed up at regular intervals, and database is backed up once in a week.
See also
OSI Model

There are two types of data backup – physical data backup and Logical data backup. The physical data backup includes physical files like data files, log files, control files, redo- undo logs etc. They are the foundation of the recovery mechanism in the database as they provide the minute details about the transactions and modification to the database

Logical backup include backup of logical data like tables, views, procedures, functions etc. Logical data backup alone is not sufficient to recover the database as they provide only the structural information. The physical data back actually provides the minute details about the database and is very much important for recovery.