User's Guide Part III. Using SQL Anywhere Chapter 25. Backup and Data RecoveryA full backup makes a copy of the database file and (optionally) a copy of the transaction log. Full backups are described in "Performing a full backup".
An incremental backup makes a copy of the transaction log.
Both full and incremental backups can be carried out online or offline. You can use any means of backing up the files onto diskette, magnetic tape, optical disk, or any other device. Incremental backups are described in "Performing an incremental backup".
Backups can be made without stopping the database engine. The backup utility can be run against a standalone engine or network database server. Using the backup utility on a running database is equivalent to copying the database files when the database is not running. In other words, it provides a snapshot of a consistent database, even while it is being modified by other users.
For a full description of the online backup facility, see "The Backup utility".
The database engine should not be running when you do offline backups by copying database files. Moreover, it should be taken down cleanly.
If you are running a multiuser database, you can use the database server -t command line option to shut down at a specified time. This way, you can have your offline backup procedure start late at night automatically.
Before doing a full backup, it is a good idea to verify that the database file is not corrupt. File system errors or software errors (bugs) in any software you are running on your machine could corrupt a small portion of the database file without you ever knowing.
With the database engine running on the database you want to check, execute the validation utility that comes with SQL Anywhere. For example, you could run the DBVALID command-line utility:
dbvalid -c "uid=dba;pwd=sql"
You can also run the validation utility from Sybase Central or ISQL.
The validation utility scans every record in every table and looks up each record in each index on the table. If the database file is corrupt, you need to recover from your previous backup.
For more information on running the validation utility, see "The Validation utility".
A full backup is completed offline by copying the database file(s) and optionally the transaction log to the backup media. A full backup should be completed according to a regular schedule that you follow carefully. Once a week works well for most situations.
To do a backup while the database engine is running, you use the backup utility. You require DBA authority in order to run the backup utility on a database. The backup utility can be run from Sybase Central, ISQL, or using the DBBACKUP command-line utility.
For more information, see "The Backup utility".
For example, you could carry out a full backup of the sample database, held in C:\SQLANY50\SADEMO.DB, to a directory E:\BACKUP, using user ID dba , and passwordsql .
To complete a full backup:
dbbackup -c "uid=dba;pwd=sql;dbf=c:\sqlany50\sademo.db" e:\backup
As neither of -d or -t is specified, both the database files and transaction log are backed up.
Whenever the database file is backed up, the transaction log can be archived and/or deleted (use the Erase utility). Provided the backup can be restored, you will never need the transaction log. Archiving transaction logs provides you with a history of all changes to your database and also provides protection if you are unable to restore the most recent full backup. The backup utility has command line options to delete and restart the transaction log (DBBACKUP -x) or backup and restart the transaction log (DBBACKUP -r) while the database engine is running.
You should keep several previous full backups. If you back up on top of the previous backup, and you get a media failure in the middle of the backup, you are left with no backup at all. You should also keep some of your full backups offsite to protect against fire, flood, earthquake, theft, or vandalism.
If your transaction log tends to grow to an unmanageable size between full backups, you should consider getting a larger storage device or doing full backups more frequently.
An incremental backup is a copy of the transaction log. The transaction log has all changes since the most recent full backup.
You can carry out an offline incremental backup by making a copy of the transaction log file. Alternatively, you can carry out an online incremental backup by running the backup utility and backing up just the transaction log. You can do this from the command line using the DBBACKUP utility with the -t switch, or you can use the backup utility from Sybase Central or ISQL. You require DBA authority in order to run the backup utility on a database file.
For example, you could carry out an incremental backup of the sample database, held in C:\SQLANY50\SADEMO\DB, to a directory E:\BACKUP, with user IDdba , and passwordsql .
To complete an incremental backup:
dbbackup -c "uid=dba;pwd=sql; dbf=c:\sqlany50\sademo.db" -t e:\backup
You should back up the transaction log daily. This is particularly important if you have the transaction log on the same device as the database file. If you get a media failure, you could lose both files. By doing daily backups of the transaction log, you will never lose more than one day of changes.
Daily backups of the transaction log are also recommended when the transaction log tends to grow to an unmanageable size between full backups and you do not want to get a larger storage device or do more frequent full backups. In this case, you can choose to archive and delete the transaction log.
There is a drawback to deleting the transaction log after a daily backup. If you have media failure on the database file, there will be several transaction logs since the last full backup. Each of the transaction logs needs to be applied in sequence to bring the database up to date.
For a description of how to do this, see "Media failure on the database file".