Skip to Content

DATABASE BACKUP IN SYBASE

Dear all,

I have one doubt. we are taking backup of our sybase database using isql editor with command

isql -Usapsa -SSID -Ppassword -X

use KEP

go

dumpdatabase SID to "/db/....dmp"

go

with this command is everything will backed up or else can i take backup separately for transaction logs tempdb etc..i am new to sybase please

guide me regarding on this. Is it possible to completely restore the database with the above taken database backup using the above commands.

Best regards,

venkat.

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    Posted on Nov 16, 2015 at 04:51 PM

    Full database dump includes the transactions up to the point the database backup runs.

    Dump transaction releases transaction log space used by committed transactions.

    "truncating transaction log" takes place only via "dump transaction" command.

    If dump transaction is not run the available transaction logs space keeps reducing till it gets full and then no further transaction can take place for that database.

    Bottom line

    dump database is full database backup, but does not release transaction log space

    dump transaction is needed to restore transactions after the last full backup up.

    HTH

    Avinash

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 16, 2015 at 05:45 AM

    Hello Sudheer,

    With the above command everything will be backed up. Until unless during this backup duration no transactions are happening.

    Usually transactional backups are useful when backup is old one(may be one old) and current transactions are backed up using transactional backup. In this case you will need both complete backup as well as transactional backup.

    With above explained scenario you will be able to restore complete system.

    Regards

    Anand

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 18, 2015 at 09:29 AM

    Hi Venkata,

    additionally to the others accurate comments: the backuped databae is *only* the one you speficied on the dump command, that is, you will have a dump command done for each database.

    Besides that, the tempdb is not required to be dumped, neither its transaction log. Tempdb is recreated each time you restart your ASE. The "tempdb" name stands for "temporary database", and contains temporary tables internally generated and session-specific. So a dump won't be useful.

    You may check those SAP Notes:
    1585981 - SYB: Ensuring Recoverability for SAP ASE

    1588316 - SYB: Configure automatic database and log backups

    you'll see there that it is recommended to use a dump configurations, then the dump database or dump transaction will use them (and with an easier command syntax 😉 ).

    1801984 - SYB: Automated management of long running transactions this explains how to create a threshold for transaction log to prevent it to be fulfilled.

    Best regards,

    Victoria.

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Venkata,

      You will this mentioned in the notes listed by Victoria. But if you need the archive logs for point in time restore you will have enable this to be backed up.

      use master

      go

      sp_dboption SID, 'trunc. log on chkpt',false

      go

      Then you will need to do your dump configurations with your specific backup locations specific and naming conventions specified i.e.

      Take note to substitute SID with your sid. And to amend stripe dir location as applicable on your system.

      1> sp_config_dump @config_name='SIDDB',

      2> @stripe_dir ='D:\Backup\Database',

      3> @compression = '101',

      4> @verify = 'header'

      5> go

      The change is completed. The option is dynamic and ASE need not be rebooted for

      the change to take effect.

      (return status = 0)

      1> sp_config_dump @config_name='SIDLOG',

      2> @stripe_dir ='B:\Log_Archives',

      3> @compression = '101',

      4> @verify = 'header'

      5> go

      On your production systems also set:

      'full logging for all', 'true'

      'enforce dump tran sequence', 'true'

      Also as mentioned by 1801984 you can look at a document I created, this process triggers archive log backups in the database by using thresholds specificied on page limits.

      http://scn.sap.com/docs/DOC-65767

      In addition to this if you ATM setup and autoexstension you also need to run dumps of the databases as follows:

      <DBSID> database (SAP database)

      master database

      sybsystemprocs database (a restore is required when automatic database expansion has been set

      up)

      Also check recommendation in note especially in relation with the database parameters.

      1611715 - SYB: How to restore an SAP ASE database server

      As victoria mentioned for each of the databases you need backed up you will have to create a seperate job in dbacockpit schedule.

      Johan

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.