on 11-26-2012 3:54 PM
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,
Uday.
You log into an ASE 'server' (or 'instance') not a database.
'use master'
'go'
Before running the sp_dboption command.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
Hello Selva,
There are two main types of backups that are maintained
in Sybase ASE;
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
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:
Lisa
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.