S 6.49 Data backup in a database

Initiation responsibility: IT Security Officer, Head of IT

Implementation responsibility: Administrator

It is generally impossible to make a full backup of the data in a database system using data backup programs running on the operating system level. In most cases, such data backup programs are just one link in a chain and are only used to write the data to be saved on a backup medium. Instead, it is necessary in most database products to additionally use the corresponding service programs offered by the DBMS to back up the DBMS and the data.

The easiest way to back up a database, which is also the most secure way at the same time, is to create a full backup of the database when the database is shut down. In this case, all files belonging to the database are saved to the backup medium. However, this procedure usually cannot be used due to the availability requirements regarding the database or due to the amount of data needed to be backed up.

One alternative to the full backup described above is to back up the database online. In this case, the backup can be performed during live operation of the database, i.e. the database does not have to be shut down. The disadvantages of this type of backup include that inconsistencies cannot be explicitly ruled out and that a full (offline) backup must be available nevertheless if the database is destroyed as a secure basis for the restoration of the online backups. For this reason, online backups should only be performed when the database needs to be available at all times. Nevertheless, full offline backups should be performed at reasonably long intervals.

Partial database backups are another possibility. They should always be used if the amount of data to be backed up is too large to enable the generation of a full backup. This may be the case if the capacity of the backup media is inadequate or if there is not enough time available to create a full backup.

If possible, all transactions completed between two full offline backups should be archived in any case. Oracle offers this capability, for example, by enabling the so-called ARCHIVE mode for the database. Transactions are recorded in Oracle in log files, of which there are several. These are filled consecutively and once all log files are full, the first log file is overwritten. The ARCHIVE mode creates backup copies of these log files before they are overwritten. In this manner, it is possible to completely reconstruct all transactions in the event of the destruction of the database. However, the existence of a full backup of the database is still a prerequisite for complete reconstruction. The amount of time required for such a recovery increases as the number of archive log files to be restored increases.

A separate data backup policy must be drawn up to govern the data backup procedure for a database system. Factors influencing such a policy include the following:

• availability requirements regarding the database
  
  If, for example, a database needs to be available around the clock on workdays, a full backup can only be performed on the weekend, since it is usually necessary to shut down the database to make a full backup.
• data volume
  
  The entire amount of the data to be backed up must be compared to the amount of backup storage capacity available. In this case, it must be determined if the backup storage capacity (one DAT tape per backup, for example) is sufficient to store the amount of data in the database to be backed up.
  
  If this is not the case, a policy for the partial backup of the data volume must be drawn up. For example, this may mean alternating the backups of the data of individual applications or individual sections of the database and/or only backing up the modified data. The capabilities offered by a partial backup depend on which database software is used.
• maximum tolerable loss of data
  
  It must be specified whether the loss of one day's data is tolerable in the event of the destruction of the database or whether it must be possible to completely restore the database up to the last transaction. This is generally the case when there are high requirements regarding the availability and/or integrity of the data.
• recovery time
  
  A maximum time admissible for the recovery of the database after a database crash must be specified that meets the corresponding availability requirements.
• data backup capabilities of the database software
  
  In general, not every conceivable data backup method, for example a partial database backup, is supported by standard database software. For this reason, it must be examined in each individual case whether the data backup concept drawn up can also be implemented using the available mechanisms.

Based on this information, a policy for backing up the data in the database can be drawn up. The following, amongst other things, must be specified in this database backup policy (see also module S 1.4 Data backup policy):

• the persons responsible for the proper execution of the data backups,
• how often the database backups should be performed,
• what type of database backup is required and how it should be performed,
• the times at which the database backups will be created,
• specification of the amount of data to be saved per backup,
• how the creation of the data backups will be documented,
• where the data backup media will be kept.

Example:

Backups from Monday through Saturday:

• start time: 3:00 AM
• a full backup of the data is generated, whereby the database is not shut down in this case, but the online backup capabilities of the DBMS are used instead.

Backup on Sunday

• start time: 3:00 AM
• the database is shut down and a full backup is made of the database.

Review questions:

• Is there a data backup policy for the database system taking into consideration the special aspects of database backup?