Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

This section should help you to quickly recover a database from an existing backup if no GUI tool (Database Studio, Database Manager GUI) is available.

 

  1. Preface
  2. Checking the backup history for available backups to restore
  3. Restore a data backup
  4. Verifying the available log information
  5. Restore a log backup
  6. Restore until a specific date and time
  7. Additional DBM commands for recovery
  8. Checking the progress of a restore while it is running
  9. Checking the success of a restore after completion

Preface

A recovery means to restore a certain dataset of a database using backups. This can include to restore a full data backup only but also to restore a set of data backup, incremental backups and log backups.

Similar to creating a backup a backup template (formerly known as backup medium) is required to restore a backup. Via the existing templates on a database you can restore the corresponding backup which was created with this template.
If a backup from a different database has to be recovered a new template has to be defined for this backup. For information on how to define a template please refer to HowTo - SAP MaxDB Backup with Database Manager CLI and SAP MaxDB documentation (glossary entry Backup Template).

Normally you use the GUI tools (Database Studio or Database Manager GUI) for a recovery as it is much more convenient to use these tools. But in case you have to use Database Manager CLI (DBMCLI) and have the required commands not in mind use the following information. It deals with file backups and won't cover the possibility to use backup tools from other providers. For those see SAP MaxDB documentation (use glossary entry Backup Tool).

You have established a DBMCLI session to the database you want to work with using the DBM operator. Example database is called DB1. Prompt looks like this:

dbmcli on DB1>

For every command or the first part of a command you can call a help. For example

dbmcli on DB1> help recover_start

gives:

OK

recover_start

<backup_template> <backup_type> [ExternalBackupID <ebid_list>] [<nnn>] [UNTIL <date> <time>] [AUTOIGNORE] [LABEL <label>]

DBM command recover_start requires a backup template (formerly known as backup medium) and a backup type to be specified. The other parameters are optional and set with default values resp. set correspondingly to the template definition.

If you are not sure about the full DBM command name you can use this help to display e.g. all DBM commands beginning with recover.
dbmcli on DB1> help recover
All valid DBM commands are shown including the mandatory and optional parameters.

For most DBM commands you can also call a comprehensive explanation using explain command:
dbmcli on DB1> explain recover_start

Always you can use the documentation for DBM commands which could be found in SAP MaxDB documentation Overview of All DBM Commands.

back to top

Checking the backup history for available backups to restore

This first step will show available backups which can be used for the recovery. The backup history data are stored within a file. It is possible to fetch these history data into the memory for faster access via the following command:

dbmcli on DB1> backup_history_open

If this command has not been executed it will be done implicitly with this command:

dbmcli on DB1> backup_history_list

This command shows the backup history. Unfortunately it is a little bit hard to read. As mostly not all information for a backup is necessary it is a good idea to use filters, e.g.:

dbmcli on DB1> backup_history_list -c label,action,pages,stop,media

The output will look similar to this:

DAT_000000001

SAVE COLD

376

2008-11-04 17:55:15

Data01

PAG_000000002

SAVE WARM

48

2008-11-04 17:56:34

Page01

LOG_000000001

SAVE WARM

24

2008-11-04 17:57:45

Log01

It is much better to read. At first here you have the information of the label of the backup. You can recognize which kind of backup it is (DAT: full data backup, PAG: incremental data backup (changed pages) or LOG: log backup). The second column shows the information if the particular backup was created in operational state ONLINE (SAVE WARM) or operational state ADMIN (SAVE COLD). How many pages was saved is visible in the third column of the above example. You can calculate the size of the corresponding backup using this number (one page = 8 KB). The time stamps in the fourth column represent what date and time the backups have been finished. The last column gives the names of the backup templates (formerly known as backup media).

A further interesting option is the following:

dbmcli on DB1> backup_history_list -r last

It shows the last backups which allow a full recovery. Combined with the columns of interest it looks quite convenient:

dbmcli on DB1> backup_history_list -r last -c label,action,pages,stop,media

The output will look similar to this:

DAT_000000001

SAVE COLD

376

2008-11-17 17:55:15

Data01

LOG_000000001

SAVE WARM

24

2008-11-17 17:57:45

Log01

In this example for a full recovery the incremental backup is not required (and therefore not listed) as all information are still available via the log. But for example, if you would have many log backups to restore and also suitable incremental data backups would be available, it would be faster to restore as much incremental data backups as possible. Restoring changed pages takes less time as restoring and afterwards applying log.

back to top

Restore a data backup

To start a restore of a data backup a database session has to be created:

dbmcli on DB1> db_connect [<user>],[<password>]

As you created the DBMCLI session using the DBM operator, no user has to be specified here.
The same user (DBM operator) will be used.

At first it is possible to check if the backup can be accessed as expected and reading the data will work fine. A service session is required for this:

dbmcli on DB1> db_admin
dbmcli on DB1> service_connect
dbmcli on DB1> recover_check <backup_template> <backup_type>

For the example above:

dbmcli on DB1> recover_check Data01 data

If the output of this command shows a returncode 0 and information about the backup the check was successful. To start the restore of this data backup afterwards you can carry on with:

dbmcli on DB1> service_release
dbmcli on DB1> db_connect
dbmcli on DB1> recover_start <backup_template> <backup_type>

For the example above:

dbmcli on DB1> recover_start Data01 data

The output consists of several lines. For example there is information about returncode, timestamp, version used when the backup was created, how many pages was transferred etc.:

Returncode

0

Date

20081105

Time

00182255

...

...

Kernel Version

Kernel 7.6.05 Build 011-123-196-300

Pages Transferred

376

...

...

If the database is restartable after the restore of this data backup depends from the available log information on the log area of the instance (in the example above: DB1  ). Please see below for more information about this.

back to top

Verifying the available log information

Restore of database log is only necessary if the required log information are not in the log area anymore
(i.e. already saved into a log backup and overwritten afterwards). You can check this via command (database has to be in operational state ADMIN):

dbmcli on DB1> db_restartinfo

Among others the following lines appear:

Used LOG Page

6

First LOG Page

0

Restartable

1

Consistent

1

Used LOG page specifies the log page which will be used after the next start of the database to operational state ONLINE. First LOG Page shows which is the oldest available log page within the log area currently. This information is useful to check if log backups have to be restored in addition to a data backup. If Used LOG page is lower than First LOG Page it is required to restore log backups until this log backup was restored which contains the required First LOG Page. To check the range of pages which is stored within a log backup it is helpful to use firstlog and lastlog as filter:

dbmcli on DB1> backup_history_list -r last -c label,action,pages,firstlog,lastlog,media

DAT_000000001

SAVE COLD

376

0

 

Data01

LOG_000000001

SAVE WARM

24

0

4

Log01

So in this example at log backup LOG_000000001 the first saved log page is 0 and the last saved log page is 4. This means all required log information is still available on the log area. The database can be started into mode ONLINE directly. That's why the above output of command   db_restartinfo   shows Restartable is set to 1 and Consistent is set to 1 as well (meaning: no log backups have to be restored to allow a restart to operational state ONLINE).

A second example with numbers more common in real life:

...

...

...

...

...

...

LOG_000000099

SAVE WARM

43704

11330446

11374135

M01_Log

LOG_000000100

SAVE WARM

43704

11374136

11417825

M01_Log

It shows that backup LOG_000000099 contains log pages from 11330446 to 11374135 and LOG_000000100 contains the pages from 11374136 to 11417825. If after a restore of a data backup command db_restartinfo would show:

Used LOG Page

11330500

First LOG Page

11410101

it would be necessary to restore log backups LOG_000000099 and LOG_000000100 after the restore data.

Detailled information about the output of DBM command db_restartinfo can be reached via SAP MaxDB documentation Overview of ALL DBM Commands.

back to top

Restore a log backup

To restore a log backup the number of the particular backup has to be specified (if the backups was done to the file system). Log backups are numbered sequently by the database automatically. To restore log backup LOG_000000001 from the above example the following DBM command has to be used:

dbmcli on DB1> recover_start Log01 log 001

The number 001 is set according to the label LOG_000000001 (last three digits). The output is similar to the output from a restore data. But in contrast to this the lines "First LOG Page" and "Last LOG Page" are filled with the according numbers:

First LOG Page

0

Last LOG Page

4

Let's assume that there still exist log information on the log area of the database. In this case the database kernel checks after the restore log if the log information is without interruption now (i.e. the just restored log information together with the still available information on the log area). If there is no gap the database will be restarted into operational state ONLINE automatically. In case there is a gap then the restore log ends with message -8020 (Next volume required). Now it is necessary to execute DBM command (restore the next log backup):

dbmcli on DB1> recover_replace Log01 C:\MaxDB\Backup\LOG 002

In this DBM command the type of backup (log) is replaced by the physical location of the log backup defined in the backup template definition (C:\MaxDB\Backup\LOG).
Repeat this DBM command using the corresponding log backup labels until the gap has been filled and all required log has been restored for interrupt free log information on the log volume. With the DBM command that restored the last required log the database restarts automatically into operational state ONLINE. This last DBM command then ends with return code 0.

Let's now assume there is no log information left on the log volume. This results in message -8020 (Next volume required) after every restore log. The reason is that the database kernel does not know if you want to restore more log backups before the next database restart or not. After every restore log it is possible to execute DBM command:

dbmcli on DB1> recover_ignore

After this the database restarts into operational state ONLINE using the available log information up to the last restored log backup.

If a log backup is restored using a parallel backup template (former: parallel backup medium) message -8020 (Next volume required) appears when all log information from the first member of this parallel template has been restored. The output shows that there are still pages to be restored, e.g.:

Returncode

-8020

Date

20081106

Time

00172230

Server

<server name>

Database

DB1

Kernel Version

Kernel 7.6.05 Build 011-123-196-300

Pages Transferred

112

Pages Left

248

Here again DBM command recover_ignore has to be used to finish the complete restore of this log backup.

back to top

Restore until a specific date and time

It is possible to restore to a specific point in time. For example you know date and time of an unwanted bigger change to the database which you want to make undone. So you can recover the database to a point in time just before this unwanted change was done. After restoring the last data backup before the particular point in time the following DBM command can be used:

dbmcli on DB1> recover_start <backup_template> <backup_type> <nnn> UNTIL <date> <time>

for the example above:

dbmcli on DB1> recover_start Log01 log 001 until 20081104 173000

In this example the log will be restored until November 04, 17:30:00 and the database is switching to operational state ONLINE afterwards automatically. Please note that the time has to be entered in 24-hour mode (5:30pm -> 17:30).

back to top

Additional DBM commands for recovery

A very convenient way for a recovery is to use DBM command autorecover. It can be used to recover the database automatically to the latest possible status according to the available backups.

dbmcli on DB1> db_connect
dbmcli on DB1> autorecover

The database has to be in opeational state ADMIN before executing this command. On the basis of the backup history the database kernel is searching for the required backups for the recovery. In addition it will be checked if these backups can be accessed successfully (when using files or external backup tools as data carrier).

DBM command autorecover also includes the possibility to recover until a specific date and time:

dbmcli on DB1> db_connect
dbmcli on DB1> autorecover until <date> <time>

After execution of DBM command autorecover the database will be started to operational state ONLINE automatically. This always happens, even if no backup needs to be restored according to the backup history. The DBM command does not show a confirmation message after execution.

More information about DBM command autorecover can be reached via SAP MaxDB documentation Overview of All DBM Commands.

A further DBM command which is worth to know is db_activate recover <template_name> data. This DBM command performs an initialization of the database and afterwards a restore of the specified data backup. Please be aware that initialization means that all data are deleted and therefore lost forever. This includes the current log information on the log area as all volumes (data and log) are formatted. Furthermore the user names and passwords are overwritten with the corresponding data from the backup (except the data of the first database operator user). So it is necessary to know the user names and passwords contained in the backup. After the restore of the backup it is required to load the system tables. So this command is useful to create a copy of a database based on a data backup.

dbmcli on DB1> db_activate recover <template_name> data

More information about DBM command db_activate RECOVER can be reached via SAP MaxDB documentation Overview of All DBM Commands.

back to top

Checking the progress of a restore while it is running

Restoring a bigger backup (e.g. several hundreds of GB) can take some time. As Database Manager CLI does not provide something like a constantly visible progress indicator you may be concerned about the progress of the running restore. To be sure that the restore proceeds it is possible to execute the following DBM command at least twice using a second DBMCLI session while the restore is running:

dbmcli on DB1> recover_state

Within the emerging output you should pay attention to these line:

Pages Transferred

<12345>

Pages Left

<54321>

Errortext

...

Now after both executions the numbers of Pages Transferred (should increase) and Pages Left (should decrease) can be compared.

More information about DBM command recover_state can be reached via SAP MaxDB documentation Overview of All DBM Commands.

back to top

Checking the success of a restore after completion

When the restore data and log have been finished a quick check of the DBM commands (recover_start, autorecover) outputs is recommended. The outputs of a restore data and log look very similar (output of restore data contains two more lines). Example for output of restore log:

Returncode

0

Date

20081106

Time

00104959

Server

<server name>

Database

DB1

Kernel Version

Kernel 7.6.05 Build 011-123-196-300

Pages Transferred

24

Pages Left

0

Medianame

Log

Errortext

...

Label

LOG_000000001

First LOG Page

0

Last LOG Page

4

Database ID

<server name>:DB1_20081104_175515

Max Used Data Page

0

Also the backup history should be checked again for the last recovery. To do this carry on as follows:

dbmcli on DB1> backup_history_open
dbmcli on DB1> backup_history_list -r last -c label,action,pages,stop,media,error

The last filter error would also add the error message if the restore finished with one.

More information about recovery can be reached via SAP MaxDB documentation glossary entry Restore. More information about DBM command recover_start, backup_history_open, backup_history_list can be reached via SAP MaxDB documentation Overview of All DBM Commands.

back to top

Relevant links

HowTo - Creating a Clone of a SAP MaxDB Database
HowTo - SAP MaxDB Backup with Database Manager CLI

SAP MaxDB Documentation
Backup Template (7.9 | 7.8 | 7.7)
Backup Tool (7.9 | 7.8 | 7.7)
Restore (7.9 | 7.8 | 7.7)
Overview of all DBM Commands (7.9 | 7.8 | 7.7)

Relevant SAP Notes

1377148 FAQ: SAP MaxDB Backup / Recovery
1928060 Data backup and recovery with file system backup
129352 FAQ: SAP MaxDB Administration
1014782 FAQ: SAP MaxDB System Copy