Database Management

Database Backup and Recovery

Although most database systems have incorporated backup and recovery tools into their interfaces and infrastructure it's imperative to understand what the backup and recovery process involves.

Database Backup and Recovery Needs

It is not just data files that need to be part of the backup process. Transaction logs must also be backed up. Without the transaction logs the data files are useless in a recovery event.

Backup and Recovery and Database Failure

There are three main reasons of failure that happen enough to be worth incorporating into your backup and recovery plan. User error is the biggest reason for data damage, loss, or corruption. In this type of failure, there is an application modifying or destroying the data on its own or through a user choice. To fix this problem you must recover and restore to the point in time before the corruption occurred. This returns the data to it's original state at the cost of any other changes that were being made to the data since the point the corruption took place. Hardware failure can also cause data loss or damage. Hardware failure can happen when the drives the data files or transaction logs are stored on fail. Most databases will be stored on computer hard drives or across groups of hard drives on designated servers. Hard drives are mechanical devices, just like automobiles. Mechanical devices are known for failure and should be replaced a few times during its lifetime.

Backup and Recovery and Disaster

The third reason for database failure is a disastrous or catastrophic event. This can be in the form of fire, flood, or any naturally occurring storm. It can also happen through electrical outage, a virus, or the deliberate hacking of your data. Any of these events can corrupt or cause the loss of your data. The true disaster would be the lack of data backup or the lack of a recovery plan during an event this severe. Without data backup, recovery is impossible. And without a recovery plan there is no guarantee that your data backup will make it through the recovery process.

Methods of Backup

There are three main back up methods in SQL, full, transaction log, and differential. In a full backup, a full copy of the database is made, including all changes and transaction logs. This method takes a large period of time. In a transaction log backup, the transaction log’s activities that have occurred since the last full or transactual back up are saved. The prior transaction logs are truncated in order to keep the files as small as possible. A differential backup saves all the data and transaction logs that have changed since the last full backup. This type of backup generates files smaller than the full backup.

Back-Up SQL Statement

backup database DataBaseName to disk = ‘place of back up’
with description = 'Backup description',
name = NameofBackup';

Physical and Logical Backups

Physical backups are backups of the physical files used in storing and recovering your database, such as data files, control files, and archived redo logs. Ultimately, every physical backup is a copy of files storing database information to some other location, whether on disk or some offline storage such as tape.

Logical backups contain logical data (for example, tables or stored procedures) exported from a database with an Oracle export utility and stored in a binary file, for later re-importing into a database using the corresponding Oracle import utility.

Physical backups are the foundation of any sound backup and recovery strategy. Logical backups are a useful supplement to physical backups in many circumstances but are not sufficient protection against data loss without physical backups.

Unless otherwise specified, the term "backup" as used in the backup and recovery documentation refers to physical backups, and to back up part or your entire database is to take some kind of physical backup.

Sources: [1] [2] [3]