OS: Windows 2008 64bit
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.
We were under the impression that
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).
We do our backups with
dbbackup -y -c "dsn=xxx;" D:\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.
answered 27 Mar '13, 08:03
"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.