dbbackup -k recover still requires db to perform recovery

[former_SQLA_member1694882]

Version: 11.0.1.2837

OS: Windows 2008 64bit

The question:

If we use the following command to backup the database, is the database guaranteed not to require a recovery process during startup? i.e. can be started up in Read Only mode.

dbbackup -k recover -s -c "links=tcpip;dsn=Backup" E:\\Backups\\database\\ -y -r

We were under the impression that -k recover was the same as WITH CHECKPOINT LOG RECOVER. And according to the BACKUP statement documentation, that should mean that the database can be started in Read Only mode.

However, we were notified about an error with our validation procedure last night where dbvalidate returned an unable to start database (-082) error. Our validation procedure starts up the database in Read Only mode (using -r parameter). It turned out that the database couldn't be started as it needed to go through a recovery process. Unfortunately the support person at the time failed to capture the console output to see if anything was reported in the console (there were reportedly some fragmentation warnings however).

[Breck_Carter]

The Help topic you quote about the BACKUP statement says this:

"To create a backup that can be started on a read-only server without having to go through recovery, you must use both the WAIT BEFORE START and WITH CHECKPOINT LOG NO COPY clauses. The WAIT BEFORE START clause ensures that the rollback log is empty, and the WITH CHECKPOINT LOG NO COPY clause ensures that the checkpoint log is empty. If either of these files is missing, then recovery is required."

That does seem to contradict this utterly incomprehensible section of the same Help topic:

"Caution Backup copies of the database and transaction log must not be changed in any way. If there were no transactions in progress during the backup, or if you specified BACKUP DATABASE WITH CHECKPOINT LOG RECOVER or WITH CHECKPOINT LOG NO COPY, you can check the validity of the backup database using read-only mode or by validating a copy of the backup database.

However, if transactions were in progress, or if you specified BACKUP DATABASE WITH CHECKPOINT LOG COPY, the database server must perform recovery on the database when you start it. Recovery modifies the backup copy, which is not desirable."

...if you can interpret either section, let alone reconcile the two, you are more clever than I 🙂

My limited personal experience with WAIT BEFORE START indicates it might as well be named "NEVER START THE BACKUP", but that's another discussion.

What I do, is take a normal backup with no fancy options, then make a second (disposable) file copy for validation purposes... if it validates OK then you know that the original backup and master copy are both OK.

[VolkerBarth]

I agree on all these points - it's waaaay easier to validate a copy of the backup than to be able to guarantee the backup does not need recovery to start up... - and yes, I find these BACKUP DATABASE WITH CHECKPOINT ... options too difficult to understand...

FWIW, I certainly prefer server-side backups for performance reasons. But server-side vs. client-side doesn't have influence on the need to recover or not.

[reimer_pods]

I agree that in many cases it's ok to run the validation on a copy of the backup. But with database sizes way beyond 20 GB, which is the case for an increasing number of our clients, that can get tedious.

[former_SQLA_member1694882]

@Breck - We obviously read it as though WITH CHECKPOINT X created valid copies of the database files, however as you rightly point out those statements do not reconcile.

As @Reimer indicated we could create a copy of the backup, however we have space limitations with doing that.

Any way I can get someone from SQL Anywhere team to comment on this?

[former_SQLA_member1694903]

We do our backups with

dbbackup -y -c "dsn=xxx;" 😧\\database\\backup

And have always been able to start the backup copy of the database. For doing a normal backup, I don't think any of the -k options are required. We don't use the -s (server backup) parameter. Instead, we run it from our "backup" server. Sorry I can't be more help.