Database Backup and Recovery

Database Backup

  • Database Backup is storage of data that means the copy of the data.
  • It is a safeguard against unexpected data loss and application errors.
  • It protects the database against data loss.
  • If the original data is lost, then using the backup it can reconstructed.
The backups are divided into two types,
1. Physical Backup
2. Logical Backup

1. Physical backups
  • Physical Backups are the backups of the physical files used in storing and recovering your database, such as datafiles, control files and archived redo logs, log files.
  • It is a copy of files storing database information to some other location, such as disk, some offline storage like magnetic tape.
  • Physical backups are the foundation of the recovery mechanism in the database.
  • Physical backup provides the minute details about the transaction and modification to the database.
2. Logical backup
  • Logical Backup contains logical data which is extracted from a database.
  • It includes backup of logical data like views, procedures, functions, tables, etc.
  • It is a useful supplement to physical backups in many circumstances but  not a sufficient protection against data loss without physical backups, because logical backup provides only structural information.

Importance of Backups

  • Planning and testing backup helps against failure of media, operating system, software and any other kind of failures that cause a serious data crash.
  • It determines the speed and success of the recovery.
  • Physical backup extracts data from physical storage (usually from disk to tape). Operating system is an example of physical backup.
  • Logical backup extracts data using SQL from the database and store it in a binary file.
  • Logical backup is used to restore the database objects into the database. So the logical backup utilities allow DBA (Database Administrator) to back up and recover selected objects within the database.

Storage of Data

Data storage is the memory structure in the system.

The storage of data is divided into three categories:
1. Volatile Memory
2. Non – Volatile Memory
3. Stable Memory

1. Volatile Memory
  • Volatile memory can store only a small amount of data. For eg. Main memory, cache memory etc.
  • Volatile memory is the primary memory device in the system and placed along with the CPU.
  • In volatile memory, if the system crashes, then the data will be lost.
  • RAM is a primary storage device which stores a disk buffer, active logs and other related data of a database.
  • Primary memory is always faster than secondary memory.
  • When we fire a query, the database fetches a data from the primary memory and then moves to the secondary memory to fetch the record.
  • If the primary memory crashes, then the whole data in the primary memory is lost and cannot be recovered.
  • To avoid data loss, create a copy of primary memory in the database with all the logs and buffers, create checkpoints at several places so the data is copied to the database.
2. Non – Volatile Memory
  • Non – volatile memory is the secondary memory.
  • These memories are huge in size, but slow in processing. For eg. Flash memory, hard disk, magnetic tapes etc.
  • If the secondary memory crashes, whole data in the primary memory is lost and cannot be recovered.
To avoid data loss in the secondary memory, there are three methods used to back it up :

1. Remote backup creates a database copy and stores it in the remote network. The database is updated with the current database and sync with data and other details.

The remote backup is also called as an offline backup because it can be updated manually. If the current database fails, then the system automatically switches to the remote database and starts functioning. The user will not know that there was a failure.

2. The database is copied to secondary memory devices like Flash memory, hard disk, magnetic tapes, etc. and kept in a secured place. If the system crashes or any failure occurs, the data would be copied from these tapes to bring the database up.

3. The huge amount of data is an overhead to backup the whole database. To overcome this problem the log files are backed up at regular intervals.

The log file includes all the information about the transaction being made. These files are backed up at regular intervals and the database is backed up once in a week.  

3. Stable Memory
  • Stable memory is the third form of the memory structure and same as non-volatile memory.
  • In stable memory, copies of the same non – volatile memories are stored in different places, because if the system crashes and data loss occurs, the data can be recovered from other copies.

Causes of Database Failures

  • A database includes a huge amount of data and transaction.
  • If the system crashes or failure occurs, then it is very difficult to recover the database.

    • There are some common causes of failures such as,
    1. System Crash
    2. Transaction Failure
    3. Network Failure
    4. Disk Failure
    5. Media Failure

  • Each transaction has ACID property. If we fail to maintain the ACID properties, it is the failure of the database system.
1. System Crash
  • System crash occurs when there is a hardware or software failure or external factors like a power failure.
  • The data in the secondary memory is not affected when system crashes because the database has lots of integrity. Checkpoint prevents the loss of data from secondary memory.
2. Transaction Failure
  • The transaction failure is affected on only few tables or processes because of logical errors in the code.
  • This failure occurs when there are system errors like deadlock or unavailability of system resources to execute the transaction.
3. Network Failure
  • A network failure occurs when a client – server configuration or distributed database system are connected by communication networks.
4. Disk Failure
  • Disk Failure occurs when there are issues with hard disks like formation of bad sectors, disk head crash, unavailability of disk etc.
5. Media Failure
  • Media failure is the most dangerous failure because, it takes more time to recover than any other kind of failures.
  • A disk controller or disk head crash is a typical example of media failure.
  • Natural disasters like floods, earthquakes, power failures, etc. damage the data.
▲