Database Restore

When a database is damaged it can be returned to a correct and consistent state with a restore procedure. This involves reloading the most recent database backup and applying succeeding transaction log backups. The best method to restore a database depends on the type of damage that has occurred and on the backups that are available. In an optimal restore operation the database is recovered with minimal downtime and no data loss.

Prerequisites

Since a database restore involves reloading backups, it is impossible to repair a damaged database without backups. A backup strategy that ensures that logs and data are regularly copied to another storage medium is therefore an essential prerequisite for any database restore. A well-planned strategy reduces the risk of data loss and minimizes the downtime required to reconstruct the database.

For more information, seeBackup Strategy.

Process

There are different reasons why errors may occur that make it necessary to restore the database:

• Disk failure

  A disk may crash resulting in the loss of the transaction log or database files.

• User fault

  A user may, for example, unintentionally delete a file or import a wrong transport.

• Database corruption

  In rare cases, a logical error may be discovered in the database.

• Move to different hardware

  A disaster might have destroyed existing hardware or a move to faster hardware might be necessary.

  Note

  After a power failure the database does not have to be restored. When the power is cut off, the system shuts down abruptly and the execution of transactions is interrupted. The SQL Server has an automatic recovery mechanism to deal with this situation. When the database is restarted, an automatic recovery mechanism starts working. This means open transactions are rolled back and completed transactions that were not written to the data files at the time of shutdown are re-executed.

The most effective method to restore a database depends on the type of error that has occurred. Finding out exactly what happened is therefore the first step in any restore process. The following describes different error situations and gives an overview of the steps required to restore the database in these situations.

Restore After Disk Failure

The procedure to follow in order to repair a damaged database after disk failure differs, depending on how files have been allocated to disks and which of these disks have been damaged. This documentation assumes that you have distributed your system to 3 different disk systems. This complies with SAP recommendations that at least the following components of your system should be located on different disks:

• Log files of the SAP database
• Data files of the SAP database
• SAP, SQL Server and Windows executables, and other databases, for example, msdb, master and tempdb.

For more information, seeDisk Configuration.

SAP Data Disk Crash

The SAP database normally resides on a RAID5 storage system for increased protection against data loss. If a single disk fails it simply needs to be replaced and does not disrupt database operation. However, if the whole RAID system crashes, the consequences are serious. The SAP database is damaged, work in the system comes to a standstill and a database restore has to be performed before resuming normal work.

To restore the database after this type of failure, you first have to immediately backup the most recent transaction logs. Once this has been done, you can restore the database by loading the latest database backup and applying the succeeding transaction log backups. For more information, seeRestoring the SAP Database after Disk Crash.

SAP Log Disk Crash

The SAP log records all the changes that are made to the database. They enable transactions to be redone when a database is restored and therefore play a central role in any restore. For this reason, SAP recommends that they be written to a RAID1 storage system that ensures data is protected against loss by a mirroring strategy. The transaction log is only lost, if the original and mirrored log disks fail.

If the entire log disk system crashes, you need to proceed in the same way as for a failure of the SAP data disk. The only difference is that you will not, in a first step, be able to backup the transaction logs that were in use at the time of the failure. You have to begin by restoring the most recent database backup and then continue with the application of the available transaction log backups.

For more information, seeRestoring the SAP Database After Disk Crash.

Executables Disk Crash

If your disk system with all other files except the transaction log and SAP data files fails, this has far reaching consequences for the system. The Windows operating system, SAP executables, SQL server executables, msdb and master databases will be gone.

The best way to rectify the situation is to restore the crashed disk on the basis of the last available full Windows backup.

Restore after User Fault

A user might erroneously delete a table or import a wrong transport. Depending on what has happened, there are different ways in which you can proceed. One of the options that may be useful is a point in time recovery. It can restore the database to the state it had on a specific day at a specific time. Before you implement this procedure, SAP recommends that you first perform a full Windows backup. This safeguards you from losing your data, if the tape for restoring the database is unreadable.

For more information see the SQL Server Books Online.

Restore after Database Corruption

When you recognize that the data in your database is corrupted you need to diagnose the problem more precisely. The optimal method of restoring the integrity of the database depends on the extent and cause of the damage. You probably need assistance from your SAP consultant, in order to analyze the error and find the most effective solution. It may be possible to repair the corrupted database. Other options are:

• Database and transaction log restore
• Point in Time Recovery

Move to Different Hardware

There are two reasons why you may need to restore the entire system on different hardware:

• Total loss of hardware.
• Database copy, for example from a test system to a productive system.