Sybase Backup Scripts
by Edward Barlow

INTRODUCTION

This package is a simple backup facility for Sybase. It includes a robust Dump, Dbcc, and Update Statistics services. Driver scripts (nightly.SERVERNAME or tran_log.SERVERNAME) to run these programs are created by the configure utility. A menu for operators is provided (menu.ksh) that provides them with the ability to load dumps and incremental dumps and to start and stop your server (among other things).

All programs are written in bourne shell using a robust shell function library (func.ksh). The individual scripts should, consequently, be simple and easy to understand. The library interfaces with a error handler that can be customized easily to send mail, page, write to syslogd, or do whatever you want (on_error.ksh).

The V4 release contains a few bug fixes and a new menu script. I highly recommend you check my web site periodically to find any future releases. If you haven't already, Download the latest gzipped tar file. Note this is a compressed tar file even though it ends with a .tar extension - rename it to latest.tar.gz. NOTE: this download is my full toolkit. The shell backup scripts are in the subdirectory shell_backup_scripts : change to that directory and follow the instructions in the installation section. If you have any comments, please contact me.

Note that this package, which is in reliable use at quite a few locations is intended for "normal" sybase installations.  Specifically, it relies on dumping the databases to disk.  Your system administrator is then responsible for backing up to tape.  It also makes no provision for striping of dumps.  This means that if you have large databases (over 2GB), you will be unable to use this package because the databases will not be dumpable to system files on most unix systems.  See your system
administrator or contact the author with any questions in this area.

The author is happy to discuss installation, support, and customization of this package to your environment.  Contact SQL Technologies at www.edbarlow.com for further information.

This package is being distributed for free. Enjoy!

This file should contain everything you need to know regarding these scripts, but it is a bit disorganized.  Please read it.

TABLE OF CONTENTS

Introduction Directory Layout  The Main Backup Script The nightly.SERVERNAME script Rights
Installation Notes Programs Menu Security

FEATURES

The following are some of the features of these scripts:
  1. Standard Nightly Processing: including Full Database Dump, Update Statistics, sp_recompile, and standard DBCC's
  2. Optional Auditing - which creates a report of your configuration that can be used for disaster recovery.  This report includes dumps of the system tables, and formated reports from extended stored procedure librayr proceures.  It requires the free extended stored procedure library (download from the same site where you got these backup scripts
  3. Optional Object Level BCP Backups - databases can be specified that allow bcp backups
  4. Nice error handling - it is clear if there are errors
  5. Backup files are optionally compressed
  6. Scripts for loading backups
  7. Menu system that operators can use - which you may place in a restricted shell for startup

DIRECTORY LAYOUT

All output is placed in a nice directory structure,identified to the system when you run the configure command.  The directory identified to configure will be composed of the following sub directories:
 
$DSQUERY/audits Directory to put audit results (optional)
$DSQUERY/bcp Directory to put bcp level backups (optional)
$DSQUERY/dbcc Directory for raw dbcc output
$DSQUERY/dbdumps Directory for local server dumps 
$DSQUERY/errors Directory for error messages
$DSQUERY/logdumps Directory for local server tran log dumps
$DSQUERY/sessionlog Directory for run logs (necessary if you are running from a scheduler like cron)

Note the beauty of this layout.  Because error messages are written to files in an error directory, you only need to peruse this directory on a daily basis.  If any files are in this directory there is a problem!  The other directories contain enough information to resolve the problem (ie. a full log of the session is in the sessionlogs subdirectory and the raw dbcc output is in the dbcc directory...).  The administrator is responsible for cleaning the error directory after reading the files it contains. Remember, files are only created in the error directory if a problem is encountered. Session logs and dbcc output are kept in sessionlog and dbcc, but will probably never need to be used (unless you are debugging a problem). dbdumps and logdumps contain the actual dumps. The audits subdirectory contains the output of the two audit shell scripts.  I believe these files will contain system configuration information sufficient to rebuild your server after a system failure.

nightly.$DSQUERY

When you initially install the scripts (using the configure command), you will create a nightly.$DSQUERY, a tran_logs.$DSQUERY, and a test_script.$DSQUERY script. The nightly.$DSQUERY script is the main backup script and is run once per night (hence the name nightly).  The tran_logs* scripts dump all transaction logs on a data server as appropriate (no data and log on same device, truncate log on checkpoint is off).  The test_script* scripts are used to test the backup scripts.  Test script will do a simple quick dump of master to see that things (like paths) are set up correctly. Be forewarned, these script contain embedded passwords. See the security section for more on this.

The nightly.$DSQUERY script will do the following:

  1. Dbcc everything but tempdb/model
  2. Report on db's that have either Select into on or have data and log on the same segment, but have Trunc. Log on chkpt off .
  3. Do a transaction log dump prior to the full dump.
  4. Dump All Databases but tempdb/model.
  5. Optionally Compress the dumps
  6. Update Statistics and Recompile everything but tempdb/model
  7. Optionally Run One of the Two Audit Reports Of Your Configuration (if extended procs installed)
This is fine for the majority of db's, but if your dbs are too large to dump to files (usually when they exceed the 2 GB file size for Unix) you are on your own and will need to dump directly to tape.

The two audit reports may contain sensitive information and should probably be protected.  They require my extended stored procedure library to be installed (after v3.1).  The audit scripts audit potential configuration and security problems as well as printing device and database layouts.

tran_logs.$DSQUERY

This script does a transaction log dump on SOME of your databases. The databases that are dumped are those that are "production", which means that "trunc. log on checkpoint" is off and "select into/bcp" is also off. Of course, master, model, and tempdb are never transaction log dumped.

RIGHTS AND REDISTRIBUTION

This package and related programs is copyright© 1996-2000 by Edward M Barlow. All Rights to this program are reserved. You may use and redistribute this software, without charge, so long as copyright notices remain intact and you make no money in the process. And if you are a kind soul and have a consulting budget - keep me in mind for that spot!

To the best of my knowledge this program works as specified, but I make no warranty about it (i.e.. you get what you pay for). I recommend you browse the scripts to see what i have done prior to using them in any production environments. Let me repeat, I offer no WARRANTY whatsoever on this software. If you make any changes that make the tool better, please send the changes to me so all can have them. I rely on people like yourself to enhance my software and make it work beautifully.

INSTALLATION

You should install this package as the Sybase account (or whatever account you will use for the backups). The reason for this is so that you have appropriate permissions on dump files (rename etc...). If you really need to change this, comment out the AmISybase functions in the drivers (but be careful here - check the permissions on your dumps).

The scripts are released as a compressed tar file (either extension .Z or .gz). Be sure that the downloaded file has the appropriate extension (some time the browser will truncate the extension).

  1. Download the file from the web site http://www.edbarlow.com
  2. Rename the script to nightly.tar.gz if your brain dead browser downloaded it as nightly.tar (your mime types are wrong)
  3. Run the command gunzip nightly.tar.gz or gzip -d nightly.tar.gz. to uncompress the file
  4. untar with the command tar xvf nightly.tar, which will create the shell_backup_scripts subdirectory.
  5. make a directory into which you wish to put your backups.  This directory should, of course, have enough space for your backups.  If you need multiple file systems for this (due to a system 2GB file system limitation), dont worry at this point.  Let the configure script (next step) create the directory tree under the base directory created in this step - and then mount additional file systems into the mount points created by configure.b
  6. Change into this directory and then run the configure script. This script will ask you for your SERVER name, the login that you will run as (either sa or an operator account that you have already set up), the password for that account, and a directory (which must already be set up) for the dumps. The dump directory should have lots of space. The configure script will set up subdirectories in the dump directory and will create a nightly.$DSQUERY, a tran_logs.$DSQUERY, and a test_script.$DSQUERY script.
  7. Test your configuration with run test_script.$DSQUERY, which tests your setup by trying to dump master.  At this stage it is common to get a message about misconfigured databases.  I lump databases into two categories: production databases (select into/bcp is off, truncate log on chkpt. is off, and data and log are on separate devices) and development databases ( truncate log on chkpt. is on).  Any other configuration will generate an error message, and to be honest, you should fix it.  The main problem here is that sometimes some sybase system databases (notably sybsystemprocs) are set up with wierd options.
  8. Further test by run nightly.$DSQUERY and tran_logs.$DSQUERY by hand.

  9. Add your script to sybase's crontab (or whatever scheduler you use).  On unix this is done with crontab -e.  If your editor doesn't work here, set the environment variable EDITOR (e.g. EDITOR=/bin/vi).  If you get a permission violation, contact your unix support person and have them allow you to run cron jobs.  A sample cron can be found in crontab.sample.  Note that you must run the scripts as the SYBASE account.  Note that the output already goes to the session file, so redirect cron output to /dev/null so you dont get voluminous mail. ie. /home/programs/bin/nightly.SYBASE >/dev/null. You probably want to dump tran logs every ten minutes on production systemscrontab.sample.
If you wish to run the menu.ksh program in a restricted unix account, modify your /etc/password (however you normally do this). You probably want it to run as a user in group Sybase (so you can use Sybase for something else). If this is the case, test starting a server - you could easily bomb on permissions. To set up as a member of group Sybase, remember that permissions should be set for group.

If you have specific error handling in mind, edit on_error.ksh, which is the script that handles all errors.  This script is easily modifiable.

COMPRESSION

The current implementation supports database backup compression (local configurations only).  To compress your files, set the compression section of your environment file:
 
#
# COMPRESSION
#
# typically this will be /usr/local/bin/gzip and gunzip
#  or /usr/ucb/compress and uncompress.  Leave blank for no compression.
COMPRESS_PROGRAM=
UNCOMPRESS_PROGRAM=
To something like
 
COMPRESS_PROGRAM=/home/programs/bin/gzip
UNCOMPRESS_PROGRAM=/home/programs/bin/gunzip
There are some gotcha's if you do compression (which is why it has not been in until the latest version).  Firstly, to compress you need double the disk space of the dump files (it makes a copy).  This can cause all  kinds of disk errors if you are not careful.  Secondly, the backup program will now break when run by any account except Sybase (or root i guess).  It will get a permission violation (cant modify to file).  Other than that the compression should work fine.

One note on file compression.  It takes twice the space to compress a file. There is no space checking involved.  If you run out of dump space the scripts will die.  If you need to compress, you might not have room to compress.

FREQUENTLY ASKED QUESTIONS

This package is stable.  Every year or so i get an enhancement request - and this year there was even a trivial (and silly) bug.  But modifications to the package are rare - which means it should be stable!!!  And that is the point isnt it.

This package should run cleanly from cron.  If it doesn't, test the nightly* and tran_logs* from the command line.  There could well be some environmental difference that is breaking things.

Run out of the *sybase* cron instead of root (if you run out of root, you should do a "su - Sybase -c" but this is not recommended).  In fact to make this a little more clear, almost every real problem I have ever encountered with the scripts has been by people using "su - Sybase -c..." instead of running out of sybase's crontab (so just dont do it).

A session log AND a separate error log are created for each run.  The error log is removed after a run if it is empty and mailed to the specified user(s) if it is not empty.

User objects (non dbo) will not have statistics updated on them, I cant figure out how to get sp_recompile to work on these, so no dice.

New system 11.9 and System 12 features are not included (the scripts have not been modified in years).  If any new features come out that should be included in this package please contact the author.

The scripts should work on microsoft sql server, but i have not tried them on this server in a long time.  If you have a MS SQL server, try them out and let me know the results (and tell me how the scripts fail).  Of course, to run them on a SQL server you will probably need a UNIX clone like MKS Toolkit to get them to work.

all scripts can be viewed and edited better with tabstop=3

Modify on_error.ksh to manage your own errors (perhaps you wish to get pages etc...).  Errors pass through this script before they are are written  to a file (identified in the environment file) .  You can modify this for your own error  handler.

For remote servers, a file called GENERATION.$DSQUERY is created.  This file maintains the current generation for the server.  This number is incremented prior to a full nightly dump of the server.  It uses this number to create a unique filename on the remote disk for the dumps.  The transaction log dumps also have the generation as well as the hour the log was dumped (you can modify this if you dump more than once an hour).  If you maintain more than one generation and need to do a restore, first check the GENERATION.$DSQUERY for that server to determine the current generation, restore that full dump (filename: {dbname}_dbdump{generation}) then apply the transaction dumps appropriately.  This is necessary because you cannot delete files on a remote disk (without NFS or some other such mounting capability which is not presumed). So in this way the dumps will write over the old dumps and will not require you to delete remote files.

Remote generations will not work with Pre System 10.

If you have tables named like State, Error, Msg, you may have trouble with the dbcc program.  These strings are greped for as error messages.  In the worst case (you have mixed case tables of these names, or a case insensitive server with these table names).

PROGRAMS INCLUDED

Dbcc.ksh  run dbcc on 1 database
DumpSybase.ksh  dump (full or incremental) 1 database 
ENVIRONMENT.* your configuration parameters
ForEach.ksh Loop through a database
IsUp.ksh  is a program up
on_error.ksh user error handler - edit to hook in however you want
Run*Audit.ksh audits a database
StartSybase.ksh start Sybase & archive your logs
StopSybase.ksh stop Sybase
Update_Statistics.ksh update statistics on a database
configure setup the package
func.ksh all the functions you need
menu.ksh a menu program
nightly.*  sample main nightly batch
test_script.* script to test your installation
tran_logs.* script to do incremental dumps

MENU

The menu.ksh program provides a user interface for the package. The menu is run by menu.ksh SERVER BACKUPSERVER login [password]. If password is not passed, you will be prompted. The menu is self-explanitory as follows
DATABASE OPTIONS BACKUP MENU
1) Backup A Database
2) Restore A Database
3) Start Database Server
4) Stop Database Server
5) Start Backup Server

6) See Backup Error Logs
7) Delete Backup Error Logs
8) Show Database Error Log
9) Show Database Backupserver Errorlog

10) Kill User Process
11) Show Users on System

12) Put a Database In Single User Mode
13) Put a Database In Multi User Mode

q) quit

SECURITY

Keep these scripts secure using unix permissioning - they should be owned by Sybase and NOT accessible by group other. Some of the scripts have passwords imbedded in them. Everything should run as Sybase (the user or the group) for safety. Internally, passwords are passed by a global variable so people using buggy versions of 'ps' can not discover them. To do this, the programs accept the variable SYBPASSWORD which is set in your driver script. If you wish to write your own drivers, copy what i do in the nightly.sample script and pass the string "N.A." to the programs in place of the password parameter.

The 2 audit reports may include sensitive information and should be protected if you so wish. The full audit script actually does a basic "crack" to discover logins where user=password. They require the extended stored procedure library to be installed (post v3.1). Samples are included as FullAudit.out and Audit.out.

RIGHTS AND REDISTRIBUTION

You are welcome to use and redistribute this software, without charge, provided you make no money from it and provided you maintain all copyright notices intact. To the best of my knowledge this program works as specified, but I make no waranty about it (ie.  you get what you pay for).  I recommend you browse the scripts to see what i have done prior to using them in any production environments.  I offer no WARRANTY whatsoever on this software. It is ok for you to modify any of the programs (so long as you dont remove any of the copyright notices).  If you make any changes that make the tool better, send the changes to me so all can have them.  I rely on people like yourself to enhance this toolkit and make it work beautifully.

This software is copyright ©1996-2000 by Edward Barlow

OTHER

Thanx to Stephen Agan who modified the backup scripts to support remote servers.
 

VERSION INFORMATION

V5.1: Sun Oct 10 15:04:14 EDT 1999
    11/1/99 Made tran_logs.sample not remove dbcc files
    10 13 99 Made fix to tran_logs.sample .sh extensions changed to .ksh
V5.0: Wed Jun  9 10:28:13 EDT 1999
V4.6: Fri Oct 30 14:24:39 EST 1998
V4.5: Sun May 17 18:23:10 EDT 1998
V4.4: Fri Apr  3 15:45:03 EST 1998
V4.3: Sun Apr 12 12:12:51 EDT 1998
Removed configure.cfg from distribution. no AmISybase function in default config. compression into configuration program. full auditing not by default.
V4.2: Mon Jun 23 10:45:22 EST 1997
added -type f to find commands for windows nt systems removed : from date of files  Command line options to nighlty. Fixed run_query which could generate spurious messages when, for example, you had a table named error or Msg in your database.
v4.1
Added support for backing up remote servers (including generations). Added ability to mail error file to certain user(s). Prompt user for # of generations in configure. Added nightly.jobs to automagically submit all nightly jobs in the directory. Added backup via bcp BCP scripts. do not handle identities currently in bcp.
v4.0
added menu.ksh changed a variety of things - including dynamic get of errorlog from the RUN_xxx scripts. added compression ability modified docs changed Update stats routine to make ForEach ./ForEach added ISQL_FLAGS variable removed chmod -f flag - doesnt work on all unix versions
v3.4
fixed dbcc error of catting whole thing to one line configure history file created with default answers for server chmod to protect the directory for the v3.3 Fixes  configure gives error if cur directory not made by system added purge of sessionlogs changes status to Status in test* Added Audit Routines
v3.2
minor error in tran_logs.* one final ./func.ksh if . not in path
v3.1
removed ForEach* programs fixed alert in nightly fixed bug in purge of log dumps moved initialize later so you could run Dump by hand (bug) moved more stuff from nightly to func.ksh
v3
made '.' not be required in path removed error log if empty made configure able to create dir if it exists removed need for dump devices unless 4.9 configure creates nightly.SERVER files Alert on bad dump databases now handles multiple servers per system - need different install for now create environment file
v2
If $SYBASE/install/errorlog_server is on mounted partition, Stop/start now work Changed a bunch of messages Added configure utility Modified Error Handling so it is much more robust. Print normal dump messages to the screen. If dump file exists, the program will not stall. Write to session log


Ed
edbarlow@mad.scientist.com

The current home for future releases is my web site:

http://www.edbarlow.com