Sybase backup

Lewis Li - ML system admin

unread,

Dec 4, 1996, 3:00:00 AM12/4/96

to

In order to recover up to the minutes of data in sybase SQL Server,
I have implemented a backup scheme as follows (also the system and
transaction log are on separate devices) :

midnight dump database dbname to dumpfile with no_log
every hour dump transaction dbname to dumpfile

The questions are:

1. Will the dump database command truncate the log ?
Is the option with no_log allowed in dump database command ?

2. Will the dump transaction command truncate the log ?

3. Should I turn on the database option "trunc. log on chkpt." ?

4. If "trunc. log on chkpt." option is turned on, it will truncate
the log after checkpoint. When the database device fails
(while the transaction log is intact) between the time when
the checkpoint finish and the dump transaction command is done,
will there be a lost of data (since the transaction has already
been truncated) ?

------------------------

Lewis Li Email: le...@hongkong.ml.com
System Administrator
Merrill Lynch Int'l Inc.
18/F Asia Pacific Tower Tel: (852) 2536 3464
3 Garden Road, Hong Kong Fax: (852) 2536 3037

Bret Halford

unread,

Dec 4, 1996, 3:00:00 AM12/4/96

to

In article <583l9n$n...@news.ml.com>, le...@hongkong.ml.com (Lewis Li - ML system admin) writes:
|> In order to recover up to the minutes of data in sybase SQL Server,
|> I have implemented a backup scheme as follows (also the system and
|> transaction log are on separate devices) :
|>
|> midnight dump database dbname to dumpfile with no_log
|> every hour dump transaction dbname to dumpfile
|>
|> The questions are:
|>
|> 1. Will the dump database command truncate the log ?
|> Is the option with no_log allowed in dump database command ?

No, and therefore, also no to the second. Dump database does
not truncate the log.

|> 2. Will the dump transaction command truncate the log ?

It will try to. If the log starts with an open transaction, dump tran
will not be able to free any space.

|> 3. Should I turn on the database option "trunc. log on chkpt." ?

No, not if you are trying to save off transaction dumps. If "trunc log on
checkpoint" is set on, your recovery is limited to your last
database dump.

|> 4. If "trunc. log on chkpt." option is turned on, it will truncate
|> the log after checkpoint. When the database device fails
|> (while the transaction log is intact) between the time when
|> the checkpoint finish and the dump transaction command is done,
|> will there be a lost of data (since the transaction has already
|> been truncated) ?
|>

Any data on the database device that is new since the last database
dump will be lost. If truncate log on checkpoint was off, and you
had been saving transaction dumps, and a data device failed (as opposed to
a log device [database device is ambiguous]), then you would still
be able to dump the tran log (dump tran with no_truncate) and later
apply it to your previous database and tranlog dumps.

--
---------------------------------------------------------------------
| Bret Halford Imagine my disappointment __|
| Sybase Technical Support in learning the true nature __|
| 6400 S. Fiddlers Green Circle of rec.humor.oracle... __|
| Englewood, CO 80111-4954 USA |
============================================================

J.Masino/T.Larson

unread,

Dec 5, 1996, 3:00:00 AM12/5/96

to

Lewis Li - ML system admin (le...@hongkong.ml.com) wrote:
: In order to recover up to the minutes of data in sybase SQL Server,

: I have implemented a backup scheme as follows (also the system and
: transaction log are on separate devices) :
:
: midnight dump database dbname to dumpfile with no_log
: every hour dump transaction dbname to dumpfile

OK so far.

: The questions are:

:
: 1. Will the dump database command truncate the log ?
: Is the option with no_log allowed in dump database command ?

Dumping the database does not clear the transaction log -- only dumping
the transaction log will do that. If your log is not on a separate
device, then you need to clear the log (dump transaction with
truncate_only) before you dump your database. It also means you can't
dump your transaction log -- it must be on a separate device.

: 2. Will the dump transaction command truncate the log ?

Yep, that's how it's done. The inactive portion of the log is removed
after the transaction log is dumped.

: 3. Should I turn on the database option "trunc. log on chkpt." ?

Only if you don't care about the transaction log. Turning this option
on negates the need and even the ability to dump the transaction log.
The server won't let you dump the log if this option is on because it
is a flag that not all transactions are in the log, therefore it can't
be used for recovery, therefore it can't be dumped.

: 4. If "trunc. log on chkpt." option is turned on, it will truncate

: the log after checkpoint. When the database device fails
: (while the transaction log is intact) between the time when
: the checkpoint finish and the dump transaction command is done,
: will there be a lost of data (since the transaction has already
: been truncated) ?

If your log has been truncated (whether on checkpoint or by issuing
the dump transaction with truncate_only command), then you can't dump
the transaction log for recovery purposes. As for your question, yes
the data is lost because the transaction log was truncated.

There's a good write-up on what DUMP DATABASE does vs DUMP TRANSACTION
in the System Administration Manual. Check it out. It explains all
this far better than I have here and it's pretty important to understand
how these commands work.

Good luck,
Teresa Larson

____________________________________________________________________
/ Teresa A. Larson ISUG Electronic Media Chair /
/ Bell Atlantic Voice: (301) 282-0051 /
/ 13100 Columbia Pike, A-3-3 Fax: (301) 282-9416 /
/ Silver Spring, MD 20904 Teresa....@bell-atl.com /
/___________________________________________________________________/
#include <std_disclaimer>

H.S. Prasad 4-8398

unread,

Dec 5, 1996, 3:00:00 AM12/5/96

to

One of the things the Lewis must realize is there is no method
by which you can guarantee upto the minute data recovery, because it would
entail dumping the logs every minute. This will drag the performance to pits.

-Prasad

br...@sybase.com (Bret Halford) wrote:
>In article <583l9n$n...@news.ml.com>, le...@hongkong.ml.com (Lewis Li - ML system admin) writes:

>|> The questions are:
>|>
>|> 1. Will the dump database command truncate the log ?
>|> Is the option with no_log allowed in dump database command ?
>

>No, and therefore, also no to the second. Dump database does
>not truncate the log.
>

>|> 2. Will the dump transaction command truncate the log ?
>

>It will try to. If the log starts with an open transaction, dump tran
>will not be able to free any space.
>

>|> 3. Should I turn on the database option "trunc. log on chkpt." ?
>

>No, not if you are trying to save off transaction dumps. If "trunc log on
>checkpoint" is set on, your recovery is limited to your last
>database dump.
>

>|> 4. If "trunc. log on chkpt." option is turned on, it will truncate
>|> the log after checkpoint. When the database device fails
>|> (while the transaction log is intact) between the time when
>|> the checkpoint finish and the dump transaction command is done,
>|> will there be a lost of data (since the transaction has already
>|> been truncated) ?
>|>
>

>Any data on the database device that is new since the last database
>dump will be lost. If truncate log on checkpoint was off, and you
>had been saving transaction dumps, and a data device failed (as opposed to
>a log device [database device is ambiguous]), then you would still
>be able to dump the tran log (dump tran with no_truncate) and later
>apply it to your previous database and tranlog dumps.
>
>
>--
>---------------------------------------------------------------------
>| Bret Halford Imagine my disappointment __|
>| Sybase Technical Support in learning the true nature __|
>| 6400 S. Fiddlers Green Circle of rec.humor.oracle... __|
>| Englewood, CO 80111-4954 USA |
>============================================================
>

--
/***********************************************************************/
H.S. Prasad Standard Disclaimer
Email: pras...@jpmorgan.com
Phone: (302)634-8398
Fax : (302)634-8563
/***********************************************************************/