Load database - restoring a database

Command used to restore the database i LOAD DATABASE. Not every user can issue that command, actually only a very narrow group of them: sa (system administrator) and dbo (database owner). The load database will overwrite the content of an existing database and is considered as very destructive command. If the incorrect dump would be loaded than a real problem will appear...

Generally options used in the LOAD command are the same as in the DUMP described here, however just after the load, the database will be in offline state. To have it again on online state the ONLINE DATABASE command will need to be issued:

LOAD DATABASE ALFA  from '/home/dumps/01_normal.dmp'
GO
ONLINE DATABASE ALFA
GO

To verify the status of database issue command:

sp_helpdb

If the database would be in offline state you will see such a description in status column.

Only database that is not in use by any user may be restored. If any user's session is still active in the database following error will be recorded:


Could not execute statement.

Database in use.  A user with System Administrator (SA) role must have
exclusive use of database to run load.
SQLCODE=3101
Server=SAP01, Severity Level=16, State=1, Transaction State=0, Line=35
Line 1

In this casse dba needs to find who is using the database. Procedure sp_who and kill can be useful here. First execute 

sp_who

In the result returned by the procedure look at sessions having the database name equal to the database you are going to restore and note the value from spid column.  Next terminate those sessions like sending the session number to the command like this:

kill 87

Backups can be restored from local dump devices, local tapes, local files but also from remote backup servers like this:

LOAD DATABASE ALFA  from '/home/dumps/02_remote.dmp' AT SAP01_BS
GO
ONLINE DATABASE ALFA
GO

If the backup was stripped between multiple files than the restore command takes form:

DUMP DATABASE ALFA TO '/home/dumps/01_stripped_01.dmp'
STRIPE ON '/home/dumps/01_stripped_02.dmp'
GO
ONLINE DATABASE ALFA
GO

If the backup was created with VERIFY=crc parameter than it is possible to verify the correctness of the backup during the restore operation:

LOAD DATABASE ALFA  from '/home/dumps/01_normal.dmp' WITH  VERIFY=crc
GO
ONLINE DATABASE ALFA
GO

If the VERIFY=crc was not specified during DUMP database was not used, and you will use it during the restore... neither error nor warning will be displayed. To be honest - validating the backup during the restore is a risky operation. What should be the plan if the backup is not valid and contains damaged pages? So a better idea could be first to perform a validation and if it is successful than do the restore. To only validate the backup file you can use the option VERIFYONLY:

LOAD DATABASE ALFA  from '/home/dumps/01_normal.dmp' WITH  VERIFYONLY=crc

This command will not restore the database, but will read the dump file and validate every page in backup file, so you can be sure it is not damaged and you can continue to database restore.

If the backup was secured with the password, than the restore should also specify the same password:

DUMP DATABASE ALFA TO '/home/dumps/01_secret.dmp' WITH PASSWD='secret'

If the password was omitted or incorrect than following error will be recorded:

Could not execute statement.

Dump is password-protected, a valid password is required.

SQLCODE=3025

Server=SAP01, Severity Level=16, State=12, Transaction State=0, Line=59

Line 7

Additionally also following rules must be fulfilled:

• database must already exists on the destination server (it can have different name than the original database)
• database must be at least so big as the database originally backed up
• during the restore not only used pages are read from a backup file, but additionally all the empty pages are also initialized. This can take some time, and this is the reason why the restore can take longer than the backup