Question Re: DATABASE BACKUP IN SYBASE in Technology Q&A

DATABASE BACKUP IN SYBASE

former_member230979 — Mon, 16 Nov 2015 05:29:04 GMT

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

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

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.

Re: DATABASE BACKUP IN SYBASE

Former Member — Mon, 16 Nov 2015 05:45:52 GMT

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

Re: DATABASE BACKUP IN SYBASE

former_member89972 — Mon, 16 Nov 2015 16:51:22 GMT

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

Re: DATABASE BACKUP IN SYBASE

victoria_normand — Wed, 18 Nov 2015 09:29:41 GMT

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.

Re: DATABASE BACKUP IN SYBASE

Johan_sapbasis — Wed, 18 Nov 2015 09:58:03 GMT

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

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

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