on 2013 Mar 26 10:05 PM
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).
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
@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?
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
9 | |
9 | |
7 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.