Other Times to Back Up a Database

In addition to routine dumps, dump a database each time you upgrade a user database, create a new index, perform an unlogged operation, or run the dump transaction with no_log or dump transaction with truncate_only command.

• Dumping a user database after upgrading – after you upgrade a user database to the current version of SAP ASE, dump the newly upgraded database to create a dump that is compatible with the current release. A dump database must occur on upgraded user databases before a dump transaction is permitted.
• Dumping a database after creating an index – when you add an index to a table, create index is recorded in the transaction log. As it fills the index pages with information, however, SAP ASE does not log the changes. If your database device fails after you create an index, load transaction may take as long to reconstruct the index as create index took to build it. To avoid lengthy delays, dump each database immediately after creating an index on one of its tables.
• Dumping a database after unlogged operations – SAP ASE writes the data for the following commands directly to disk, adding no entries (or, in the case of bcp, minimal entries) in the transaction log:

  • Unlogged writetext

  • select into on a permanent table

  • Fast bulk copy (bcp) into a table with no triggers or indexes

    You cannot recover any changes made to the database after issuing one of these commands. To ensure that these commands are recoverable, issue a dump database command immediately before executing any of these commands

• Dumping a database when the log has been truncated – dump transaction with truncate_only and dump transaction with no_log remove transactions from the log without making a backup copy.

  To ensure recoverability, dump the database each time you run either command because of lack of disk space. You cannot copy the transaction log until you have done so.

  If the trunc log on chkpt database option is set to true, and the transaction log contains 50 rows or more, SAP ASE truncates the log when an automatic checkpoint occurs. If this happens, dump the entire database—not the transaction log—to ensure recoverability.