Loading a Transaction Log to a Point in Time

You can recover a database up to a specified point in time in its transaction log.

To do so, use the until_time option of load transaction. This is useful if, for example, a user inadvertently drops an important table; you can use until_time to recover the changes made to the database containing the table up to a time just before the table was dropped.

To use until_time effectively after data has been destroyed, you must know the exact time the error occurred. You can find this by issuing a select getdate at the time of the error.

  1. For example, suppose a user accidentally drops an important table, and then a few minutes later you get the current time in milliseconds:
    select convert(char(26), getdate(), 109)
    Mar 26 2007 12:45:59:650PM
  2. After dumping the transaction log containing the error and loading the most recent database dump, load the transaction logs that were created after the database was last dumped. Then, load the transaction log containing the error by using until_time; in this example, about 10 minutes earlier:
    load transaction employees_db
    from "/dev/nrmt5"
    with until_time = "Mar 26 2007 12:35:59: 650PM"

    After you load a transaction log using until_time, SAP ASE restarts the database’s log sequence. This means that until you dump the database again, you cannot load subsequent transaction logs after the load transaction using until_time. You must dump the database before you can dump another transaction log.