Restoring the Database and Log Backups with Enterprise Manager

Prerequisites

The following explanation of the restore procedure assumes that you have set up your SAP system on at least three disks as recommended by SAP and that the disk referred to as the executables disk is still accessible. This disk contains the SQL Server executables that enable the use of the Enterprise Manager and the msdb database that keeps a record of all backups that have been made. Both the Enterprise Manager and the msdb database must be accessible to enable the restore operation described here.

The overall restore process consists of several restore phases. If you do not have valid transaction log backups the restore process may consist of only one phase.

Phases

·        Restore of the most recent database backup

·        If available, restore of the latest differential database backup

·        Restore of all succeeding transaction log backups

·        Restore of the latest transaction log backup. This may be an emergency transaction log backup which can still be performed when the data disk crashes

It is absolutely vital to ensure that the correct restore option is set for the restore phases.

·     For the last step of the overall restore process you have to set the restore option
Leave database operational. No additional transaction logs can be restored.

·     For all other restore operations you have to set the option:
Leave database nonoperational, but able to restore additional transaction logs

In the following procedure you first restore the most recent database backup and then the succeeding transaction logs. During the entire procedure, do not execute any transactions and do not shut down the database server. A Server shutdown would write a checkpoint to the log and, as a result, it would not be possible to restore further transaction logs.

Procedure

...

       1.      Ensure that no users are connected to the database.

       2.      In the Enterprise Manager, expand the server and select the SAP database you intend to restore. Choose Action  ® All Tasks  ® Restore Database.

The Restore Database dialog box opens.

       3.      Switch to the options tab and set the correct restore options.
Set the following:

Force Restore over Existing Database

Leave database operational. No additional transaction logs can be restored.

       4.      Switch back to the General tab, select the <SAPSID> database and specify Restore Database.

       5.      In the field Show Backups of Database, make sure that the <SAPSID> database is entered.

All the backups that are necessary to restore the <SAPSID> database are now displayed in a list. This list is generated from the backup history that is stored in the msdb database. Check that no backup is missing from the list, especially the last transaction log backup.

If backup history in the msdb database is not up to date then you have to restore the database and all the succeeding transaction log backups from device. In this case, follow the instructions given in Restoring a Backup from Device with Enterprise Manager .

If only the last transaction log backup is missing from the list of backups suggested for the restore operation, proceed as follows:

·         Change the restore options in the Options tab to:

Leave database nonoperational, but able to restore additional transaction logs.

·         Continue with steps 6 and 7.

·         Restore the last transaction log from device. Follow the instructions given in the section Restoring a Backup from Device with Enterprise Manager. Note that for this last step it is essential to change the restore option to Leave database operational. No additional transaction logs can be restored.

       6.      Insert the correct tape with the most recent database backup into the tape drive. If you originally backed up the database to several tapes in parallel, you have to insert all the tapes used in parallel.

To find out the label of a tape you have to insert, select the required backup from the list and choose Properties. A window with information about the backup opens. Media Name shows the label of the tape.

       7.      Switch back to the General tab. The backups that must be restored are preselected. This can include the most recent database backup, the latest subsequent differential backup and all succeeding transaction log backups.

Make sure that the most recent full database backup has been selected. If a differential backup is selected, verify that it is the latest one.

       8.      Choose OK to start the restore operation.

The data from the database backup is copied to the disk and the succeeding log backups are applied to the database.