?
Solved

Sybase Backup

Posted on 2001-09-03
4
Medium Priority
?
327 Views
Last Modified: 2008-03-04
We have recently had a sybase (11.9.2 on Solaris 2.6) based application installed - as most of our in house knowledge is Oracle based we are on a steep learning curve!

We have scripted a back up of each of the databases in turn -this is executed by cron daily.

We are unable to backup the transaction logs because the truncate log on checkpoint is set on - this hardly matters as I understand a checkpoint is performed before a backup so all data from the transactions logs would be flushed to the databases before my 2:00am backup.

However what we want is point in time recovery - if the database performs a checkpoint between the 2:00 am backups (which I assume it will) the transaction logs will truncate thus rendering any data written to the databases unrecoverable in the event of disk failure to the datbase disks and unrecoverable from the transaction logs because they have been truncated.

It strikes me that it would be better not to truncate the logs on checkpoint.  However, this is not how the databases have been setup.  

1) Can we change this option  to stop the transacion logs truncating - i.e. is it likely to have an impact on the application (I appreciate that this is a question for the application designers - but I am really after general principles)?

2) I am used to Oracle where you can have archive logs being written to multiple destinations for security is there any equivalent mechanism Sybase - or is mirroring the transaction logs the only option?


Finally the Sybase documentation makes great play of the importance of running dbcc before backups and for various other integrity checking tasks - dbcc is not installed on either the development box or on the production box - does dbcc come as part of the Adaptive Server release?


Many thanks in advance
Peter Wickett
0
Comment
Question by:pwickett
  • 3
4 Comments
 
LVL 3

Expert Comment

by:mathavra
ID: 6454341
1) Can we change this option  to stop the transacion logs truncating - i.e. is it likely to have an
impact on the application (I appreciate that this is a question for the application designers - but
I am really after general principles)?

Yes. You can change the option. But, you may have to increase the transaction log as per your need. It should not affect the application at all as long as you time the CRON job to dump the log OR set the threshold to reasonable levels which depends on the log size and transaction activity of your database. You can do this by doing one of the following:

One method:

Step 1) Assign a area (file system) in the OS for the transaction logs to be backed up.

Step 2) Create the log threshold procedure and define it at various free page limits.

Step 3) Inside the procedure, dump the transaction log to the filesystem created in the first step.


Second method:

Step 1) Write a shell script which logs on to the Sybase and performs the "transaction dumps" every 1 hour or so.


Q2) I am used to Oracle where you can have archive logs being written to multiple destinations for security
is there any equivalent mechanism Sybase - or is mirroring the transaction logs the only option?

I beleive there is no such option in Sybase. Mirroring is the only option. OR, via the first question's answer, you can copy to multiple locations.
0
 
LVL 3

Accepted Solution

by:
mathavra earned 150 total points
ID: 6454362
Didn't read completely. About DBCC:

DBCC comes as part of Sybase ASE. Probably the manual is talking about "DBCC Database" which is needed to run DBCC Checkcatalog command which was available from Sybase 11.9.2. This command can be used to for large databases. It can be stopped and started anytime from where it left off. The original DBCC commands such as dbcc checkalloc, checkdb, tablealloc, indexalloc, etc cannot be run in that manner. ALso, these do not need the DBCC database to run it.
0
 
LVL 3

Expert Comment

by:mathavra
ID: 6454375
Just one more thing, RUNNING DBCC before every backup is not a feasible option just because of the timing. We usually run this ONCE a week in our environment.

OR, you can use a backup tool such as SQL Backtrack/DB Verify from BMC Corporation which does the DBCC on the backed up database (not in Sybase). But, I heard they may be dropping the DB Verifier product completely.
0
 

Author Comment

by:pwickett
ID: 6456007
Many thanks Mathavra
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The deadly train derailment that occurred recently in DuPont, Washington, raises a lot of questions. It was a new route, the first trip tested with passengers, and the train was travelling at 50 mph over the zone’s speed limit. Could IoT play a role…
Ready to kick start your career in 2018? Add app developer skills to your resume. January’s Course of the Month features Android App Development training with hands-on learning.  Read on to learn why these skills are important.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question