How do I tell if a backup is in progress?

[Breck_Carter]

How do I turn the following pseuodo-code into Watcom SQL?

IF a backup that will block a checkpoint is in progress THEN

...

END IF;

Here is the reason (from the V12 docs): a backup blocks checkpoints and any statement that causes a checkpoint, and a SQL module may decide not to proceed, or to take another path, rather than be blocked.

---

BTW the statement "Any statement that causes a checkpoint. This includes data definition statements and the LOAD TABLE and TRUNCATE TABLE statements." may not be entirely correct about CREATE TABLE and TRUNCATE TABLE. I have submitted a comment on the V11.0.1 DCX topic here.

[VolkerBarth · Accepted solution]

Starting with SQL Anywhere 16.0.1674, there's an easy way to answer that by checking the value of the following new database property:

SELECT DB_PROPERTY('BackupInProgress');

A new database property, BackupInProgress, has been added. Querying the property will return 'on' when there is a backup happening, and 'off' otherwise.

According to the cited CR entry, that property is not available in v12 and below.

[VolkerBarth · Accepted solution]

...and the inevitable Kudos to Breck for listing all those new features in v16 EBFs/SPs on his blog:

• New Features Since GA: SQL Anywhere 16

[johnsmirnios]

There's no solid way that I can see. I'd be concerned about the following logic too:

IF !(backup in progress) {
    // Drat, a backup could start right here and the following checkpoint will still wait
    checkpoint;
}

If there is a backup in progress and it uses WAIT AFTER END then an explicit CHECKPOINT by another connection will fail if it has uncommitted operations (otherwise the backup would wait forever for the other connection to commit but that connection is waiting forever for the backup to end so that it can do a checkpoint).

From the historical client API, you could attempt to start a backup. I think the attempt will fail if a backup is already in progress. I'm not sure if you could concoct a "do nothing" backup statement that would fail if another backup is in progress but succeed otherwise. I haven't checked but maybe the BACKUP statements just queue up rather than failing if another is in progress.

If you resort to any of these approaches, you didn't hear it from me 🙂

[VolkerBarth]

Starting with V12, it seems that backups (both with BACKUP DATABASE and DBBACKUP) are done by a temporary connection (like same kind on event, methinks).

According to the V12 docs, the temporary connection is called "BackupDB", in my test cases with the name "INT: BackupDB".

However, I can't claim whether this connection is only active while the backup process is "active", i.e. while it doesn't wait on other connections to commit.

[johnsmirnios]

I think those temporary connections will only appear if we do a parallel backup and I don't think all backups are guaranteed to be parallel.

[VolkerBarth]

I just tested with only a simple full backup of a small database (about 15 MB) on a notebook, just default options, so I don't expect a high degree of parallelization here. - It went so quick (just a few seconds) so it was quite hard to check the temporary connection with sa_conn_info:)