Solved

Sybase Backup

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to export Access tables into Sybase tables using Rapid SQL Version 8.1.1? 2 1,151
How do we check sybase license in ASE 1 2,956
sybase T-sql different 2 116
sybase 3 48
Are client relationship the only driver of a successful MSP? While important, client relationships are only one component. Learn how else MSPs can broaden their horizon and differentiate themselves.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

791 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