Database file corruption may not be apparent until the database server tries to access the affected part of the database. As part of your data protection plan, you should periodically check that your database has no errors. You can do this by validating the database using tools such as the Validate Database wizard in Sybase Central, or the Validation utility (dbvalid). You should validate your database both before and after you perform a backup. You must have VALIDATE permissions to perform validation activities.
Validation includes a scan of every row in every table and a look-up of each row in each index on the table. Validation requires exclusive access to each table in turn. For this reason, it is best to validate when there is no other activity on the database. Database validation does not validate data, continued row structure, or foreign key relationships if you perform an express validation using the -fx option.
If you can be sure that no transactions are in progress when the backup is being made, the database server does not need to perform the recovery steps. In this case, you can perform a validity check on the backup using the read-only database option. See -r server option.
Using the BACKUP statement with the WAIT BEFORE START clause ensures that no transactions are in progress when you start a backup.
If a base table in the database file is corrupt, you should treat the situation as a media failure, and recover from your previous backup. If an index is corrupt, you may want to unload the database without indexes, and reload.
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. 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 created your database with checksums enabled, you can check the validity of the disk pages. Checksum validation requires either DBA or VALIDATE authority.
For databases with checksums enabled, a checksum is calculated for each database page and this value is stored when the page is written to disk. You can use the Validation utility (dbvalid) or the Validate Database wizard in Sybase Central to perform checksum validation, which consists of reading the database pages from disk and calculating the checksum for the page. If the calculated checksum does not match the stored checksum for a page, the page has been modified or corrupted while on disk or while writing to the page. If one or more pages has been corrupted, an error is returned and information about the invalid pages appears in the Server Messages window.
For more information about checksum validation, see VALIDATE statement, and Validation utility (dbvalid).
The database server calculates checksums for critical database pages in all databases, regardless of whether checksums are enabled. These checksums are used to detect offline corruption, which can help reduce the chances of other data being corrupted as the result of a bad critical page. Because the database server calculates these checksums, if a database becomes corrupt that does not have checksums enabled, the database server shuts down with a fatal error.
As well, if you validate a database that does not have checksums enabled, but that has a bad critical page, dbvalid can still return warnings about checksum violations.