Database Backup


Your database is always subject to the risk of damage. There are many reasons why data might be lost or become corrupted:

·        Disks crash

·        Interruption of power supply

·        User mistakes

·        Virus damage

·        Fire or earthquake

Therefore, be sure to work out a security strategy that consists of a validated, and if possible certified backup and restore process.

The first part of this security strategy is to backup the database and its transaction log at regular intervals. When the database is damaged, these data and log copies can be reloaded in order to restore your database. However, the most important part of the security strategy is to test and validate the backup and restore process. Only if both work reliably, your system is protected against data loss and you are able to restore it to a correct and consistent previous state.

Process Flow

Generally the procedure for backing up includes the following phases:


       1.      Backup Strategy

A backup can only effectively contribute to safeguarding your database, if it is performed as part of an overall backup and restore strategy. It is therefore important to first carefully work out a strategy and to test it before applying it in a productive system. It should define details such as the frequency of backups, what should be backed up at various times, which media are to be used and how backups are to be verified to ensure that they can be used for a later restore. When a strategy is planned, many different factors play a role. For example, it is important to take into account the transaction workload, the maximum permissible downtime, the available hardware and, in the worst case, the amount of data loss that is tolerable.

Once you have drawn up a backup strategy, you can begin to implement it. Both the MS SQL Enterprise Manager and the SAP system provide the functions you need to define, schedule and execute backups and to perform other related tasks. For more information, see Backup Strategy.

       2.      Definition and Scheduling of the Backup

Depending on the tool you decide to use, the procedure for defining and scheduling a backup will vary. However, the technical details that need to be specified in order to be able to perform a backup are always the same:

Backup Details

Backup contents

SAP database, msdb database, master database and so on.

Backup type

Full database, transaction log, differential database and so on.

Backup destination

The tape devices or disks that the backup is written to.

Volume label (MediaName)

The names assigned to the tapes that the backup is written to.
The naming conventions recommended by SAP should be used.

Expiration period

The length of time in which a backup tape is protected from being overwritten.

Execution time

Backups can be started immediately or scheduled to run at a specific time. Normally they are scheduled to run periodically, in accordance with a backup strategy that has already been defined.

Depending on the tool you use, you may have to enter these details in your system manually or they may be preset. For more information, see Backing Up with the Enterprise ManagerBacking Up with the SAP System.

       3.      Backup Execution and Verification

Normally backups are executed automatically. They are scheduled in advance in accordance with an overall backup strategy and then run automatically at a predefined time. However, it is the responsibility of the database administrator to always ensure that the correct tapes are inserted in the backup tape devices.

Usually a tape that has been written will be removed from the tape device and stored safely. In the past, however, it has been found that it is not certain that readable data actually exists on the tape that was supposedly written successfully. For this reason, the SQL Server offers the verify option. After the backup this always checks whether all the files have been written to the tape and whether they can be read. SAP recommends that this option is used for all backups.

Even if the verify option is used, this does not guarantee that the backup tapes can be physically read. In particular, when a new tape drive or driver is installed, you cannot be certain that the backups are being written correctly. The only way to make sure that they are usable is to actually apply them to a test system in a trial restore operation. SAP recommends that you perform such tests regularly.

       4.      Backup Monitoring

During and after the execution of a backup it should be part of the routine to check whether execution has been successful using the tools available. For more information, see Monitoring Backups.

       5.      Consistency Check

The backup verification feature does not check the database for consistency. If a database contains corrupted data, this will be transferred to the backup. Therefore, ideally, consistency checks should be performed at regular intervals.

The database consistency check is scheduled in the SAP system using the DB Administration Planning Calendar.

We recommend you to run the consistency check on the Backup Validation Server, because you can check whether the backup has been successful. Another advantage is that there is no I/O impact on the production server as a database consistency check is very I/O intensive. For more information, see Checking Consistency of the Database.