RESTORE DATABASE statement

Use this statement to restore a backed up database from an archive.

Syntax 
RESTORE DATABASE filename
FROM archive-root
[ CATALOG ONLY 
   | [ RENAME dbspace-name TO new-dbspace-name ] ... ]
[ HISTORY { ON | OFF } ]

filename : string | variable 
archive-root : string | variable 
new-dbspace-name : string | variable
Parameters

  • CATALOG ONLY clause   Retrieves information about the named archive, and places it in the backup history file (backup.syb), but does not restore any data from the archive.

  • RENAME clause   Allows you to specify a new location for each dbspace. You cannot use the RENAME clause to change the dbspace name. However, you can use the RENAME clause to change the file name.

  • HISTORY clause   Allows you to control whether the RESTORE DATABASE operation is recorded in the history file, backup.syb.

Remarks

Unless HISTORY OFF is specified, each RESTORE DATABASE operation updates a backup history file called backup.syb. This file records the BACKUP and RESTORE operations that have been performed on a database server. You may want to prevent the RESTORE DATABASE operation from being recorded in backup.syb if the following conditions apply:

  • your RESTORE DATABASE operations occur frequently

  • there is no procedure to periodically archive or delete the backup.syb file

  • disk space is very limited

RESTORE DATABASE replaces the database that is being restored. If you need incremental backups, use the image format of the BACKUP command and save only the transaction log; however, image backups to tape are not supported.

Permissions

The permissions required to execute this statement are set on the server command line, using the -gu option. The default setting is to require DBA authority. See -gu server option.

This statement is not supported on Windows Mobile.

Side effects

None.

See also
Standards and compatibility

  • SQL/2003   Vendor extension.

  • Windows Mobile   Not supported on Windows Mobile.

Example

The following example restores a database from a tape drive. The number of backslashes that are required depends on which database you are connected to when you execute RESTORE DATABASE. The database affects the setting of the escape_character option. It is normally set to On, but is set to Off in utility_db. When connected to any database other than utility_db, the extra backslashes are required. 

RESTORE DATABASE 'd:\\dbhome\\mydatabase.db'
FROM '\\\\.\\tape0';
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1