User's Guide
PART 4. Database Administration and Advanced Use
CHAPTER 20. Backup and Data Recovery
Client-side only For more information, see BACKUP statement. |
There are two kinds of backups:
A full backup makes a copy of the database file and (optionally) a copy of the transaction log.
An incremental backup makes a copy of the transaction log.
Both full and incremental backups can be carried out online (while the database is running) or offline.
Backups can be made without stopping the 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 though the database is being modified by other users.
For a full description of the online backup facility, see The Backup utility.
The server 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 multi-user 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.
Performing a full backupBefore doing a full backup, it is a good idea to verify that the database file is not corrupt. File system errors, or errors in any software that you are running on your machine, could corrupt a small portion of the database file without you ever knowing.
With the database you want to check running on a server, execute the Validation utility. 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 Interactive SQL.
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 per week works well for many situations.
To do a backup while the server 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, Interactive SQL, 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 path\asademo.db, to a directory e:\backup, using user ID DBA, and password SQL.
To complete a full backup:Enter the following command line:
dbbackup -c "uid=dba;pwd=sql;dbf=path\asademo.db" e:\backup
where path is the name of your Adaptive Server Anywhere installation directory.
As neither -d nor -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 (with the Erase utility). If 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 back up and restart the transaction log (dbbackup -r) while the server 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.
Performing an incremental backupAn 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 Interactive SQL. 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 path\asademo.db, to a directory e:\backup, with user ID DBA, and password SQL.
To complete an incremental backup:Enter the following command line:
dbbackup -c "uid=dba;pwd=sql; dbf=path\asademo.db" -t e:\backup
where path is the name of your Adaptive Server Anywhere installation directory.
If you are running a server that holds critical information, 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 if 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.