Introduction
The development of a backup and recovery strategy is an exercise carried out in phases. In order to work through these phases we need to understand some database terminology such as, backup and recovery, the difference between a full database backup and an incremental database backup, and the meaning of an online, offline, and live backup.
In this paper we outline the phases in developing a backup and recovery plan and cite an example. We show how time becomes an important factor in database recovery. We also explain what to do when the backed up database fails to restore from your backup medium, and discuss what database health checks should be performed to ensure the database and log files are valid.
An example of database backup and recovery commands is provided to illustrate the statements used to backup and recovery a database. Finally, we discuss key points in developing a disaster recovery strategy in the event the physical machine running your database is no longer available.
Backup, recovery, and disaster recovery
We tend to think of 'backup and recovery' as one topic and 'disaster recovery' as another.>
'Backup' is a utility program used to make a copy of the contents of database files and log files. The database files consist of a database root file, log file, mirror log file, and other database files called dbspaces.
'Recovery' is a sequence of tasks performed to restore a database to some point-in-time. Recovery is performed when either a hardware or media failure occurs. Hardware failure is a physical component failure in your machine, such as, a disk drive, controller card, or power supply. Media failure is the result of unexpected database error when processing data.
Before you begin recovery, it is a good practice to back up the failing database. Backing up the failing database preserves the situation, provides a safe location so files are not accidentally overridden, and if unexpected errors occur during the recovery process Sybase Technical Support may request these files be forwarded to them.
'Disaster recovery' differs from a database recovery scenario because the operating system and all related software must be recovered before any database recovery can begin.
Database files that make up a database
Adaptive Server Anywhere or SQL Anywhere databases consist of disk files that store data. When you create a database either using Sybase Central 'Create Database' or dbinit command-line utility a main database file or root file is created. The default database file is created as database_name.db. This main database file contains database tables, system tables, and indexes.
Additional database files expand the size of the database and are called dbspaces. A dbspace contains tables and indexes, but not system tables. A dbspace can be created using Sybase Central or Interactive SQL by issuing a CREATE DBSPACE command. By default the dbspace file created is in the format of drive:\path\dbspace_name.db. You can find out if your database is using one or more dbspaces by opening the DBSpaces folder in Sybase Central or querying the sys.sysfile system table, which has a row for each dbspace.
A transaction log is a file that records database modifications. Database modifications consist of inserts, updates, deletes, commits, rollbacks, and database schema changes. A transaction log is not required but is recommended. The database engine uses a transaction log to apply any changes made between the most recent checkpoint and the system failure. The checkpoint ensures that all committed transactions are written to disk. Using the database initialization utility called dbinit a log file is created with a default file name of database_name.log. When you use Sybase Central to create a database log file the default displayed will be the full path to the log fill such as drive:\path\database_name.log. During recovery the database engine must find the log file at this location. When the transaction log file is not specifically identified then the database engine presumes that the log file is in the same directory as the database file.
A mirror log is an optional file and has a file extension of .mlg. It is a copy of a transaction log and provides additional protection against the loss of data in the event the transaction log becomes unusable.
Online backup, offline backup, and live backup
Database backups can be performed while the database is being actively accessed (online) or when the database is shutdown (offline). When a database goes through a normal shutdown process (the process is not being cancelled) the database engine commits the data to the database files.
An online database backup is performed by executing DBBACKUP from the command-line or from the Sybase Central 'Backup Database' utility. When an online backup process begins the database engine externalizes all cached data pages kept in memory to the database file(s) on disk. This process is called a checkpoint. The database engine continues recording activity in the transaction log file while the database is being backed up. The log file is backed up after the backup utility finishes backing up the database. The log file contains all of the transactions recorded since the last database backup. For this reason the log file from an online full backup must be 'applied' to the database during recovery. The log file from an offline backup does not have to participate in recovery but it may be used in recovery if a prior database backup is used.
A live backup is carried out by using the DBBACKUP utility with the ?l command-line option. A live backup provides a redundant copy of the transaction log for restart of your system on a secondary machine in the event the primary database server machine becomes unusable.
Full and incremental database backup
A database backup is either a full or incremental backup. For a full backup, the database backup utility called DBBACKUP copies the database and log. An incremental backup uses the DBBACKUP utility to copy the transaction log file since the most recent full backup. When you perform an incremental backup the mirror log is not backed up. When you backup and rename the log files the transaction and mirror log file is renamed and new log files are created. You must plan to manually back up the mirror log. Be aware of this when planning out your backup and recovery strategy.
Developing a backup and recovery strategy
The steps suggested in the development of a backup and recovery strategy consist of the following:
Understand what backup and recovery means to your business
Management commits time and resources for the project
Develop, test, time, document, health check, deploy, and monitor
Beware of any external factors that affect recovery
Address secondary backup issues.
Understand what backup and recovery means to your business
How long can my business survive without access to the corporate data?Express your answer in terms of minutes, hours, or days.
If your recovery time is in minutes then database backup and recovery is critical to your business needs and it is paramount that you implement some kind of backup and recovery strategy. If recovery can take hours then you have more time to perform the tasks. If recovery can be expressed in terms of days then the urgency to recover the database still exists, but time appears to be less of a factor.
Management commits time and resources for the project
Management must decide to commit financial resources towards the development and implementation of a backup and recovery strategy. The strategy can be basic or quite extensive depending upon the business needs of the company. After developing a backup and recovery strategy management should be informed of the expected backup and recovery times. Anticipate management countering the timings by preparing alternative solutions. These alternative solutions could be requesting additional hardware, improved backup medium, altering backup schedule, accepting a longer recovery time versus backup time. Then it will be up to management to decide what solution fits their corporate needs.
Develop, test, time, document, health check, deploy, and monitor
These phases are the core in developing a backup and recovery strategy:
- Create backup and recovery commands. Verify these commands work as designed. Does your full or incremental online backup work? Verify that your commands product the desired results.
- Time estimates from executing backup and recovery commands help to get a feel for how long will these tasks take. Use this information to identify what commands will be executed and when.
- Document the backup commands and create written procedures outlining where your backups are kept and identify the naming convention used as well as the kind of backups performed. This information can be very important when an individual must check the backups or perform a database recovery and the data base administrator (DBA) is not available.
- Incorporate health checks into the backup procedures. You should check the database to ensure the database is not corrupt. You can perform a database health check prior to backing up a database or on a copy of the database from your backup.
- Deployment of your backup and recovery consists of setting up your backup procedures on the production server. Verify the necessary hardware is in place and any other supporting software necessary to perform these tasks. Modify procedures to reflect the change in environment. Change user id, password, server and database name to reflect the change in environment.
- Monitor backup procedures to avoid unexpected errors. Make sure any changes in the process are reflected in the documentation.
Beware of external factors that affect recovery
External factors that effect database recovery are time, hardware, and software. Allow additional recovery time for entering miscellaneous tasks that must be performed. These tasks could be as simple as entering recovery commands or retrieving and loading tapes. Factors that influence time are the size of database files, recovery medium, disk space, and unexpected errors. If you use a DBSPACE with your database and it fails to be restored from a backup then the backup is not valid. The more files you add into the recovery scenario increases the places where recovery can fail. Let's say you use four DBSPACES main database file, a mirror, and transaction log file. If the one of the log files fails to restore from a tape backup you have the other log file to use. If a DBSPACE fails to restore from your backup to disk then you must revert to a prior full database backup. As the backup and recovery strategy develops it may be necessary to check the performance of the equipment and software ensuring it meets your expectations.
Protect database backups by performing health checks
Database health checks are run against the database and log files to ensure they are not corrupt. The database validity utility called DBVALID is used to scan 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 recovery from your previous database backup. A database can be validated before being backed up or against a copy of the database from your backup.
DBVALID can take time to check the database. You will need to decide when to run DBVALID. A database validity check done before running a full database backup would ensure the database is free of database pointer errors before being backed up. When you run DBVALID against a restored copy from a back up medium you will be accomplishing the following checks:
- You will verify the database can be successfully restored from the backup medium
- You will be able to run you database validity check against the database without impacting your production environment
Run DBVALID on a non-production machine to avoid impacting your production environment. The database used in the validation process cannot participate in recovery since log offsets will be changed once the database is started.
Log files are checked for corruption by using the DBTRAN utility. The DBTRAN utility translates a log file into a SQL file and verifies the log file is free of corruption. This is important to keep in mind when you have to recover a large database and apply several log files. If during a database recovery a log file is found to be corrupt then your recovery stops and you must determine if you can manually recover from that point to the time of failure. If you have a backed up mirror log you have the option of replacing the corrupt transaction log with the contents of the backup mirror log and apply the log file. An alternative approach, if the log file was previously translated then the translated SQL file could be read into the database along with the remaining translated SQL files. Beware that reading the SQL file(s) into the database in lieu of applying log file(s) is a recovery alternative, but it cannot be used when the database participates in replication.
Backup and recovery strategy example
Let's develop a backup and recovery strategy for a fictitious company called 'Data Online'. Data Online requires access to corporate data stored in this database during business hours. Business hours are defined as a time period from Monday through Friday between the hours of 8AM to 8PM. During business hours Data Online cannot be without this information for more than 2 hours. Access to the corporate data outside of business hours is not critical.
The database is not being backed up. The size of the database is 600 MB consisting of a main database file of 400 MB with two 100 MB database spaces each containing several tables and indexes. The database is using a transaction and mirror log. Daily business activities will cause the log files to grow at a daily rate of 80MB. We will now be able to develop a base line for the time it will take to backup and recover the database. The timings are fictitious since they will vary depending upon the machine, size of database, and additional software running on the machine.
The name of the database backup utility is DBBACKUP. It can be used to backup database and log files. A full database backup using DBBACKUP will make a copy of the main database file, database spaces, and transaction log file.
Time the basic backup and recovery operations
After running several full database backup tests, we calculated the average backup time to be 30 minutes. Incremental backups should also rename and restart the log files to "prune" their growth. We now run several test incremental backups where we are only backing up the changes made to the database by backing up the transaction and mirror log file. After performing several backup tests we calculate the average incremental backup time to be 10 minutes for a log file size of 40 MB.
Data Online has a third party software tool to backup disk files tape. All database backups will be copied onto tape. We calculate the average database and log backup and restore from tape to be 15 minutes and 5 minutes respectively for an incremental database backup. Now we can use these timings to develop our recovery strategy.
Estimate a total recovery time from failure
The times estimated in the previous section give us a way to estimate what our recovery time will be given the following conditions.
- If we need to recover the database from a backup it will take 15 minutes to restore the database from tape to disk
- Then for each day's two incremental database backups add an additional 10 minutes (5 minutes to restore each log file from tape).
- We presume the current database transaction log file is not corrupt and can be applied the database. Recovery process of applying a log to the restored database takes approximately 5 minutes.
- An estimated recovery time is calculated to be a total of 55 minutes, as shown below.
- Recovery from backup medium phase
| Operation | Time |
| Full database backup | 15 minutes |
| Incremental database backup | 10 minutes (2 @ 5 minutes each) |
| Subtotal time for backup medium | 25 minutes |
Database recovery phase (applying incremental database backups to recovered database)
| Operation | Time |
| Apply 3 log files | 15 minutes (3 @ 5 minutes each) |
| Issuing commands | 15 minutes |
| Subtotal time for database recovery | 30 minutes |
Total recovery time: 55 minutes
This recovery example indicates that it takes approximately one hour to recover the database and apply 3 incremental backups. A full database backup from the night before along with the next day's two incremental backups will recover the database to the end of the next business day and meet our business objective of database recovery within 2 hours. Therefore, a full database backup will be performed nightly and during each business day two incremental database backups will be done at noon and the other at 6 PM. We suggest that the database backups be retained on tape for 5 weeks.
We now have a backup and recovery strategy to present to our management.
What do you do when you encounter errors during recovery?
During the restore process of the full database backup a database space fails to restore from tape. This backup copy of the database is now invalid. Identify the next available full database backup and the incremental backups needed to recover the database to the original point of failure. We have the full database backup of the previous night to begin the recovery process. Use the previous day's full database backup to begin the recovery process. Recovery time would add an additional 25 minutes to restore the previous day's files. It would also add an additional 60 minutes to apply 6 log files (4 incremental database backups and 2 transaction log files accompanying the previous day's full database backup and the failed full database backup).
Recovery from previous day's backup
Recovery from medium phase. First attempt fails to successfully restore from tape a full database backup
| Operation | Time |
| Full database backup | 15 minutes |
| Incremental database backup | 10 minutes (2 @ 5 minutes each) |
| Subtotal time for backup medium | 25 minutes |
Second attempt successfully restoring from tape the previous day's full database backup
| Operation | Time |
| Full database backup | 15 minutes |
| Incremental database backup | 10 minutes (2 @ 5 minutes each) |
| Subtotal time for backup medium | 25 minutes |
Database recovery phase (applying incremental database backups to recovered database)
| Operation | Time |
| Apply 6 logs to the recovered database | 30 minutes |
| Issuing commands | 30 minutes |
Total recovery time: 1 hour and 50 minutes
This second scenario illustrates the need to be ready for the unexpected. Every database recovery will be different from the previous one. If one of the database files from a full database backup is bad verify the log files are valid. If so, then at least you can recover from a previous day's full database backup and recover past this point of failure by applying the log.
Additional Information regarding recovery
Before doing any kind of database recovery you should backup your failing database. If you feel uncomfortable or unsure of what to do, then it's time to contact Sybase Technical Support. Even if you have come up with a way to recover the database, it is worth getting a second opinion and to have your recovery plans verified by Sybase Technical Support.
It should be stressed that multiple full database backups allow for multiple fail safe points of recovery. Run DBTRAN against each incremental backup to verify the log is valid. Do not replace the applying a log file with the translated SQL file if your system participates in replication. This alternative approach will break your replication. If you do use a translated SQL file then all remaining log files must be translated and read into the database. This alternative recovery will take longer than applying the log files.
Secondary backup issues
Define a backup retention timeframe for your backups.
If your backup medium is tape then decide the number of times they
will be used before discarding.
Test your backup procedures by restoring from them to ensure they
can be restored.
Make up different recovery scenarios and try to recover the
database from them.
Verify your recovery procedures are kept current.
Backup and recovery commands
The database server used in this section is Adaptive Server Anywhere 6.0. If SQL Anywhere Version 5.x is your current database version of software then replace the personal server dbeng6 with dbeng50 or the network server dbsrv6 with dbsrv50. Similarly, If you are using Adaptive Server Anywhere 7.0 or 8.0, replace dbeng6/dbsrv6 with dbeng7/dbsrv7 or dbeng8/dbsrv8 respectively. The path in the following examples is not indicated since you will either use the installation default or change the path accordingly. The other utilities are the same between database versions unless specified.
Test your commands
It is not intended for these commands to be copied as is into a backup and recovery script. Always, test, test, and test your commands before deploying them.
Directories used
Some file directories we will use in this example
|
C:\prod (production database directory) |
|
|
C:\bkdb (failing database directory) |
|
|
D:\rcv (recovery database directory) |
|
|
D:\dbbkup (full database backup) |
|
|
D:\lgbkup (incremental log backup) |
|
| \HHhhhhh0800 (backup at 8:00 A.M.) | |
| \HHhhhhh1200 (backup at 12:00 A.M.) | |
| \HHhhhhh1700 (backup at 5:00 P.M.) | |
In our example, the database has been mounted to a personal server.
DBENG6 -n server C:\prod\database.db
Creating backups
Create a full online backup of the database and log file
DBBACKUP -c "uid=dba;pwd=sql;eng=server;dbn=database" D:\dbbkup
An incremental online backup of the transaction log file and the
log file is truncated at 8:00 A.M.
DBBACKUP -t -x -c "uid=dba;pwd=sql;eng=server;dbn=database"
D:\lgbk\h0800
An incremental online backup of the transaction log file and the
log file is truncated at 12:00 A.M.
DBBACKUP -t -x -c "uid=dba;pwd=sql;eng=server;dbn=database" D:lgbk\h1200
An incremental online backup of the transaction log file and the
log file is truncated at 8:00 A.M.
DBBACKUP -t -x -c "uid=dba;pwd=sql;eng=server;dbn=database"
D:\lgbk\h1700
Recovering backups
To recover the database from the full backup and apply all of the incremental backups (transaction logs) and log file at the time of failure to recover the database to a current point in time:
-
Create backup copy of the failing database and log to c:\bkdb.
-
Create a recovery database d:\rcv.
-
Copy only the database from the full backup to d:\rcv.
Start full database backup applying the log file using the ?a (apply log) switch. Monitor the server window for error messages. You should see a message indicating that recovery is in progress.
DBENG6 D:\rcv\database.db -a D:\dbbkup\database.log
Apply the remaining log files
DBENG6 D:\rcv\database.db -a D:\lgbk\h0800\database.log
DBENG6 D:\rcv\database.db -a D:\lgbk\h1200\database.log
DBENG6 D:\rcv\database.db -a D:\lgbk\h1700\database.log
Now apply the log file at the time of the database failure
DBENG6 D:\rcv\database.db -a D:\bkdb\database.log
Only copy the database file to the production directory. A new log file will be created. Now you are ready to perform health checks against the database.
Perform health checks
Now start the database using the personal server (dbeng6) or stand alone database engine (dbeng50). We do this is to ensure to users will be able to connect to the database until we are certain it is valid.
The database is mounted to a personal server.
DBENG6 -n server C:\prod\database.db
Verify that the database is not corrupt by running DBVALID.
DBVALID -c "uid=dba;pwd=sql;eng=server;dbn=database"
Create a post-recovery backup
Clear out old copies of the database in the backup directory. You may want to backup the files to some backup medium.
Create a post-recovery backup of the database and log file
DBBACKUP -c "uid=dba;pwd=sql;eng=server;dbn=database" D:\dbbkup
Allow user access the database
At this time we now want to shutdown the database because we used the stand alone or personal server database engine to perform health checks, verify the database was valid, and post-recovery of the database. Start the database engine in the normal manner prior to the recovery. Monitor the database engine and user activity against the database for unexpected error messages.
Disaster recovery
Disaster recovery is different from a database recovery because it usually implies the machine is no longer available. This may be the result of a flood, natural disaster, or an inoperable machine. This type of situation requires the operating system, system software, database software, and application software recovery onto a physically different machine. The machines may be similar or identical. If the machine is different this could impact your disaster recovery. After system and database software is installed then database recovery procedures can be started.
Additional Information regarding disaster recovery planning
The following list is an example of what information is needed in developing a disaster recovery strategy. You should review your strategy and update it accordingly if you have omitted any of these points of interest. This list is only representative of may be required and can vary based upon each site's configuration of hardware, software, location, and personnel.
- Establish an offsite location with compatible system and network hardware.
- Establish procedures for retrieving offsite backups.
- Disaster recovery procedures should be kept in multiple places. At the offsite location with the backups for the operating system, system software, database, and application software as well as with individuals involved with disaster recovery.
- Schedule regular disaster recovery tests and after each test review the outcome.
- Verify the disaster recovery procedures contain copies of all installation software and their passwords for the operating system, system software, database, and application software.
- Attach a current list of all software vendor names, phone numbers, product support plan numbers, and software license information to the disaster recovery procedures.
- Record the total time to complete your disaster recovery.
- Keep the disaster recovery procedures current and update the copies kept offsite.
Summary
The material presented here provides background information in your development and implementation of a database backup and recovery strategy. We have identified the files used in a database and what is the difference between an online, offline, and live backups. We stressed the importance of incorporating health checks into the backup and recovery strategy to safeguard your investment. A backup and recovery example is given showing factors involved in the development of a backup and recovery strategy. We discuss what is involved with disaster recovery. Disaster recovery should not be overlooked. An accident to your equipment can happen causing your server and database to become inoperable. What we also want to stress is to test, test, and test your backup and recovery procedures. Assume nothing and verify everything works as expected. We hope this discussion provides you with the necessary information to assist in your development and implementation of a backup and recovery and disaster recovery strategy.
Additional information regarding backup and recovery can be found in the Adaptive Server Anywhere User's Guide and Sybase SQL Anywhere User's Guide Volume I in the chapter titled "Backup and Data Recovery".