SQL Server

Database Recovery Methods Part 1


In this article a number of methods which can be castoff for database recovery from failures are discussed. The various reasons of database failures are already to known to us, like system crashes in addition to transaction faults. The very first section of the article will be outlining the usual recovery techniques as well as the classification of recovery algorithms will also be discussed, next it will be describing a number of recovery ideas, including write ahead logging, in – place vs. shadow updates, as well as the procedure of rolling back (undoing) the effect of an unfinished or else unsuccessful transaction. The second part of the article will be focusing on the recovery methods based on deferred update; it is well-known as the NO – UNDO or REDO method also. The next part of the article will be discussing the recovery techniques based on immediate update; which is consist of the UNDO or REDO as well as UNDO NO-REDO algorithms. The procedures which are known as shadowing or shadow paging, which can be considered as a NO-UNDO or NO-REDO algorithm, are discussed in the next section of this article. In conclusion, the methods for recovery from terrible failure are discussed. The emphasis is on theoretically defining a number of various methods to recovery.

Recovery Theories

Recovery Framework plus Classification of Recovery Algorithms

Recovery from the transaction failures typically means that the database is restored to the most latest reliable state just before the time of failure. To accomplish this, the system should preserve data about the alterations which were applied to the data items by means of the numerous transactions.

This data is normally reserved in the system log. A usual approach for recovery may be brief casually as follows:

1. If there is general destruction to an extensive part of the database due to disastrous failure, like a disk crash, the recovery technique restores a past copy of the database which was backed up to archival storage (typically tape) as well as recreates a more present state by means of reapplying or rebuilding the processes of committed transactions from the backed up log, till the time of failure.

2. At the time when the database is not materially damaged however has become unreliable due to non-catastrophic let-downs of kinds as it was discussed in the preceding articles, the plan is to converse any alterations which has caused the discrepancy by means of undoing a number of actions. It might also be essential to rebuild a number of operations so as to restore the database in a reliable phase. In this circumstance an individual do not require a comprehensive archival copy of the database. Relatively, the entries held in reserve in the online system log are referred at the time of recovery.

Theoretically, an individual can discriminate two (2) main methods for recovering non-catastrophic transaction failures: (1) Delayed update in addition to (2) Instant update. The delayed update methods do not physically update the database on disk till the particular transaction reaches its commit point; at that point the updates are logged in the database. Before reaching commit, every transaction updates are logged in the local transaction workspace or commonly known buffers. In the course of commit, the updates are first (1st) logged determinedly in the log plus then written to the database. When a transaction fails before the attainment of its commit point, it will not have any changes logged to the database in any way, thus UNDO is not required. It might be essential to REBUILD the consequence of the processes of a committed transaction from the log, as their effect may not yet have been logged in the database. Therefore, delayed update is also known as REDO algorithm.

In the instant update methods, the database may be updated by a number of operations of a transaction before the transaction achieves its commit point. But, these actions are normally logged in the log on disk by means of force writing before they are applied to the database which is still making recovery. When a transaction failure happens even after recording a number of changes in the database however before reaching to the commit point, the result of this procedure on the database should be undone; which means the transaction should be rolled back. In the common situation of instant update, both undo as well as redo may possibly be essential at the time of recovery. This method is known as the UNDO or REDO algorithm, necessitates both processes, and is castoff most frequently in practice. A deviation of the algorithm where every updates are logged in the database in prior a transaction commits needs to undo only, hence it is known as the UNDO or NO-REDO algorithm.

In the upcoming part we will be focusing on the Buffering of Disk Blocks