Backing Up and Restoring User Databases

Regular and frequent backups are your only protection against database damage that results from database-device failure.

If the Tivoli Storage Manager (TSM) is supported at your site, see also Using Backup Server with IBM Tivoli Storage Manager. However, most of the syntax and usage information in this chapter is relevant to sites supporting TSM.

The dump database, dump transaction, load database, and load transaction commands have parallel syntax. Routine dumps and loads require the name of a database and at least one dump device. The commands can also include these options:
  • compression= to compress your dump files to a local file

  • at server_name to specify the remote Backup Server

  • density, blocksize, and capacity to specify tape storage characteristics

  • dumpvolume to specify the volume name of the ANSI tape label

  • file = file_name to specify the name of the file to dump to or load from

  • stripe on stripe_device to specify additional dump devices

  • dismount, unload, init, and retaindays to specify tape handling

  • notify to specify whether Backup Server messages are sent to the client that initiated the dump or load, or to the operator_console

Note: When you dump a user database, its database options are not dumped because they are stored in the sysdatabases table of the master databases. This is not a problem if you load a previously dumped database onto itself, because rows in sysdatabases describing this database still exist in master. However, if you drop the database before you perform the load database, or if you load the database dump on a new server, these database options are not restored. To restore the image of a user database, you must also re-create the database options.

Use dump transaction with no_log if there is insufficient free space on the device to successfully a dump transaction or dump transaction with truncate_only command.

See the Reference Manual: Commands.

dump and load database display the percentage completed while they run. dump database displays the percentage completed for the database you are dumping, and load database displays the percentage loaded for the target database.

Note: The dump and load transaction commands do not display the percent, completed.
For example, if you dump the sybsystemprocs database to a file named pubs2.dump:
dump database sybsystemprocs to "pubs2.dump"
Backup Server session id is:  13.  Use this value when executing the
'sp_volchanged' system stored procedure after fulfilling any volume change
request from the Backup Server.Backup Server: 4.41.1.1: Creating new disk file
/linuxkernel_eng3/Pubs/REL1502/ASE-15_0/bin/pubs2.dump.
Backup Server: 6.28.1.1: Dumpfile name 'pubs20805209785  ' section number 1
mounted on disk file '/linuxkernel_eng3/Pubs/REL1502/ASE-15_0/bin/pubs2.dump'
Backup Server: 4.188.1.1: Database pubs2: 876 kilobytes (46%) DUMPED.
Backup Server: 4.188.1.1: Database pubs2: 1122 kilobytes (100%) DUMPED.
Backup Server: 3.43.1.1: Dump phase number 1 completed.
Backup Server: 3.43.1.1: Dump phase number 2 completed.
Backup Server: 3.43.1.1: Dump phase number 3 completed.
Backup Server: 4.188.1.1: Database pubs2: 1130 kilobytes (100%) DUMPED.
Backup Server: 3.42.1.1: DUMP is complete (database pubs2).
When you load pubs2.dump into a database:
load database pubs2 from “pubs2.dump”
Backup Server session id is:  17.  Use this value when executing the
'sp_volchanged' system stored procedure after fulfilling any volume change
request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'pubs20805209785  ' section number 1
mounted on disk file '/linuxkernel_eng3/Pubs/REL1502/ASE-15_0/bin/pubs2.dump'
Backup Server: 4.188.1.1: Database pubs2: 1880 kilobytes (45%) LOADED.
Backup Server: 4.188.1.1: Database pubs2: 4102 kilobytes (100%) LOADED.
Backup Server: 4.188.1.1: Database pubs2: 4110 kilobytes (100%) LOADED.
Backup Server: 3.42.1.1: LOAD is complete (database pubs2).
Started estimating recovery log boundaries for database 'pubs2'.
Database 'pubs2', checkpoint=(1503, 22), first=(1503, 22), last=(1503, 22).
Completed estimating recovery log boundaries for database 'pubs2'.
Started ANALYSIS pass for database 'pubs2'.
Completed ANALYSIS pass for database 'pubs2'.
Started REDO pass for database 'pubs2'. The total number of log records to
process is 1.
Completed REDO pass for database 'pubs2'.
Use the ONLINE DATABASE command to bring this database online; ASE will not
bring it online automatically.

For dump database, the percentages that appear are estimates that depend on the total size of the database being dumped. However, for load database, the percentages that appear are estimated according to the total size of the receiving database. For example, if you load a 500MB database dump into a 100 megabyte database, the completed percentage values are estimated according to the 100MB database, not the 50MB dump.

The remainder of this chapter provides greater detail about the information specified in dump and load commands and volume change messages.

Related concepts
Database Recovery with the for load Parameter
Related tasks
Using alter database
Loading a Backup of master