Solved

Sybase Backup

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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 query constraints in Sybase 3 1,680
Looking for Sybase 12.5.4 EBF ESD#9.1 or 10 patch 5 788
Generating a Format File in Sybase ASE BCP 1 1,173
SQL Update query 9 361
This tutorial shows how to create a greeting card by combining two image layers and a text layer on a PC using a free image editing app.
In this increasingly digital world, security hacks are no longer just a threat, but a reality. As we've witnessed with Target's big identity hack 2013, Heartbleed in 2015, and now Cloudbleed, companies and their leaders need to prepare for the unthi…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 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