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.

Specifying the Database and Dump Device
At a minimum, all dump and load commands must include the name of the database being dumped or loaded.
Compressing a Dump
The dump command lets you compress databases and transaction logs using Backup Server, thereby reducing your space requirements for your archived databases.
Dump History File
SAP ASE maintains the history of successful and failed backups from dump database and dump transaction commands in a dump history file.
Backups for the Dump Configuration Files
Create a backup of SAP ASE configuration files in a specified dump directory.
Performing Cumulative Dumps
The dump database command allows you to perform a cumulative backup, in which you make a copy of all the pages in the database that have been modified since the last full database dump.
Specifying a Remote Backup Server
Use the at backup_server_name clause to send dump and load requests over the network to a Backup Server that is running on another machine.
Remote Dump Host Control
Use the remote access control file to prevent remote dumps and loads, and execution of remote procedure calls (RPCs) from any client or server that is running on unauthorized servers.
Specifying Tape Density, Block Size, and Capacity
In most cases, Backup Server uses a default tape density and block size that are optimal for your operating system; SAP recommends that you use these defaults.
Apply Source Database Attributes to the Target Database
You can apply source database attributes to the target database.
Generate SQL for a Different Target Database
You can generate SQL from the dump history file and dump information to create a new target database has a different name than the source database.
Specifying the Volume Name
Use the with dumpvolume = volume_name option to specify the volume name.
Identifying a Dump
When you dump a database or transaction log, Backup Server creates a default file name for the dump by concatenating aspects of the database name and the date and time of the dump.
Improving Dump or Load Performance
When you start Backup Server, use the -m parameter to improve the performance of the dump and load commands by configuring more shared memory for the Backup Server.
Automatic Physical Database Rearrangement on Load
When loading a dump of a database that had a segregated log and data segment, SAP ASE rearranges the physical layout of the target database to ensure physical separation of the log and data segments.
Specify Additional Dump Devices with the stripe on Clause
Striping allows you to use multiple dump devices for a single dump or load command. Use a separate stripe on clause to specify the name (and, if desired, the characteristics) of each device.
Tape Handling Options
The tape handling options, which appear in the with clause, apply to all devices used for the dump or load.
Dumping and Loading Databases with Password Protection
Protect your database dump from unauthorized loads using the password parameter of the dump database command.
Overriding the Default Message Destination
Backup Server messages inform the operator when to change tape volumes and how the dump or load is progressing. The default destination for these messages, which you can change, depends on whether the operating system offers an operator terminal feature.
Bringing Databases Online with standby_access
Use the with standby_access parameter of dump transaction to dump only completed transactions.
Getting Information About Dump Files
se the with headeronly or with listonly option of the load commands to get information about the contents of a tape.
Copying the Log After a Device Failure
Normally, dump transaction truncates the inactive portion of the log after copying it. Use with no_truncate to copy the log without truncating it.
Responding to Volume Change Requests
On UNIX and PC systems, use sp_volchanged to notify the Backup Server when the correct volumes have been mounted.
Recovering a Database: Step-By-Step Instructions
The symptoms of media failure are as variable as the causes.
Loading Database Dumps from Older Versions
During most upgrades, all databases associated with a server are automatically upgraded. Major upgrade, for example, from version 12.5.x to version 15.5, require that you run the preupgrade and upgrade utilities to complete the upgrade.
Cache Bindings and Loading Databases
If you dump a database and load it onto a server with different cache bindings, you may want to load the database onto a different server for tuning or development work, or you may need to load a database that you dropped from a server whose cache bindings have changed since you made the dump.
Cross-Database Constraints and Loading Databases
If you use the references constraint of create table or alter database to reference tables across databases, you may encounter problems when you try to load a dump of one of these databases.

Related concepts

Database Recovery with the for load Parameter

Related tasks

Using alter database

Loading a Backup of master