How Does Point-In-Time Recovery Work

It can happen to anyone. Somehow your computer crashes and files show up as corrupted. If you were smart and made a backup of the database, then it’s possible to restore the computer to better times using a point-in-time recovery.

What is point-in-time recovery? In the big picture, it is a system in which a set of data can be restored to or recovered from a particular time in the past – sort of like a time machine for computers. This also works for a particular setting that needs to be reinserted. Windows XP and Vista both feature these capabilities as well as Time Machine for Mac OS X. The recovery is made to a specified date and time in which to restore the data base.

Point-in-time recovery works in certain systems by recording system file changes in order to restore to the previous points. These restore points can be done periodically, even daily by the program, or you can set up their own points. If a system failure occurs, then you can restore in either Safe Mode or Normal Mode, and recover system function. The system also records these points any time a new program is installed or a substantial change is made.

Some servers, such as Oracle perform crash recovery and instant recoveries automatically after a failure. There are two options: rolling forward the backup to a more current time by applying online re-do records or rolling back, in which changes are made to their original state. The Oracle system is very similar to the point in recovery systems mentioned above.

Most of the programs available are very simple to use and only prompt you for the date/time you need restored to. Sometimes it is necessary to perform several point-in-time recoveries to determine the exact time you need if you were not aware when the original corruption occurred, however this can be time consuming. The SQL Server provides instructions if you need to only restore certain databases. With SQL, there are three phases in the recovery and restoration that include data copy, redo (roll forward) and undo (roll back) phases. The data copy phase creates a copy of all the information backed up by the database previously and initialized the contents to be restored. The second phase, redo, takes the data and returns it to its original state at the recovery point or the date you specify. Lastly, the undo phase identifies uncommitted transactions which have become inconsistent with the recovered data. Once a consistency is established, the database is brought back to life.

Even with a point-in-time recovery system, it is still important to make a backup of your files regularly. The system restore is ideally meant for when the computer is corrupted or suffers some other catastrophe. With a backup, you still have important documents ready at hand. Most PC’s come with a disk providing a system restore program that can be installed at any time. With these disks it is important to remember that this process completely overwrites the files on your disk with any new files, rendering your old copy worthless. Having a previous backup will ensure that you still have the files you need.