Fully Recoverable DDL and dump transaction

In versions of SAP ASE earlier than 15.7, some operations are minimally logged. Since dump transaction is not allowed after a minimally logged operation, this restriction has an effect on recoverability and scalability.

  • Recoverability and operational scalability for very large database (VLDB) installations, where dump database may be very time consuming.

  • Up-to-the-minute recoverability of the database. Even though minimally logged operations are fully recoverable from a server failure, changes after the last successful transaction dump may be lost when data devices are broken, or if the database is corrupted. You cannot, after a minimally logged operation, use dump tran with no_truncate to dump the log, then recover the database using the dumped transaction log.

  • You cannot restore the database to a particular time using dump transaction, and then load tran with until_time.

Beginning with SAP ASE 15.7, you can use dump transaction to fully recover the following operations that in earlier versions of SAP ASE were minimally logged:
  • select into including select into a proxy table

  • alter table commands that require data movement

  • reorg rebuild

Use sp_dboption in in the master database to fully log commands that are, by default, minimally logged.