Process documentationPlanning Concept Locate this document in the navigation structure

 

To ensure the secure operation of your database system, you have to create a scheduling concept for the following activities:

  • Data Backups

  • Log Backups

  • Statistics updates for the SQL Optimizer

  • Checking database structures

  • Storing and checking backups

  • Table Statistics Updates

You can schedule, start, and monitor these activities with the DBA Planning Calendar.

You can use backup tools from other providers to increase the speed of the backup.

Prerequisites

Setting Up and Using the DBA Planning Calendar

Database Administration, Regular Administration Tasks, Using Third-Party Backup Tools

Process

Data Backups

Note that in the case of a recovery, the more up-to-date the last backup is, the fewer log entries have to be reloaded. Therefore, perform data backups as often as possible.

  • Perform at least one complete data backup every week (at the weekend, if possible).

  • On each day that the database is productive, start an incremental data backup.

    Note Note

    If a complete data backup is active you cannot start an incremental data backup.

    End of the note.

You can perform data backups in parallel to increase the speed of each backup.

Follow the procedure described in Scheduling a Data Backup.

For more information, refer to: Database Administration, Backing Up Data

Log Backups

In production systems, make sure that your log settings include hardware-based mirroring of the log area, that the log area can be overwritten only once it has been backed up, and that redo log management is always activated. Perform log backups regularly.

  • Automatic log backup

    The automatic log backup (autosave log mechanism) must always be activated (see Activating the Automatic Log Backup). Check regularly that automatic log backups are activated.

    Complete and incremental data backups are also possible while the automatic log backup is activated.

    Proceed as described in Activating Automatic Log Backups.

  • Interactive log backup

    If automatic log backup is not active, you must regularly check that there is enough storage space available in the log area. If no new log entries can be written due to lack of storage space in the log area, the database system shuts down. If necessary, back up the log area immediately by starting an interactive log backup.

    Proceed as described in Scheduling Interactive Log Backups.

  • Archiving the backup files

    Regularly archive the backup files that are written during a log backup on a data carrier of your choice. After these files have been archived successfully, you can delete the backup files that were written during the log backup. This ensures that there is always enough space in the directory for the backup files.

    If you use a backup tool from another provider, you can automate the process for archiving backup files (archiving log backup files).

    Follow the procedure described in Archiving Backup Files.

More information: Database Administration, Backing Up Log Entries, Archiving Backup Files

Updating the SQL Optimizer Statistics

After an installation, an upgrade or a database migration, execute the action Update all Optimizer statistics once.

Subsequently, you need to update the statistics only once a week (if possible at the weekend). To do this, use the actions Select tables for update statistics and Update statistics for selected tables.

Follow the procedure described in Scheduling Updates of the SQL Optimizer Statistics.

More information: SQL-Optimierer im CCMS

Database structure checks

In general, you should perform the database structure checks at the latest before the data carriers of a backup cycle are overwritten. The following recommendations are based on a backup cycle of four weeks.

Every four weeks (if possible, on a weekend), check the database structures for all database objects (tables and indexes). To do this, use the action Check database structure. This structure check may take some time, depending on the size of your dataset.

Alternatively, or additionally, you can check the database structures for all tables. To do this, use the action Check database structure (tables only). Indexes contain redundant information and can be restored after errors. Therefore, you do not need to check their structures as regularly.

Follow the procedure described in Scheduling a Database Structure Check. You can check structures for a single table by means of the user menu Tables/Views/Synonyms.

For more information, refer to: Database Administration, Checking Database Structures

Storing and Checking Backups

Tapes containing backups should not be directly overwritten with the next backup. If you retain, say, the last four backup generations, it may be possible to use an older backup if a data carrier fails.

Check the backups on the data carriers from time to time. In this way, you can find out in time if a backup is no longer readable. Use one of the following actions: Check complete data backup, Check incremental data backup or Check log backup.

For more information, refer to: Database Studio, Checking Backups

Table Statistics Updates

You should schedule the action Update table statistics to update the statistics on a weekly basis.

You can execute a one-off update of the table statistics using the Table Sizes user menu.

In the Table Sizes user menu, you can analyze the statistical data received.