Skip to Content
author's profile photo Former Member
Former Member

Backup strategy for Sybase ASE

Hello all!

I am new to administration Sybase ASE.

Help me please to find a document that describes the backup strategy for the database Sybase.

I found Note 1588316 - SYB Configure automatic database and log backups.

But I don't understand where is offline backup and where is online backup?

I was working with a database Oracle, there exists a offline backup, online backup, backup redologs.

How to make a backup strategy for database Sybase?

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • Best Answer
    Posted on May 14, 2013 at 04:20 PM

    Is your ASE system being used for SAP applications, or for other uses? If it isn't being used for SAP applications, the "SAP Sybase ASE for Custom Applications" group is the place for general ASE questions. http://scn.sap.com/community/sybase-ase-custom-applications SAP has specific methods they want you to use with their applications, see the following notes:

    • 1585981 SYB: Ensuring Recoverability for Sybase ASE
    • 1588316 SYB: Configure automatic database and log backups
    • 1611715 SYB: How to restore a Sybase ASE database server (Windows)
    • 1618817 SYB: How to restore a Sybase ASE database server (UNIX)

    Speaking from a generic ASE perspective, there are two main methods for backup:

    1) DUMP DATABASE [and DUMP TRAN]

    2) QUIESCE DATABASE [or ASE shutdown] with direct device copy.

    Both are documented in the ASE System Administration Guide, particularly Chapter 12.

    http://infocenter.sybase.com/help/index.jsp?docset=/com.sybase.infocenter.help.ase.15.7.2/title.htm&docSetID=1884

    One of the first things you should think about when deciding on a backup strategy for ASE is how much data loss you can accept in the case of a disaster, as well as how much down time you can accept. I expect that SAP has already made these decisions for their own applications. There are actually factors beyond the dump strategy that can factor into this planning – for instance disk mirroring to guard against a hard drive freezing up and bursting into flame, the “high availability” option that uses two hosts sharing a disk system to guard against failure of a host, etc.

    An ASE server has multiple databases on it, each database can be on multiple devices (and can share devices). Each database has a transaction log, which is itself a table within the database. At any given time, the contents of tables written to disk may contain a mix of committed and uncommitted data. ASE uses a write-ahead transaction log, meaning that when a client issues a commit transaction all the log records for that transaction are written to disk before success is returned to the client. However, the changes to data pages are not flushed to disk by the commit command (though they are flushed by other events, such as checkpoints or aging out of cache.) If the server goes down abruptly, a recovery process applies the transaction log records to the rest of the data stored on disk to return it to a transactionally consistent state.

    As a general overview, there are three main methods for doing backups (in order of common usage): the DUMP and LOAD commands, the QUIESCE DATABASE command coupled with disk copy, and Replication.

    The DUMP DATABASE command copies the entire database to an archive device. It does not prune (truncate) the transaction log (which is itself a table in the database). DUMP DATABASE is typically run on a daily or weekly basis. If your loss tolerance is 24 hours, then a single DUMP DATABASE every 24 hours is a reasonable choice for backup strategy and the transaction log can be kept pruned by either setting the database option “truncate log on checkpoint” or by regular use of the DUMP TRANSACTION command with the TRUNCATE_ONLY option.

    If your loss tolerance is less than 24 hours, down to about 15 minutes, then you would add in transaction dumps. The DUMP TRANSACTION command copies the contents of the transaction log to an archive device and truncates the log from the beginning to the start of the oldest open transaction. DUMP TRAN can be run off a cron (or Job Scheduler) job, or triggered automatically when the log grows to a certain size using thresholds. Note that the more frequently transaction log dumps are taken, the more transaction log dump files there are to LOAD, and the longer it will take to load them all. These dumps can be kept to be reloaded on the host in case of a problem, or used to maintain a "warm" backupserver by promptly loading the dumps as they are taken onto a second host; in case of a failure the warm system can be brought online relatively quickly by starting the recovery process by issuing the ONLINE DATABASE command (the time to load the dumps is avoided, but the last phase of recovery still needs to be done).

    DUMPs can be made either natively (directly to file or tape device) or to an interface written using the Backupserver API. DUMPS made to a file look like “dump database mydatabase to ‘/work/mydatabase.dmp’”, DUMPS made to an API look like “dump database mydatabase to ‘myapimodule::arbitrary_api_parameters’”. Sybase provides one API module with ASE, which is the compression module, which takes a compression level and file name as a parameter. “dump database mydatabse to ‘compress::4::/work/mydatabase.cmp.dmp’”. There are other third party vendors such BMC’s SQL Backtrack and various automated tape library manufacturers that create and provide API modules. ASE DUMPs also now have a native compression parameter, there are two methods for dump compression because the compression API was developed first and is still supported.

    The second major backup method is the use of QUIESCE DATABASE coupled with a device copy of the underlying devices of the database, usually using a very fast copying method. QUIESCE prevents ASE from performing any writes to the devices until it is released; this allows a consistent “snapshot” of multiple devices to be copied. Activity can continue on the server in this state until the data caches fill completely with dirty buffers – although any process that has to flush log records will be blocked at that point until the log records can be written. This approach is typically used for very large databases where the time taken to dump or load the full database becomes very long with the traditional DUMP and LOAD commands. This method can also be combined with DUMP TRANSACTION.

    A third method, using Replication, is used when the tolerance for data loss or down time is very low. The method maintains a hot backup system on another host by constantly shipping and applying transaction log records to the hot backup system using a Replication Server. If the primary fails, the warm backup can quickly be substituted.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 15, 2013 at 02:46 AM

    Hi Fomin,

    In extension to above details you may refer options in link below

    http://www.tldp.org/HOWTO/Sybase-ASA-HOWTO/backup.html

    Hope this helps.

    Regards,

    Deepak Kori

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Deepak,

      Be careful not to get ASA and ASE confused.

      They share the same first 2 parts of the name but are very different products :-)

      Adaptive Server Anywhere (AKA SQL Anywhere), now primarily used for many mobile solutions (IQ also part based on it)

      Adaptive Server Enterprise (larger scale database used by SAP Business Suite and of course many, many of the legacy Sybase customers)

      Cheers,

      Simon

  • author's profile photo Former Member
    Former Member
    Posted on Sep 04, 2013 at 03:54 PM

    Take a look on a Blog that I wrote. I believe it will be helpfull.. Is about backup strategy on SYBASE.

    http://scn.sap.com/community/sybase-ase/blog/2013/05/31/backup-challenges-on-sybase

    BR

    Fred

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 16, 2015 at 07:04 AM

    Hi there,

    I'd just like to thank you for such a fantastic answer. I know I'm a little late to the party, but that is a very well phrased and informative answer.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.