Why and When to Use Sybase ASE Transaction Dumps

By: Jeff Garbus, Lyle Fernquist


SAP Sybase Adaptive Server Enterprise (ASE) transaction dumps are incremental backups that will backup just the changes in the transaction log since the last transaction dump. They can be very useful as an alternate to full database dumps.

Why use transaction dumps?

The transaction log is usually much smaller than the rest of the database, so a dump of the transaction log will also be much smaller than a full database dump. Since transaction dumps are smaller, they also take less time to complete, and can also be done more frequently, for example once every 30 minutes or once an hour, rather than once a day or once a week.

Transaction dumps can also be used to provide backup redundancy. For example, if a full database backup fails or goes missing, you could then restore an earlier good database dump, and then restore a complete set of transaction dumps. The restoration process will take longer, of course, but will still be much better than a lost backup.

You can also use transaction dumps to manage the size of the transaction log. Whenever you perform a transaction log dump, it will purge transactions from the log file. This can be useful is your log file fills up frequently.

 

How often should the transaction dump be performed?

The frequency with which you should do transaction dumps will depend, of course on the needs of your organization. How much data can you afford to lose? Can you lose 5 minutes worth of data? How about 2 hours? The answer to this question usually forms a good basis for defining the frequency of the transaction dump execution.

Note that if you can’t afford to lose any data whatsoever in the event of a disruption or disaster, then you must consider other solutions to backup and recovery, such as clustering, replication or some other high-availability architecture options that can help to  ensure minimal data loss.

 

How do I perform a transaction dump and load?

To perform a transaction dump, you use the following syntax:

dump tran<database_name> to <destination>

load tran<database_name> from<backup_source>

In normal operations, you will likely have more transaction dump files than database dump files, and it’s important to manage them efficiently.

You must restore transaction dump files in the order they were created following a full backup. This means that you will need to organize and name the files in some way that allows for fast recognition of the data in the file. We recommend that you use a consistent naming scheme for all dump files that reference in some way the following components so that you can easily tell which dump file(s) you need:

  • Server name
  • Database name
  • Date and Time of the backup
  • Type of dump (db or tran)

The following example shows a transaction dump and load:

dump transaction pubs2 to "/sybase/backups/pubs2.tran.bak"

load transaction pubs2 from  "/Sybase/backups/pubs2.tran.bak"

Transaction Dumps Can Be a Critical Component of a Disaster Recovery Strategy

Transaction dumps can provide an important function in your overall disaster recovery approach. By leveraging the smaller size of transaction dumps, you can provide more frequent backups more effectively. You can also enable a level of redundancy to the full database backup strategy.

 

Comments

Leave a Reply