Database systems often perform backup and restore operations to provide a safeguard for protecting critical data stored in databases. Backing up and restoring a database allows for the complete restoration of data over a wide range of potential system problems, including media failure, user errors, or loss of database servers. In addition, backing up and restoring databases is useful for other types of problems not related to the system itself, such as moving or copying a database from one server to another. By backing up a database from one computer and restoring it to another, a copy of a database can be made quickly and easily. Moreover, database systems can be configured such that there are multiple copies of the database operating on differing computers. Such configuration is preferred to have a real-time backup database just in case the primary database becomes corrupt or is non-operational, and also provides a way to offload query processing from a primary computer (the source server) to read-only destination servers.
Backup operations can be performed, for example, as database backups or transaction log backups. Backing up a database involves making a copy of the database that can be used to restore the database if it is lost. Everything in the database is copied, including any needed portions of the transaction log. The transaction log is a serial record of all the modifications that have occurred in a database and includes information as to which transaction performed each modification. The transaction log is used during restore operations to roll forward completed transactions and to roll back or undo uncompleted transactions. In the case where there are multiple copies of the database operating concurrently, the databases are kept synchronized through an operation known as log shipping, that is the process of feeding transaction logs from one database to another on a constant basis. Continually backing up the transaction logs from a source database and then copying and restoring the logs to a destination database keeps the destination database synchronized with the source database.
By contrast to a database backup, backing up a transaction log backs up only the changes that have occurred in the transaction log after a prescribed synchronization point. For database backup operations, this synchronization point might occur after data is copied from the database files, but before copying the portion of the transaction log that is needed to provide a transactionally consistent view of the data that was copied from the database files. For log backup operations, the synchronization point might occur before the log is copied to the backup media, i.e., roughly the start of the log backup operation. Hence, a database backup records the complete state of the data in the database at the time the backup operation is completed and a transaction log backup records the history of the transactions since the last time the log was backed up.
A restore operation involves the application of a backup set to a database. Restoring a database backup returns the database to the state in which it was when the backup was created. Any incomplete transactions in the database backup are rolled back to ensure that the database remains internally consistent. Incomplete transactions include any transactions that were not complete as of the above-described synchronization point. Restoring a transaction log backup reapplies all completed transactions that are in the transaction log to the database. When applying a transaction log backup, the transaction log is traversed, and all transactions in the log are rolled forward. When the end of the transaction log is reached, the database is restored to the state in which it was when the transaction log backup operation began. The restore operation then rolls back all transactions that were incomplete when the backup operation started.
Database backups, transaction log backups, and log shipping are advantageously used together to restore and synchronize multiple copies of a database to the point in time at which a failure occurred. Loss of data due to the failure can be greatly reduced or even eliminated entirely. In certain situations, using database backups, transaction log backups, and log shipping is highly desirable. For example, the practice is advisable in any situation in which any loss of changes after the last database backup is unacceptable. The use of transaction log backups is also indicated when the resources involved in performing only database backups are limited. In addition, transaction log backups are advantageous in cases in which it is desirable to return the database to some point in time before failure.
In addition, it is also advisable to use transaction log backups in cases in which changes to the database are frequent. When a large number of changes occur to the database over a relatively short period of time, the last database backup can become outdated quickly. Because transaction log backups typically use fewer resources than database backups, they can be created more frequently than database backups. Thus, the window of time in which a failure can occur after a backup is reduced, also reducing the amount of data that is potentially lost. Further, by applying transaction log backups, the database can be recovered to a specific point in time before a failure. This point in time need not be immediately before the failure.
To restore a database from both a database backup and one or more transaction log backups, the most recent database backup is typically restored. Next, the transaction log backups that were created after the most recent database backup are applied in the same order in which they were created. Although the use of transaction log backups increases recoverability, creating and applying them is also more complex than using database backups alone. Restoring a database using both database and transaction log backups works only if there is an unbroken sequence of transaction log backups after the last database or differential database backup. In the context of the resynchronization of copies of databases where there is an unbroken sequence of transaction log backups, a similar process is followed to restore the failed copy. Specifically, when one of the databases fails, the transaction log is shipped to the failed copy and a transaction log restore is performed.
Taking transaction log backups and restoring them on a copy of the database is only one type of log shipping. Another common type of log shipping, called real-time log shipping operates in manner where updates being performed and logged (e.g. through log records) on one copy of the database on one server are sent simultaneously to a copy of the database residing on a cooperating server such that the updates represented by sent log records are continuously being executed on the cooperating server. This process results in creating a mirror (identical copy) of the database.
One difficulty encountered in the context of log shipping is the possibility of database corruption in certain situations known as divergence in transaction histories. For example, given an environment wherein a database having two copies, a primary database D1 running on server S1, and a secondary copy of the database D1′ running on server S2, a divergence in transaction history is a condition in which D1 fails for a period of time during which D1′ remains operational, and wherein D1 is brought back online. As log shipping functions are terminated during the downtime of D1, this results in D1 and D1′ becoming unsynchronized. FIG. 3 describes a divergence in transaction history in more detail in context to the example provided. As is shown, and indicated by block numbered 1, S1 and S2 are active. S1 generates log records labeled L1-L10 (as described by log sequence numbers—LSN1-10) and sends them to S2 where they are saved on disk and re-performed to keep D1′ up-to-date with D1. A log sequence number (LSN) is an ID of a log record. LSNs are assigned to log records as the records are generated and each new log record is assigned an LSN that is greater that the previous LSN. At LSN=L10, S2 is shutdown or unavailable due to network failure. S1 continues to generate log records L11-L20 that affect database pages P1 and P2 (a database page is a unit of disk storage holding data records). In operation, Pages can be allocated (i.e. in use) or de-allocated (not in use). Pages also contain a pageLSN (page log sequence number) recording the ID of the last log record for an update to the page.
In the contemplated example it is then assumed at this point S1 becomes unavailable. S2 is brought back online to take over for S11 despite the fact that S2 does not have log records L11-L20. In this situation L10 is considered the point of divergence in transaction history. S2 will now generate a new set of log records labeled L11-30 that touch database pages P2 and P3. S1 and S2 are now divergent and require resynchronization.
The goal of resynchronization is to bring S1 and its database D1 back online so that they can become the mirror of D1′ currently running on S2. To accomplish this feat, however, involves, removing all changes recorded on D1-S1 in the log records L11-L20. In practice such goal is accomplished by removing L11-L20 of S1 from the log and removing L11-L20 from the affected database pages (e.g.: P1 and P2) and restoring P1 and P2 to values they had at point L10 in the log or at some point after L10 from S2-D1′. From there, the log records L11-L30 from S2 can be copied to S1 and then replayed to bring S1-D1 up to date with S2-D1′.
Current practices require the backup of all of D1′ and restore it to S1. Such practice is extremely inefficient for large databases and can be disruptive to S1 and S2's processing if these servers are geographically disparate. Even with current practices, conventional database systems cannot reliably and efficiently rectify the resynchronization of database copies upon a divergence in transaction history condition as in most cases manual data operations are required. From the foregoing it is appreciated that there exists a need to overcome the prior art.