Solved

Sybase Backup

Posted on 2001-09-03
4
298 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 50 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Scenario: Your operations manager has discovered an anomaly in your security system. The business will start to suffer within 15 minutes if it is a major IT incident. What should she do? We have 6 recommendations for managing major incidents (https:…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

914 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now