S 6.49 Data backup in a database

Initiation responsibility: Head of IT Section, IT Security Management

Implementation responsibility: Administrators

As a rule, database systems cannot be backed up fully using backup programs on the operating system level. In most cases, these programs simply act as a link for writing the data on backup media. As far as most products are concerned, backup of the database management system and information in the database additionally requires the use of the database utility programs integrated in the database management system.

The easiest and most reliable way of backing up a database is to perform a full backup with the database shut down. In this process, all files belonging to the database are saved on the storage medium. Usually however, this technique is not feasible due to requirements of database availability or the volume of data to be backed up.

One alternative to the full backup described above is online database backup. In this case, the database is backed up while remaining in operation, i.e. it does not need to be shut down. The disadvantages of this type of backup are that inconsistencies cannot be ruled out explicitly, and that a full, offline backup must nevertheless be available in the event of damage to the database, to serve as a secure foundation for re-loading the online backups. For this reason, online backups should only be performed if the database needs to remain available on a continuous basis. Full, offline backups should nevertheless be performed at reasonably regular intervals.

Partial database backups constitute another alternative. They should be made use of whenever the data volume requiring backup is too large to allow a full backup. This can result from situations in which the capacity of the backup media has been exhausted or insufficient time is available for performing a full backup.

If possible, all transactions between two full, offline backups should be archived. In Oracle databases, for instance, the ARCHIVE mode can be activated for this purpose. In Oracle, transactions are recorded in several log files. These log files are written consecutively. Once all the files are full, the first file is overwritten again. The ARCHIVE mode prepares backup copies of the files before they are overwritten. This permits all transactions to be reconstructed fully in the event of damage to the database. However, the existence of a full database backup is also a prerequisite in this case. The duration of such a recovery increases with the number of archived log files which need to be restored.

A database backup policy needs to be prepared for backing up a database system. Influencing factors in this policy are:

• Requirements of database availability

If a database needs to remain available round the clock on weekdays, for example, full backups can only be performed on weekends, as the database generally needs to be shut down for this purpose.

• Data volumes

The total volume of data requiring backup must be compared with the available storage capacity of the backup media. Here it is necessary to determine whether the backup storage capacity (for example, one DAT tape per backup session) is sufficiently large for the volume of data held in the database.

If the backup storage capacity is insufficient, a concept for partial backup of the database must be prepared. This might involve, for example, backing up the data of individual applications or individual sectors of the database in rotation, or only backing up modified data. The possibilities of partial backup depend on the database software in use.

• Maximum permissible data loss

Here, it is necessary to specify whether a loss of data accumulated in the course of one day is permissible in the event of damage to a database, or whether the database should be restorable right up to the last transaction. The latter option is generally chosen in cases where high demands are placed on the availability and integrity of the data.

• Restart time

The maximum permissible time taken to restore a database after a crash must also be specified in order to meet the applicable availability requirements.

• Possibilities of backing up provided by the database software

Standard database software does not generally support all conceivable possibilities of data backup, such as partial database backup. In individual cases, a check is therefore required as to whether the prepared database backup policy can be implemented with the available mechanisms.

This information can be used as a basis for defining a database backup policy which must include a specification of the following items (also refer to Chapter 3.4 Data backup policy)

• Persons responsible for the orderly carrying out of data backups

• The intervals at which database backups are to be performed

• The database backup techniques

• The times at which database backups are to be performed

• The data volume which is to be backed up in each session

• Documentation on performed data backups

• Storage locations for the database backup media

Example:

Backup from Monday to Saturday:

• Starting time: 3.00 am.

• A backup of all relevant data is performed using the online database backup feature of the database management system, i.e. the database is not shut down.

Backup on Sunday

• Starting time: 3.00 am.

• The database is shut down and a full offline backup is performed.

Additional controls:

• Is there any documentation describing how the database is to be restored in the event of a crash?

• Has a current database backup policy been documented for the institution in question?

• How are staff members informed about the sections of the concept which are applicable to them?

• Is adherence to the concept monitored?

• How are changes in the influencing factors taken into account?