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.
asked 25 Jul '11, 18:50
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.
answered 26 Jul '11, 09:26