Generating an optimized restore plan

A restore advisor may generate and apply a recovery plan to restore a database to a specified point in time. A restore/recovery scenario may be specified; resources, and backups available may be specified and one or more optimized restore plans may be generated. One or more alternate plans may be requested. The plans may be applied, verified or saved.

FIELD OF THE INVENTION

The invention relates to restoring a database after a system failure and in particular to generating an optimized restore plan.

BACKGROUND OF THE INVENTION

Restoring a database after a system failure or a data loss requires a plan to restore the database to the pre-failure state. For large databases or databases with high volumes of transactions, building the restore plan is a complex and tedious task. It typically requires identification of the backups of the database and determination of the correct order of application of the backups to bring the database to the specific point in time prior to the failure.

Depending on the specific conditions associated with the system failure, system resources available and backup media available, there may be several possible restore plans. Some plans may be less efficient than others. Some plans may be impossible to implement because of the unavailability of backup media or system resources. Frequently there is a sense of urgency associated with restoring the system to operation, so a restore plan must be developed quickly.

It would be helpful if there were a way to generate an optimized restore plan quickly and easily to aid in disaster recovery.

In addition to dealing with system crashes, it may be desirable to verify that a restore plan is available, should a crash occur. For example, a business may want to verify that, should a database crash, it could be recovered, and recovered in the shortest period of time possible.

It would be helpful if there were a way to generate an optimized restore plan quickly and easily so that the availability of backup media and the presence of an optimized restore plan could be verified.

It may be desirable to verify a duplicate database against a production database. For example, suppose someone has deleted data but the time at which the data was deleted is unknown. It may be helpful to be able to create and apply a restore plan to an intermediate point in time so that the two databases can be compared to determine when the data was deleted. It may be helpful to be able to create a copy of a database at a particular point in time, perhaps to determine the time at which a data loss occurred, or for other reasons such as, for example, for the purpose of an audit. An optimized restore plan would be helpful to create the duplicate database in the most efficient manner to a specified point in time.

SUMMARY OF THE INVENTION

A restore advisor may generate and apply a recovery plan to restore a database to a specified point in time. A restore/recovery scenario may be specified; resources, and backups available may be specified and one or more optimized restore plans may be generated. One or more alternate plans may be requested. The plans may be applied, verified or saved.

DETAILED DESCRIPTION OF ILLUSTRATIVE EMBODIMENTS

Overview

Suppose that at 2 pm on a Tuesday, just when the maximum number of users is relying on a computer system, the database crashes. Suppose the last full backup (a copy of the entire database) was last taken at time t0, to tape and the time of the crash is t0+n. To restore the database to a time just before the crash will require restoring the last full backup and applying the transaction log to a point just before the crash.

Now suppose three transaction log backups were taken at times t1, t2and t3to tape. Suppose further that a differential backup, (a copy of changes to databases since last full backup), was taken at t4to CD and a fourth log backup was taken at t5to CD. It is now t5+n. What is the best (fastest) way to get the system back up and running? One way is to apply the last full backup (the backup at0) and then apply the four log backups in order (t1, t2, t3and t5) and then apply the log to a point just before the crash. Another way is to apply the last full backup (the backup at t0) and then apply the differential backup (at time t4), the transaction log backup at t5and the log to a point just before the crash. What if the tape for the second log backup is defective? What if the CD on which the differential backup is stored is in Sacramento and the system is in Baltimore?

In accordance with some embodiments of the invention, an optimized restore plan to restore a database to a specified point in time is determined. In response to received input, an alternate plan may be determined. In some embodiments of the invention, the alternate plan is the next best plan, in terms of time to restoration of the database.

Exemplary Computing Environment

FIG. 1and the following discussion are intended to provide a brief general description of a suitable computing environment in which the invention may be implemented. It should be understood, however, that handheld, portable, and other computing devices of all kinds are contemplated for use in connection with the present invention. While a general purpose computer is described below, this is but one example, and the present invention requires only a thin client having network server interoperability and interaction. Thus, the present invention may be implemented in an environment of networked hosted services in which very little or minimal client resources are implicated, e.g., a networked environment in which the client device serves merely as a browser or interface to the World Wide Web.

Although not required, the invention can be implemented via an application programming interface (API), for use by a developer, and/or included within the network browsing software which will be described in the general context of computer-executable instructions, such as program modules, being executed by one or more computers, such as client workstations, servers, or other devices. Generally, program modules include routines, programs, objects, components, data structures and the like that perform particular tasks or implement particular abstract data types. Typically, the functionality of the program modules may be combined or distributed as desired in various embodiments. Moreover, those skilled in the art will appreciate that the invention may be practiced with other computer system configurations. Other well known computing systems, environments, and/or configurations that may be suitable for use with the invention include, but are not limited to, personal computers (PCs), automated teller machines, server computers, hand-held or laptop devices, multi-processor systems, microprocessor-based systems, programmable consumer electronics, network PCs, minicomputers, mainframe computers, and the like. The invention may also be practiced in distributed computing environments where tasks are performed by remote processing devices that are linked through a communications network or other data transmission medium. In a distributed computing environment, program modules may be located in both local and remote computer storage media including memory storage devices.

A monitor191or other type of display device is also connected to the system bus121via an interface, such as a video interface190. A graphics interface182, such as Northbridge, may also be connected to the system bus121. Northbridge is a chipset that communicates with the CPU, or host processing unit120, and assumes responsibility for accelerated graphics port (AGP) communications. One or more graphics processing units (GPUs)184may communicate with graphics interface182. In this regard, GPUs184generally include on-chip memory storage, such as register storage and GPUs184communicate with a video memory186. GPUs184, however, are but one example of a coprocessor and thus a variety of coprocessing devices may be included in computer110. A monitor191or other type of display device is also connected to the system bus121via an interface, such as a video interface190, which may in turn communicate with video memory186. In addition to monitor191, computers may also include other peripheral output devices such as speakers197and printer196, which may be connected through an output peripheral interface195.

One of ordinary skill in the art can appreciate that a computer110or other client device can be deployed as part of a computer network. In this regard, the present invention pertains to any computer system having any number of memory or storage units, and any number of applications and processes occurring across any number of storage units or volumes. The present invention may apply to an environment with server computers and client computers deployed in a network environment, having remote or local storage. The present invention may also apply to a standalone computing device, having programming language functionality, interpretation and execution capabilities.

Generating an Optimized Restore Plan

FIG. 2is a block diagram of an exemplary system for generating an optimized restore plan in accordance with some embodiments of the invention. Computers202and218may be a computer such as computer110described above with respect toFIG. 1. A database204may reside on computer202. Additionally, a database such as metadata database206may reside on computer202. Database204may represent a source database for which backups are taken.

FIG. 3is an exemplary timeline of backups that may be made of a database such as database204. A database backup may be a full backup, a differential backup or a transaction log backup. As used herein, a full backup creates a copy of the entire database and may include both the present state of the database and a log of transactions that were applied to the database up to that point, a transaction log backup makes a copy of transactions from a start time to an end time and a differential backup makes a copy of transactions applied to the database from a period of time beginning at the last full backup to the time at which the differential backup is taken.

Referring now toFIG. 3, at t0302, a full backup (e.g., Backup1304) may be made. Backup1304may contain a copy of the state of the database as it existed at t0. At t1306a log backup (e.g., Backup2308) may be made. Backup2308may contain transactions applied to the database from time0302to time t1306. At t2310a log backup (e.g., Backup3312) may be made. Backup3312may contain a log of transactions applied to the database from time t1306to time t2310. At t3314a log backup (e.g., Backup4316) may be made. Backup4316may contain a log of transactions applied to the database from time t2310to time t3314. At t4318a differential backup (e.g., Backup5320) may be made. Backup5320may contain changes to the database from time t0302(the time of the last full backup) to time t4318. At t5322a log backup (e.g., Backup6324) may be made. Backup6324may contain a log of transactions applied to the database from time t3314to time t5322.

In some embodiments of the invention, a transaction log operates logically as a serial string of log records. Each log record includes the transaction and is identified by a log sequence number, LSN. An LSN may be a sequential number associated with each transaction, such that each new log record written to the logical end of the log is associated with an LSN than is higher than the LSN of the record before it. Thus, a transaction occurring earlier in time will have an LSN that is smaller than a transaction that occurs later in time. Similarly, if a first transaction has a first LSN, the next transaction will have an LSN that is higher than the LSN of the previous transaction.

When a backup is performed on database204, information associated with the backup may be stored in source metadata database206, as well as on the media storing the backup, here represented by storage media226,228, etc. The information that may be stored in source metadata database206and/or on the storage media226,228, etc. may include an indicator of the type of storage media (tape, CD, disk, DVD, etc.), the volume identifier of the storage media, a beginning log sequence number (LSN) for the first transaction stored on the storage media, an ending LSN for the last transaction on the storage media, the type of backup performed, the drive or device on which the backup was performed, the date of the backup, etc.

Hence, forFIG. 3, information concerning Backup1304, Backup2308, Backup3312, Backup4316, Backup5320, and Backup6324may be stored in source metadata database206, and/or on the storage media. The information that may be stored in source metadata database206may include the volume identifier of the storage media, a beginning log sequence number (LSN) for the first transaction on the storage media, an ending LSN for the last transaction on the storage media, etc.

A restore advisor such as exemplary restore advisor214may reside on a client208as shown inFIG. 2. It will be appreciated that client208may reside on computer202,218or on another computer. Similarly restore advisor214may reside on computer202,218or on another computer and may be run against source database204to create target database220and target metadata database222from any location. The restore advisor214may be embedded within a database management tool and may be implemented as a pluggable component.

When the restore advisor214is invoked, an optimized restore plan such as restore plan216may be generated. An optimized restore plan may identify which backups must be applied in what order to recreate a database in the most efficient way possible given a set of constraints. In some embodiments one or more restore plans216, etc. may be generated. The restore plans216, etc. may be stored in source metadata database206, target metadata database222or in a separate bank224. Information about backups used by the restore advisor214to produce the restore plan(s) may be retrieved from one or more of: a source metadata database206, a target metadata database222, backup media226,228, etc., bank224or from user input.

A method for creating an exemplary optimized restore plan is illustrated inFIG. 4. At step402in response to the notification of a database system failure or data loss or notification of a desire to verify that a valid restore plan is available, or notification that a duplicate database is to be created as the database existed at a particular point in time, an optimized restore plan tool may be invoked and the tool may be launched.

A series of scenarios categorized by various events may be displayed. The events may include system failure, data loss, maintenance tasks, etc. For example, any or all of the following scenarios may be displayed for selection: restore or recover an existing database, restore or recover an existing database to a specific point in time, restore a subset of an existing database while the database remains online, restore a subset of a database to another location for investigation, restore damaged data pages of a database, create a new database from an existing database, move a database to a new location, restore a subset of a database to extract a portion of the data and so on. A suitable scenario may be selected.

At step404the user may be prompted for identification and/or location of a source database, a target name and/or destination for the recovery process, a source and/or target metadata database that stores the backup history information, available system resources, available backups and backup history information and a point in time to which the database is to be recovered (e.g., a target restore time, such as, for example, “restore to most recent possible”, “restore to transaction marked”, “restore to a specific date/time”. In some embodiments of the invention, instead of generating a restore plan to restore a database to a specified point in time, the restore plans is generated to restore a database to a particular checkpoint. A checkpoint, as described above, is a particular transaction that has been labeled. Alternatively, this information may be available from a source metadata database as described above and thus the identification of the source metadata database may be received.

The user may also specify the target date/time of the recovery and the state of the database after it is recovered (e.g., “with no recovery” or “with recovery”. “With no recovery” or “with recovery” refers to the way in which incomplete transactions are handled. For example, if additional transaction logs will be applied, it may be desirable to specify restoring the database “with no recovery”, meaning that incomplete transactions are not backed out. If further transactions logs will not be applied, it may be desirable to specify restoring the database “with recovery”, meaning that incomplete transactions are backed out.)

At step406an appropriate plan of recovery for the database may be generated. In some embodiments of the invention, a bank of stored scenarios may be accessed to determine if a restore plan has already been generated for the specified scenario and database, etc. A plan may comprise one or more steps or actions to be taken to restore the database, in view of the constraints received in step404. In some embodiments of the invention, the details of each plan step may be presented as a list of steps. Upon selection of one of the steps, details may be viewed and the backup device specified for the step may be changed.

In some embodiments of the invention, an alternate plan may be requested. For example, referring again toFIG. 3, suppose an optimized restore plan indicates that Backup1304and Backup5320are to be applied, in that order, but Backup5320has a read error and is unusable. An alternate plan may be requested. In response to the request for the alternate plan, a second restore plan may be generated, for example, specifying that Backup1304, Backup2308, Backup3312and Backup4316should be applied, in that order.

At step408the plan may be executed, saved or verified (a “Dry Run”) performed. In some embodiments of the invention, the restore plan is saved as a script file. The restore plan(s) may be saved in the source metadata database, a target metadata database or in a restore plan bank or datastore.

At step410the information acquired from step404may be stored in a source metadata database (such as source metadata database206) a target metadata database (such as target metadata database222). This information may be used to enable the restore advisor to “learn” from previous restore plans.