cancel
Showing results for 
Search instead for 
Did you mean: 

Sybase Transcation log backup issue

Former Member
0 Kudos

Hi All,

I am trying to take backup for Sybase database and transcation log as per below link,

http://scn.sap.com/thread/3245201

I was successfully able to take the backup for database without any issues but when I try to take the transcations log backup I end up with below error,

1> sp_dumptrans SBP

2> go

Msg 4208, Level 16, State 1:

Server 'SBP', Line 1:

DUMP TRANsaction to a dump device is not allowed while the trunc. log on chkpt.

option is enabled.  Disable the option with sp_dboption, then use DUMP DATABASE,

before trying again. (Return status = 4208)

1>

Later after checking the SAP guide for Sybase database we tried to setup below options,

=====================================================================================

Ensure that a complete sequence of transaction log archives is available at all times

 

For your SAP database <DBSID> it is mandatory that you ensure that the archived log sequence

remains unbroken.

To ensure a complete log sequence, set the following database options:

trunc log on chkpt, false

full logging for all, true

enforce dump tran sequence, true

 

You have to set these options after a successful  DUMP DATABASE has been performed and before any

Set these options using the stored procedure sp_dboption to change database options. (Syntax:sp_dboption [dbname, optname, {true | false}] ). You must change to the master database to be able to change options for a database

========================================================================

My question is do I need to login to Master database to set this options because when I try to set the below option

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

I end up with below error,

When I try to login to master database with sa user I could not,I gave the same password which i gave it during the installation

Pelase advise on how to proceed further

Regards

Uday

change is made to the database,

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Uday.

You log into an ASE 'server' (or 'instance') not a database.

'use master'

'go'

Before running the sp_dboption command. 

Former Member
0 Kudos

Hi Chris,

I am not clear with your answer can you explain me in detail what steps I need to execute here

Regards

Uday

Former Member
0 Kudos

use master

go

sp_dboption SBP, 'trunc',true

go

use SBP

go

checkpoint

go

You can then complete a full backup of the database and then transaction log dumps

Former Member
0 Kudos

I am using below command to connect to database

isql -U<Databaseuser> -P<MasterPassword> -S<DBSID>

So which database I need to use in place of -S to connect?

Regards

Uday

Former Member
0 Kudos

-S is the Database Server name (instance in oracle)

Each Database Server has a number of databases.

Type sp_helpdb  - you will see all the databases on the Server.

master is one of these databases

So you need to type the 'use master' before running the sp_dboption command.

Former Member
0 Kudos

Thank you I was able to perform that step successfully and got below out put,

1> use master

2> go

1> sp_dboption SBP, 'trunc',false

2> go

Database option 'trunc. log on chkpt.' turned OFF for database 'SBP'.

Running CHECKPOINT on database 'SBP' for option 'trunc. log on chkpt.' to take

effect.

(return status = 0)

1> checkpoint

2> go

1>

Do I need to perform database backup again or shall I proceed with transaction log backup only now

Regards

Uday

Former Member
0 Kudos

Best practise is to do a full database backup

Former Member
0 Kudos

Hi Jones,

Thanks for all your support,I was able to perform the backup succesfully.

I have taken the DUMP database and Transctional log backup again which went fine without any issues

Regards

Uday

Former Member
0 Kudos

Hi Uday,

   Could you please help me for backup and restore steps in sybase , and totally how many backup and restore methods in sybase ase 15.7?

Former Member
0 Kudos

Hello Selva,

There are two main types of backups that are maintained
in Sybase ASE;

  • Dump Tran and Dump DB
  • Quiesce Backup

Dump Tran and Dump DB Backup

 

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 the 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.

SAP Note 1588316 is a very good point to start in case you are thinking about configuring your SYBASE ASE DB and Transaction Log Backups.

In addition I highly recommend going through the Technical Document stored in the below link;

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

Hope this helps,

Regards

Surajit

Former Member
0 Kudos

Hi,

Just a quick clarification.  Quiesce database does not back up a database but suspends and resumes updates for a list of databases.  Once the database is quiesced, you can issue a dump tran, do disk mirroring, or external copying of the database.

This URL should be helpful:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc31644.1570/html/sag2/CACI...

Lisa

Former Member
0 Kudos

Hi Surajit,

      Thanking you very much for your response.

Former Member
0 Kudos

thank you lisa for your response

0 Kudos

Thanks Uday....

Answers (1)

Answers (1)

Former Member
0 Kudos

Take a look on a Blog that I wrote. I believe it will be helpfull..

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

BR  

Fred