Recovering a Database to a Specified Time

You can use the until_time option for most databases that can be loaded or dumped. until_time does not apply to databases such as master, in which the data and logs are on the same device. Also, you cannot use it on any database that has had a truncated log since the last dump database, such as tempdb.

The until_time option is useful for the following reasons:

It enables you to have a database consistent to a particular time. For example, in an environment with a decision-support system (DSS) database and an online transaction processing (OLTP) database, the system administrator can roll the DSS database to an earlier specified time to compare data between the earlier version and the current version.
If a user inadvertently destroys data, such as dropping an important table, you can use the until_time option to back out the errant command by rolling forward the database to a point just before the data was destroyed.

To effectively use the until_time option after data has been destroyed, you must know the exact time the error took place. You can find out by executing a select getdate () command immediately after the error. For a more precise time using milliseconds, use the convert function, for example:

select convert (char (26), getdate (), 109)

--------------------------
Feb 26 1997 12:45:59:650PM

After you load a transaction log using until_time, the SAP ASE server 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. Dump the database before you dump another transaction log.

Only transactions that committed before the specified time are saved to the database. However, in some cases, transactions committed shortly after the until_time specification are applied to the database data. This may occur when several transactions are committing at the same time. The ordering of transactions may not be written to the transaction log in time-ordered sequence. In this case, the transactions that are out of time sequence are reflected in the data that has been recovered. The time should be less than a second.

For more information on recovering a database to a specified time, see the System Administration Guide.

Alternatively, you can use the sybrestore utility to restore a database to a point in time, within the range of time during which the database is backed up in the dump history files. See sybrestore in the Utility Guide.