Free Tutors On How Classical back-up of databases works

Free Tutors On How Classical back-up of databases works

As in all applications, the consistency of backed up data also has to be ensured in databases. In databases, consistency means that the property of atomicity of the transactions is maintained. After the restoration of a database it must therefore be ensured that only the results of completed transactions are present in the data set. In this section we discuss various back-up methods that guarantee precisely this. In the next section we explain how storage networks and intelligent storage systems help to accelerate the back-up of databases (Section 7.10.3).

The simplest method for the back-up of databases is the so-called cold back-up. For cold back-up, the database is shut down so that all transactions are concluded, and then the files or volumes in question are backed up. In this method, databases are backed up in exactly the same way as file systems. In this case it is a simple matter to guarantee the consistency of the backed up data because no transactions are taking place during the back-up. Cold back-up is a simple to realize method for the back-up of databases. However, it has two disadvantages. First, in a 24 × 7 environment you cannot afford to shut down databases for back-up, particularly as the back-up of large databases using conventional methods can take several hours. Second, without further measures all changes since the last back-up would be lost in the event of the failure of a disk subsystem. For example, if a database is backed up overnight and the disk subsystem fails on the following evening

all changes from the last working day are lost. With the aid of the archive log file the second problem, at least, can be solved. The latest

state of the database can be recreated from the last back-up of the database, all archive log files backed up since and the active log files. To achieve this, the last back-up of the database must first of all be restored from the back-up medium – in the example above the back-up from the previous night. Then all archive log files that have been created since the last back-up are applied to the data set, as are all active log files. This procedure, which is also called forward recovery of databases, makes it possible to restore the latest state even a long time after the last back-up of the database. However, depending upon the size of the archive log files this can take some time. The availability of the archive log files is thus an important prerequisite for the successful forward recovery of a database. The file system for the archive log files should,

therefore, be stored on a different hard disk to the database itself (Figure 7.19) and additionally protected by a redundant RAID procedure. Furthermore, the archive log files should be backed up regularly. Log files and archive log files form the basis of two further back-up methods for databases: hot back-up and fuzzy back-up. In hot back-up, the database system writes pending changes to the database to the log files only. The actual database remains unchanged at this time, so that the consistency of the back-up is guaranteed. After the end of the back-up, the database system is switched back into the normal state. The database system can then incorporate the changes listed in the log files into the database. Hot back-up is suitable for situations in which access to the data is required around the clock. However, hot back-up should only be used in phases in which a relatively low number of write accesses are taking place. If, for example, it takes two hours to back up the database and the database is operating at full load, the log files must be dimensioned so that they are large enough to be able to save all changes made during the back-up. Furthermore, the system must be able to complete the postponed transactions after the back-up in addition to the currently pending transactions. Both together can lead to performance bottlenecks. Finally, fuzzy back-up allows changes to be made to the database during its back-up so that an inconsistent state of the database is backed up. The database system is nevertheless

capable of cleaning the inconsistent state with the aid of archive log files that have been written during the back-up. With cold back-up, hot back-up and fuzzy back-up, three different methods are avail- able for the back-up of databases. Network back-up systems provide back-up clients for databases, which means that all three back-up methods can be automated with a network back-up system. According to the principle of keeping systems as simple as possible, cold back-up or hot back-up should be used whenever possible.