5. Backing up and Restoring a Database

Creating a backup of your data is a simple, essential component of any serious installation. Adaptive Server Anywhere includes utilities to help minimize data loss in case your data becomes corrupt as a result of media failure, power outage, or other failure.

5.1. Creating a Backup of the Database

Backups of Adaptive Server Anywhere databases can be performed through the dbbackup command line utility, SQL, or Sybase Central. Both full backups and incremental backups can be performed, and they can be performed either online or offline (that is, whether the server is running or not, respectively). In addition, backups can be performed both from the server side and from the client side.

5.1.1. Full vs. Incremental Backups

A full backup makes copies of the main database file and the transaction log file. While it's the most basic and essential type of backup, it usually isn't practical to regularly perform full backups of large databases. As a result, incremental backups are commonly used.

An incremental backup makes a copy of the transaction log alone. It takes place as part of a cycle that begins with a full backup, which is then followed by a given number of incremental backups. Since only the transaction log is copied, an incremental backup uses less time and resources, making it particularly suited for large databases. Keep in mind, though, that the more time you leave between full backups, the greater the risk of losing data in the event that one of the transaction logs becomes unusable.

5.1.2. Online vs. Offline Backups

An online backup is performed without stopping the database server. It provides a consistent snapshot of the database, even as the database is modified. Online backups are useful for databases with high availability requirements, but they won't complete until all active transactions are complete.

In contrast, offline backups are performed once the database server has been shut down. They're useful for when the database can be taken down on a regular basis. You make offline backups simply by copying the pertinent files to another location using the cp command in a terminal window.

In either case, both full and incremental backups can be performed.

5.1.3. Server-side vs. Client-side Backups

An online backup can be performed from a client using the dbbackup command line utility. This is known as a client-side backup, and it puts a backup of the database on the client machine.

An online backup can also be performed on the server by issuing the BACKUP statement in SQL. Server-side backups are generally faster, owing to the fact that client-side backups usually depend upon transport across networks.

5.1.4. How to make a backup

5.1.4.1. From the command line

The command line utility for making a backup of your database is dbbackup. Its syntax is as follows:

dbbackup [ switches ] directory

directory specifies a destination directory for the backup files. Some useful switches include the following:

-c is used to specify a connection string to the database to be backed up
-d creates a backup of the main database file only
-t creates a backup of the transaction log only
-r renames any previous transaction log backups and creates a new one. It is necessary for replication systems.
-x deletes any previous transaction log backups and creates a new one. It should not be used in replication systems.

For example, if you were creating your first backup, you would want to create a full backup of MyDatabase. To put it in ./backups, use the following command:

dbbackup -c "uid=DBA;pwd=SQL;dbn=MyDatabase" ./backups

The next few backups could be incremental backups, so use the following:

dbbackup -t -r -c "uid=DBA;pwd=SQL;dbn=MyDatabase" ./backups

5.1.4.2. From SQL

If you prefer to back up your database from Interactive SQL, the SQL statement is BACKUP DATABASE. You must have DBA authority to use BACKUP DATABASE, whose syntax is as follows:

BACKUP DATABASE DIRECTORY backup-directory
  [ WAIT BEFORE START ]
  [ DBFILE ONLY ]
  [ TRANSACTION LOG ONLY ]
  [ TRANSACTION LOG RENAME [ MATCH ] ]
  [ TRANSACTION LOG TRUNCATE ]

5.1.4.3. From Sybase Central

To make a backup from Sybase Central, open the Utilities folder under "Adaptive Server Anywhere 7" and double-click "Backup Database" to open a dialog box which will guide you through the backup process.

5.2. Validating the database and its backup

You should regularly use either Sybase Central, SQL, or the dbvalid command line utility to validate a backup of your database in read-only mode, and, if errors are found, make repairs against the original database. Never make changes to a backup database! To read more about validation, see "Validating a database" and "Validating a transaction log" under the Backup and Data Recovery chapter of the Adaptive Server Anywhere User's Guide.

5.3. Recovering the database

Depending on the way your database and its backups are set up, and the status of your files after a media failure, there are several possible processes involved in how you go about recovering data. For information on how to recover data in various situations, see the Backup and Data Recovery chapter of the Adaptive Server Anywhere User's Guide.