Backup of Database while in Use

[baventjr]

I have a 40gb database (SQL Anywhere 17) that I am trying to backup and have begun to get errors when validating a certain table. I am using a maintenance plan that runs when the database should be at the lowest level of use (3:45 am) but I can't get it to validate and do the backup. I have added some prevalidation commands to rebuild the indexes on the table that fails validation and they run successfully. It continually fails with this message:

The maintenance plan has ended because of the following error: Run time SQL error -- Validation of table "TRAV_MODS" has failed SQLSTATE: 40000 SQLCODE: -300 Maintenance plan Backup ZZZZZZ for YYYYY on XXXXXXX finished on 2020-01-15 at 03:50:36.470

Any ideas as to what I might try?

[baventjr]

By the way, I have tried Full Image backups and Full Archive backups and neither will work. Both fail at the same place.

[Breck_Carter]

Presumably you have an older backup that didn't fail the validation. Start that one and apply the subsequent log backups to bring it up to date.

Tip: Backups are easy, restores are hard... restores are especially hard if the backups have never been tested.

Tip: When working on a restore, do not overwrite ANY files, even if they are faulty... if necessary, make file copies of everything and put them somewhere safe.

[VolkerBarth]

FWIW, you might also try to just validate the according table with VALIDATE TABLE statements. In some cases, it might be sufficient to rebuild according indexes.

[VolkerBarth]

Well, if the database is not valid as a whole, why would you want to backup that?

[Breck_Carter]

Volker, I'm ignorant of how the delivered "backup plan" feature works. Does it run the validation on the active database, or does it run the validate on the backup copy with no other connections?

[baventjr]

At this point I have no valid backups of this database. I am pretty much screwed if this system fails. My only hope is a third party that is backing up changed files every 15 minutes. I hope I don't have to test this out until I get this fixed.

I have tried validating the table the backup is failing on and I am finding it will validate 4 out of 5 times and the 5th time failure is caused by an index row count mismatch between an index and the table. I suppose I could delete the index and see how the performance is affected.

This column that the failing index is on has a lot of <blank> rows. Could that be causing the count mismatch?

[baventjr]

Sorry, not blank rows but blank entries in the column.

[VolkerBarth]

AFAIK, the builtin maintenance plan facility creates an event that does one or more of

• validating the running database and
• backing up the database

so it's not a validation of a copy.

AFAIK by default it prevents new connections (via CALL SA_SERVER_OPTION('ConnsDisabled', 'Yes')), I don't know about current users.

[VolkerBarth]

At this point I have no valid backups of this database.

So what backups do you have available at all?

And can you shutdown that database for further validation (and possibly rebuilding), or is it still used by actual users?

[baventjr]

I cannot shut down the db as it is in use by users. I may be able to schedule a shutdown over a certain time period during a weekend.

[Breck_Carter]

> validating the running database

Doesn't that guarantee failure?

The dbvalid docs say you should "ensure that no changes are made the database while it is being validated".

[Breck_Carter]

Are you seeing any errors other than errors reported by the validation process?

If the answer is no, perhaps you should stop doing validation as part of the "backup plan", and do validation separately on the resulting (idle) backup copy. If the backup copy passes validation, then you know (by definition) the original database must be valid since the backup process does not fix errors 🙂

[baventjr]

I have deleted the index that was failing validation and will see if the backup works tonight.

[VolkerBarth]

If it fails, I would really recommend to separate the tasks of validation and backup, so you get a backup for further analysis even if the validation failed.

[VolkerBarth]

Well, it could lead to spurious validation errors, yes. Personally, I think these plans are a good starting point but should be adapted for real usage... And one should read and understand the generated code...

[chris_keating]

It is possible for validation errors to be reported if there are changes happening on the database during the validation process - since this is a live database, this is certainly a possibility. This behaviour is documented. The "it will validate 4 out of 5 times and the 5th time failure is caused by an index row count mismatch between an index and the table." is consistent with changes happening to the database while the validation is being run. If you must validate a live database, you may need to consider the VALIDATE options WITH DATA LOCK or WITH SNAPSHOT. As others have noted, have you validated a copy of the backup(s) to see if it has issues. I suspect those will be clean.

[baventjr]

I am hoping to get a "unvalidated" backup tonight. I tried the Express check and it didn't validate, stopping the backup.

For Chris, when I was doing the validation of the database and it failed 1 out of 5 times I was doing on a database that had no activity on it. That made failures very suspect, but you are right about the database having activity on it. I am running the backup when it is least used to see if that will help, although I am not totally sure it is inactive.

[asa25]

Does the following information help you?

1958942 - Error: "Assertion failed: 123456" / SQLCODE -301 "Internal Database Error" - Handling an Assertion Failure

How to Salvage the database if there is no valid backup and the database starts, but assertion failures occur frequently during operation

[Breck_Carter]

Did you post this to the wrong question?

[BudDurland]

Just my 2 cents, if you continue to have problems with validation, and dbbackup is having trouble, I would schedule some downtime and do a dbUnload / reload.

[baventjr]

Ok, I was finally able to get a backup without using validation. Now I need to test to see if it will validate before I unload and reload. I do need to schedule some downtime to unload and reload as you suggested. I think I have a grip on what I need to do now. Thanks for all your help.

[Breck_Carter]

> schedule some downtime to unload and reload

For testing purposes, try copying your backup database and running the unload/reload on a different computer; e.g., use a USB drive and an ordinary workstation.

FWIW "40GB" is not big.

[VolkerBarth]

But just when the validation of the copy has failed, right?