Article

Automating Large Sybase Backups

Matthew Cheek

To satisfy the voracious resource appetite of one of our database applications, we recently migrated it to the latest Digital Alphaserver running Digital UNIX (formerly OSF/1). Unfortunately, the vendor responsible for our standard backup tool doesn't support this platform. Moreover, the Sybase data files are written to raw devices, so the standard UNIX backup utilities weren't useful. We also needed a system that would automatically manage a tape carousel, because the backup needed to run unattended and would span multiple tapes. Thus, we were forced to design a custom backup application. By using Sybase facilities and tape utilities from the carousel vendor, we were able to solve our backup problems with some relatively straightforward scripts.

The Design

The development of a custom backup script was necessary because of the following factors:

The large volume of data to be backed up (approx. 25 gigabytes) will not fit on a single tape.

The solution must provide for reasonable database growth.

The database objects reside on raw (character) devices that cannot be backed up with standard UNIX tools (tar, cpio, dump).

The procedure must operate unattended via the cron facility, because the system is located in a minimally staffed, geographically distant location.

The system is a 7 x 24 application and cannot be taken down for "off-line" backups.

The backup must be completed in a limited time (a four-hour window each night).

The solution must be Sybase supported.

After evaluating the requirements of the backup procedure, the following resources were identified:

Sybase provides a "dump database" utility that allows "hot" or online backups to a device or UNIX file. Unfortunately, this utility is designed to be run interactively in the foreground.

Digital (and other vendors) offers Digital Linear Tape (DLT) changers allowing access to seven tapes, each with a maximum capacity of 20 Gb compressed, for a total of 140 Gb. The capacity, coupled with a high transfer rate of approximately 7 Gb per hour, provides the means to backup the entire database on a nightly basis in the allotted time frame.

Digital also provides a utility, mcutil, or Media Changer Utility, which allows manipulation of the tape changer mechanism (i.e., moving individual tapes between the magazine and the tape drive itself).

The Backup Script

The backup script (dump_db.ksh) ties these three resources together to provide hot Sybase database backups either from cron or in the background (Listing 1). We were able to work around the interactive nature of Sybase's dump utility by connecting its input and output channels to named pipes. Separate "helper" scripts feed input to the dump utility and monitor its output. This arrangement allows the main script to detect when it is necessary to change a tape and synchronize that activity with the dump utility.

A backup is initiated by calling dump_db.ksh with no arguments. The script will do the following:

1. Load and mount the first tape in the changer.

2. Create the named pipe.

3. Start an awk program to watch for the "full tape" message with standard input coming from the named pipe.

4. Start the Sybase "dump database" utility with standard output redirected into the named pipe.

5. Mount subsequent tape(s) when needed.

6. Unload the final tape when backup is complete.

The named pipe or FIFO is the special file that allows the script to run in the background. This pipe allows the small awk script to monitor the output of the database dump, even though the dump is running as a separate background process. The awk script acts upon the dump utility's output by spawning yet another process to change the tape when required. (See Listing 2.)

Figure 1 shows the typical output of the Sybase "dump database" command that will be monitored for two items. The string <new_volume_name> indicates a tape change is necessary, and the @session_id tells the backup server to continue after a tape change.

Listing 1, the main script, starts a simple awk program to watch the "dump database" output for these two items. This awk script takes its standard input from the named pipe. Next, the main script starts Sybase's "dump database" utility with its standard output redirected into the the named pipe and to a logfile via the tee command. When the awk program matches the strings from the "dump database" output, either the session_id environment variable is assigned for use later in the process or the vol_change.ksh script is run to change the tape.

Both scripts invoke the mcutil command to manipulate the tapes during the backup process. The mcutil -e command reports the status of the elements of the media changer (Figure 2). The main script queries the media changer status to ensure that the correct tape is loaded before starting the backup. Once a tape is full, the vol_change script again invokes mcutil, this time with a -m or move option to advance to the next tape. The following example command line moves the media from slot 1 to drive 0:

mcutil -m s:1 d:0

The main script also records each backup's start and finish times with calls to logger.

Summary

This script demonstrates the use of a named pipe to watch the output of a program and do something when a particular string is seen. This particular script has been running in a production environment for more than 6 months, and the backups it creates have been successfully tested by performing several dress rehearsal restores.

About the Author

Matt Cheek has been a UNIXsys admin since 1988. He currently works for MCITelecommunications in Colorado Springs, CO where he supports a variety of mission-critical systems both in development and production. He can be reached at matthew.cheek@mci.com.