I'm trying to make sure I understand the (-k) option when running dbbackup under SQL Anywhere 12.0.1.

Under ASA6, there was no such option when running dbbackup. My impression is that when dbbackup was fired, a checkpoint would happen first, applying the contents of the checkpoint log to the database, then back up the database files (please advise if this is not correct).

Now we have the auto, copy, nocopy, and recover options. (as of SQL Anywhere 10 I believe)

Would using the -k option against a database that is running as a consolidated database with multiple remotes involved in replication, cause any issues?

If drive space and CPU speed is not an issue, can anybody give me reasons why to use auto, copy, nocopy or recover when using the -k option? I guess I'm just wondering what the advantage is to having the checkpoint log just backed up as one of the files, vs. committing the contents of the checkpoint log before backing up.

TIA

Jeff Gibson
Intercept Solutions - Sybase SQL Anywhere OEM Partner
Nashville, TN

asked 25 Jul '11, 18:50

Jeff%20Gibson's gravatar image

Jeff Gibson
1.8k416172
accept rate: 20%

A good question, and I have to admit I don't make use of the -k option (nor the BACKUP STATEMENT WITH CHECKPOINT LOG analogies) simply because I don't seem to get the subtle differences of these options...

In case that does meet your situation, too, and performance/file size are no big deal, either, I suggest to omit the clause and rely on the defaults.

As usual with SQL Anywhere, I feel the defaults are appropriate until one knows better:)

(26 Jul '11, 09:25) Volker Barth
Replies hidden

Note when using DBBACKUP and omitting -k (as suggested above), the behaviour would initially depend on the usage of the -s option (which I would recommend).

However, when enough space is available, in the end -s will default -k to auto which in turn will choose copy - and that's the default for -k when not using -s, too...

(26 Jul '11, 09:35) Volker Barth

You are correct that in V6, a checkpoint was performed at the start of the backup; however, the checkpoint log was not applied afterwards. Immediately after a checkpoint, the checkpoint log is effectively empty so nothing needs to be applied. While the backup is in progress, any pages that are modified are put into both the checkpoint log and the temporary file. As the backup reads each page, it must fetch it from the temporary file if it has been modified or from the dbspace if it has not. The end result is that the backed up dbspace is identical to the dbspace as of the beginning of the backup. This is identical to the "NOCOPY" option in v12.

With the "COPY" option, BACKUP can just read the underlying dbspace file as-is. Page copies do not need to be sent to the temporary file and there is no overhead of looking for saved copies in the temporary file as we read the dbspace. On a busy server with lots of updates during a backup, the "COPY" option can save a lot of work during the backup. This was the first backup method that used large block IO; however, I think both methods do that now. This method was only made possible by the new checkpoint log format introduced in v8.

The doc covers the differences fairly well: http://dcx.sybase.com/index.html#1201/en/dbreference/backup-statement.html*d5e33342

All of the forms of backup are valid to use in a replication environment.

permanent link

answered 26 Jul '11, 09:26

John%20Smirnios's gravatar image

John Smirnios
11.9k396165
accept rate: 37%

Thanks for the answer John!! It's good to be able to understand the details on this.

(26 Jul '11, 16:02) Jeff Gibson
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×84
×60
×12

question asked: 25 Jul '11, 18:50

question was seen: 3,915 times

last updated: 26 Jul '11, 16:02