The following is a guide to a FREE plan-based Data Server Backup and Maintenance Package for Sybase ASE and Microsoft SQL Server. 

The package is written in perl,  runs on WinNT, Linux, and UNIX platforms, and include all the facilities you will require to perform basic data server management.  The individual scripts are small and easy to understand and, as with all perl code, are released as source code. The primary interface to these scripts is command line based for flexibility and power. Electronic mail is used for notification as appropriate.

This package has been tested on the following databases

    SQL SERVER 2000     SQL SERVER 2005     Sybase 4.9     Sybase 10     Sybase 11     Sybase 12.5     Sybase 15

This package is released as Free software from the GEM web site under a standard GPL license. The license will be updated to GPL v3 when that draft is finished.

Backup Manager Package Links. Home Page   Standalone Download   GEM Download

The GEM Server Maintenance Scripts are a complete solution for routine maintenance of "normal" Sybase and Microsoft SQL Server installations. This package uses Maintenance Plans to perform normal maintenance for your database systems like dbcc, update statistics, reorgs etc...

These scripts work with both Sybase and Microsoft SQL Server. While we recommend the Microsoft Enterprise Manager solution for your smaller SQL Servers, their solution does not work well for all environments. Our solution is designed to complement the Microsoft solution, and is engineered to provide a flexibile mechanism that works well in complicated SQL server environments.

This package has been around for about 15 years, although it was originally written as a set of Unix bourne shell scripts.  It was rewritten in perl in the late 90s as perl scripts, modified in early 2002 for Microsoft Sql Server compatibility, and is considered production quality and stable. 

The following are some of the features of these scripts:

• Free
• Open Source Perl
• Maintenance Plan Based
• Command line driven
• Complete backup solution for normal (ie. not data warehouses) Sybase and SQL Servers.
  • full backups
  • transaction log dumps
  • update statistics (with sp_recompile)
  • standard DBCC's
  • db reorgs
  • log shipping
• Reports provide audit information necessary for you to rebuild your server from scratch.
• Table Backups via Optional configurable BCP Backups of critical databases and tables
• Extensible error handler that interfaces with your error management system
• Universal Solution that works on UNIX, Win32, Sybase, and SQL Server
• Compression of backup files using either internal (sybase) or external compression utilities (sql server)
• Graphical menu system that both operators and administrators can use to load backups
• Extensive Logging allows you to understand what occurred and when occurred
• Self Cleaning architecture removes old backups and log files based on configurable parameters
• Clean Layout provided in an easily understood clean directory structure
• Centralized Solution running all maintenance from a single system
• Log Shipping solution for disaster recovery

These scripts are designed to be simple to understand and work with. The scripts are delivered in three directories. The first is dbi_backup_scripts, which contains the code. The second, named lib, contains perl modules/libraries. The third, named conf, contains your configuration files.

This package is installed and run from ONE centralized system. This central system runs all your backups, which you are responsible for scheduling using cron (on unix) or the windows task scheduler (on win32). You will identify an 'output' directory (BASE_BACKUP_DIR) which will contain your log files (dbcc output, session logs etc). The full and incremental backup files, of course, stay on the system that is running your RDBMS. The success of your backups is checked via RSH, SSH, or FTP (if running on UNIX) or via Windows Native File Access (if running on win32).

The following stand alone perl scripts are provided. These open source programs are small and self documenting - you can see syntax using perl program_name -? and can see full documentation either at the end of this help section or by typing perldoc program_name:

PROGRAM	DESCRIPTION
backup.pl	This is the main script to run backup plans - calling the other scripts appropriately.
config_report.pl	Create An Audit Report
dbcc_checktable.pl	Runs DBCC Checktable on a single table
dbcc_db.pl	Runs Standard Database Level Dbcc Commands
dump_database.pl	Backup Database (complete or incremental)
fix_logship.pl	Resync two directories of log shipping files - ie copy from source to target
kill_users_in_db.pl	Kill the users in a particular database
load_all_tranlogs.pl	Load All Unapplied Transaction Logs into another database
load_database.pl	Load a Database from Dump or Incremental DUmp
rebuild_index.pl	Complete Index Rebuilds (not used much)
reorg_sybase.pl	Reorg Rebuild or Compact for Sybase
set_dboption.pl	Sets A Database option like truncate log on checkpoint
show_configvars.pl	Diagnostic - see the configuration values
update_stats.pl	Update Statistics and Recompile

Most of the time the only program you will use/schedule is backup.pl with appropriate arguments. backup.pl reads maintenance plan information from the master configuration file, configure.cfg, and runs the associated sub-commands as needed. Backup.pl is normally scheduled using one of two formats: backup.pl -JPLAN_NAME (full plan) or backup.pl -t -JPLAN_NAME (just dump & ship the tran log). backup.pl can take additional arguments like -d (runs in verbose/diagnostic mode), -R (control which steps are run), and -D (specifiy the databases to work on). Full documentation for any of these scripts can be found on this page or via 'perldoc backup.pl'. A short command syntax will be printed when you run 'backup.pl -?'. When installing, i suggest 'testing' your backups using -Dmaster - backing up a small database like master is an effective way of identifying all setup issues because the scripts will work fast.

The files conf/unix_passwords.dat, conf/sybase_passwords.dat, and conf/sqlsvr_passwords.dat contain passwords.

These backup scripts are shipped as an integrated part of GEM, our commercial enterprise management solution for DBA's. They are also shipped as a standalone package and can be downloaded in compressed tar format (.tgz). 

There are minor differences between the two layouts. The main difference is that the standalone release creates the three directories (conf, lib, and dbi_backup_scripts) under the directory "backup_scripts", while the GEM release puts the actual scripts in <GEM ROOT>/ADMIN_SCRIPTS/dbi_backup_scripts. Other than that, the releases are identical.

We distribute the software as .tgz files (compressed tarbals). A compressed tar file is a native unix format that is also the format used by Windows utilities like WinZip.

On unix, uncompress the distribution using tar xvzf backup_scripts.gtz. Older versions of tar may not have the 'z' uncompress option - you can also uncompress via gunzip backup_scripts.gtz or gzip -d backup_scripts.gtz and then extract with tar xvf backup_scripts.tar. Note the change in file endings - the uncompression programs will change the .tgz extension to a .tar one.

After you download the package and uncompress, you can see the three main directories. The configuration files (in the conf subdirectory of course) are distributed with .sample extensions. This means that you can overwrite a working distribution with a code update and it will not clobber your configuration settings. You will need to copy these files, removing the .sample extension, and hand edit them to define your passwords and Maintenance Plans.  These files are completely self documenting. 

Finally, our standalone release ships with a file configure.ksh which you must run before contiuning. All this simple file does is reformat the codeline by removing windows newlines and changing the internal include paths (so the code can find the perl libraries that are shipped in lib). The GEM configuration process does much the same thing.

Note that when you download, some older browsers strip off multiple extension names.  If the file does not open once it is downloaded, check that the file contains the extension .tgz (compressed tar file) and rename the file if it does not. After you have downloaded, follow instructions in the installation section. If you have any comments, please contact SQL Technologies.

These scripts normally run on a central 'scheduling' system.  You do not need to schedule backups on your database servers.

If you have both Windows and Unix Database Servers, you will want to schedule your Unix Database Backups on a unix system and your Windows Database Backups on a windows server. The reasons for this are technical - TDS protocol changes by microsoft make connections from Unix to SQL Server impossible without implementing Free TDS / UnixODBC (non-trivial). The lack of native ssh/rsh on Windows makes checking Unix backups from windows hard - and scheduling Unix Database backups from the Windows Task Scheduler can not be recommended. Backups and server maintenance are a core and critical part of the dba's job - and reliability is important. Schedule your Unix/Sybase backups from a central Unix system and schedule your Win32/SQL Server or Windows/Sybase backups from a central Windows server. It is normal in mixed environments to store your logs (BASE_BACKUP_DIR) in a samba share so all logs are readily available from both unix or windows.

The scripts validate backups by doing a file listing on the backup files. We use SSH, RSH, FTP and Windows Networking to implement this. You are responsible for setting up no-password access via SSH, RSH and Windows Networking, or for providing unix credentials that work with FTP.

There is occasionally a need for additional installations of the backup scripts. This might be done due to communication firewalls or log shipping for High Volume installations. High Volume log shipping may require backup script installation on one of the database servers because log shipping requires copying the log dumps (and sometimes full dumps) from the Primary to the Secondary server. When scheduled from a third system, this is done by copying the files from Source to that third system and then copying to the target - double copying. For large (100+GB) dump files this is problematic. In this case, simply install and run the standalone version of the scripts on either the primary or secondary server - and your log shipping solution will work faster because there is no 'middle' system.

This solution is a general solution for backing up and maintaining Sybase and Microsoft SQL Server. No provision is made for very large databases (ie ones that will not fit on disk). This package routinely handles servers containing several hundred gigabytes and hundreds of databases. Very large RDBMS systems have particular needs which must be engineered by your database administration staff. For example, many data warehouses have uptime requirements that preclude your ever running update statistics and dbcc! These servers may require specially designed maintenance using facilities like table level dbcc or the running of update statistics on a separate servers. We provide consulting if you have questions on how to implement backups (but theoretically the scripts are designed so they are easy to use & you wont need that).

Our scripts provide a robust and complete log shipping solution that can assist you if you are concerned about the length of time that restoring your databases from backups might take in emergency situations. We use simple transaction log shipping, which has been a normal part of these RDBMS systems for many years.

The code is released in three directories. These directories are laid out differently depending on if you are using GEM or the stand alone release.

GEM Release Directory Structure

	gem	Where You Installed
	conf	configuration files
	lib	Perl Libraries
	ADMIN_SCRIPTS	Where all the tools are
	dbi_backup_scripts	The code that is run

Standalone Release Directory Structure

	backup_scripts	Where You Installed
	conf	configuration files
	lib	Perl Libraries
	dbi_backup_scripts	The code that is run

Output is placed in a simple neat directory structure that exists on all your systems (both the scheduling system and the target database). These directories will not necessarily be populated. The scheduling server populates all directories except dbdumps and logdumps - which contain the actual backups. dbdumps and logdumps are the only directories populated on the systems running your RDBMS's. The directory structure is composed of the following:

	Base Backup Directory	e.g. C:/backups (on NT) or /dbdumps (on UNIX) per config file
	.../$SERVER	Sub directory for your Server
	.../$SERVER/audits	Directory to put audit results (optional)
	.../$SERVER/bcp	Directory to put bcp level backups (optional)
	.../$SERVER/dbcc	Directory for raw dbcc output
	.../$SERVER/dbdumps	Directory for local server dumps
	.../$SERVER/errors	Directory for error messages
	.../$SERVER/logdumps	Directory for local server tran log dumps
	.../$SERVER/sessionlog	Directory for run logs

This is a very functional and practical layout.  Because sessionlog and error messages are written to files on the central backup server, you have ready access to this information. If you wish to track down what happened to last nights backups of system X, you do not need to actually go to system X - you have the data in the same location as is all your other data. A full log of all your Maintenance operations stored in the sessionlog sub directory. A full log of raw dbcc output is stored in dbcc.

All files in these directories are appropriately timestamped using yyyymmdd.hhmmss format so they list in time order when you do a directory listing. Log files are kept small yet clear, so you can keep several weeks worth of information readily available. The system will self-purge these log files based on a configuration setting that you control (ie. how many days do you want to keep of either your backups or your logs). You most likely will never use these historical logs, but they are useful if you have a problem.

Similarly, 'audits' contains the output of a server configuration audit. This can be used to rebuild your system from scratch and contains system procedure output (sp__helpdb) plus a bcp in both native and character mode of important system tables. The audits require that you install our free extended stored procedure library.

This package and related programs is copyright © 1996-2008 by SQL Technologies and Edward Barlow. All Rights to this program are reserved. The software is released under standard Gnu Public License (GPL) license. The license will be updated to GPL v3 when that draft is finished.

You may redistribute the package at will (see below). Tell your friends.  Tell me about bugs. Be nice - I am making no money off this.

You are allowed to use this software so long as all copyright notices, README, and other documentation are not altered and so long as no money is made by the sale of this software (i.e. you cant include it in a commercial package without permission). If you would like to "make money" or include the code in a commercial package, I ask that you decide on a "fair" price and create some form of "fair" agreement. Make two copies, sign them both, and send them to the package author (Edward Barlow). If the agreement seems fair, I will sign both and send one copy back to you, and we will have a deal. I have put significant effort into this code and, while my primary purpose is to create software for people to use, I expect a fair shake from anybody who can profit from my endeavors.

Keep these scripts secure using file permissioning - files (especially your configuration files which are in the conf subdirectory) should have restricted access.  They contain your server administrative passwords. Keep them secure.

As an added level of security, we block the unix 'ps' command from showing your passwords.

The password files in the conf directory contain all passwords for system access. On unix Sybase DBMS's you will need to edit unix_password.dat and sybase_password.dat. The unix information is necessary for ftp/rsh/ssh to work to remote unix systems. On windows databases, you only need to edit the information in sqlsvr_password.dat and/or sybase_password.dat as windows networking is used to manage database backup verificiaton and file copies. You will, of course, need windows networking access from your central backup server to your remote backup directories.

The password files are pretty simple. They use the following format:

 EDSDB sa edsdbsapass
        SERVER_TYPE=PRODUCTION
 BOBSDB sa bobsdbsapass
        SERVER_TYPE=PRODUCTION

Entries starting at the beginning of the line are servers and have the format servername login password. The login of course must be able to perform the necessary operations (ie be an oper role account). The lines that are indented contain server variables like SERVER_TYPE - which indicates the system is DEVELOPMENT, PRODUCTION etc...

The master configuration file is named configure.cfg.  This file contains your plan information. This file exists in the 'conf' directory. The configuration file configure.cfg is designed to store maintenance information keyed by maintenance plan. The file is composed of key-value pairs of the form VARIABLE=VALUE. More specifically, information is stored in either the form

 [VARIABLE]=VALUE
 or
 [PLAN_NAME]_[VARIABLE]=VALUE.

When you run a task that specifies a PLAN_NAME, the [PLAN_NAME]_[VARIABLE] key will override the default [VARIABLE].  If no plan specific variable [PLAN_NAME]_[VARIABLE] exists, the default is used. 

Each of your RDBMS systems will have 1 or more Maintenance Plans. Y The majority of your smaller RDBMS systems can have a single Maintenance Plan per DBMS. For a larger system, you might create a plan for normal everyday backups and a separate plan for weekend maintenance. It is normal to name the Maintenance Plan for SERVER1 as "SERVER1" - keeping the PLAN_NAME matching the server name keeps things readable and simple. 

If you wish to only run particular steps (like DBCC) on weekends, we recommend you set up a Maintenance Plan named <SERVER_NAME> with DO_DBCC=n and a second Plan named <SERVER_NAME>WEEKEND or <SERVER_NAME>DBCC  with DO_DBCC=y turned on.   There are no limits on PLAN_NAMES other than that they should do a single set of tasks on a single database and the name can not contain non-printable characters like spaces (underscores are ok). 

Each Maintenance Plan is composed of a variety of PLAN VARIABLES (listed later).

BASE_BACKUP_DIR is a special global variable pointing to the directory structure in which you want to keep your sessionlogs and other output. 

SYBASE is also a special global variable defining the SYBASE home directory on your scheduling server. BASE_BACKUP_DIR and SYBASE can not be overridden on a per plan basis. The SYBASE variable is only requred for backing up Sybase Database servers.

The third 'GLOBAL' variable is IGNORE_SERVER, which contains a comma or pipe separated list of servers to not backup, an alternative to changing your scheduler if you wish to disable a backup.

The configure.cfg ships as configure.cfg.sample - which is renamed when building the system. This file contains complete documentation on the plan variables and should be read. The below code 'sample' shows a section of configure.cfg that overrides the two number of files to keep parameters for the SYBPROD2 backup plan.

   # NUM_BACKUPS_TO_KEEP=[ A Number >=1 ]
   NUM_BACKUPS_TO_KEEP=1
   SYBPROD2_NUM_BACKUPS_TO_KEEP=2

   # NUM_DAYS_TO_KEEP
   #  For other backup files (log files etc), this is the number of days
   #  to keep stuff for prior to removing it)
   NUM_DAYS_TO_KEEP=7
   SYBPROD2_NUM_DAYS_TO_KEEP=10

GLOBAL BACKUP VARIABLES

VARIABLE		EXPLANATION
BASE_BACKUP_DIR		[ A directory for plan output files that exists on server you will schedule your plans from ]
IGNORE_SERVER		[ pipe separated list of servers to ignore ]. Why would you do this? Well consider the situtation where you have multiple plans all running on the same server which you need to offline for a few hours. This allows you to do this. It will not be used often (The author has never used it).
SYBASE		[ Sybase Directory On This Machine (Sybase Only) ]

This is used by some cron jobs (which do not source an environment) and is the SYBASE environment variable for the system which will run the cron jobs. This can NOT be overridden on a per job basis (you shouldnt need to ever)

   example:
       SYBASE=/opt/sybase

PLAN VARIABLES

VARIABLE		EXPLANATION
SERVER_NAME		The Server/Connection Name for this plan. This is the DSQUERY variable for unix/sybase or ODBC DSN on windows for the server. By convention, you should name the main plan for your server such that PLAN_NAME=SERVER_NAME. This makes things less confusing (ie. MYRDBMS_SERVER_NAME=MYRDBMS).
SERVER_DIRECTORY		Directory on the server for your backups. e.g. D:/backups or /export/backups.. Backups files are placed in subdirectory off SERVER_DIRECTORY (see the earlier directory tree info) named SERVER_DIRECTORY/SERVER_NAME/dbdumps and SERVER_DIRECTORY/SERVER_NAME/logdumps. These may be overridden using the two optional variables below.
	SERVER_DUMP_DIRECTORY	Override database full backup directory (SERVER_DIRECTORY/SERVER_NAME/dbdumps). Not normally set. If you set this, you must set CLIENT_PATH_TO_DUMP_DIR.
	SERVER_LOG_DIRECTORY	Override the db tran log directory (SERVER_DIRECTORY/SERVER_NAME/logdumps). Not normally set. If you set this, you must set CLIENT_PATH_TO_LOG_DIR.
NUM_BACKUPS_TO_KEEP		[ A Number >= 1 ] Number of full backups to keep
DO_PURGE		[y|n] - Plan Includes Purging old files as part of normal backup
	NUM_DAYS_TO_KEEP	[ A Number >= 1 ] The Number of Days to Keep Other Files (like session logs)
DATABASE_IGNORE_LIST		Pipe separated list of databases ignored By Backups [db1|db2|db3]. Should include non-backupable databases - e.g. DATABASE_IGNORE_LIST=model|tempdb|pubs|sybsyntax
MAIL_FROM		From address for mails
MAIL_HOST		Your mail host. Not exactly sure what environments its needed in but i think only win32
MAIL_TO		Comma Separated List of EMail addresses to send failure notices. No spaces should exist in the line.
SUCCESS_MAIL_TO		Comma Separated List of EMail addresses to send success notices. No spaces should exist in the line.
IS_REMOTE		[y|n] - Is The SERVER_DIRECTORY remote from the Backup Client software. Set this to 'y' if the SERVER_DIRECTORY can NOT be reached by direct file access (NFS or NT Networking) from the Client. If Y, we will/must use FTP/SSH/RSH to access the server. If 'N', the backup scripts will use copy(). (Always 'n' on Windows)
	CLIENT_PATH_TO_DIRECTORY	If IS_REMOTE=N, then this represents the full path name the client uses to get to the SERVER_DIRECTORY. For example if SERVER_NAME=cluster4 and SERVER_DIRECTORY is e:/backups then you should set this to //cluster4/e$/backups.
	CLIENT_PATH_TO_DUMP_DIR	Similarly, this is the full path to SERVER_DUMP_DIRECTORY. Only use if you have set SERVER_DUMP_DIRECTORY
	CLIENT_PATH_TO_LOG_DIR	Similarly, this is the full path to SERVER_LOG_DIRECTORY. Only use if you have set SERVER_LOG_DIRECTORY
	SERVER_HOSTNAME	This is the Host Name the DBMS resides on and is used if IS_REMOTE=Y. Used in conjunction with SERVER_DIRECTORY. (UNIX ONLY as IS_REMOTE=N for Win32)
	SERVER_HOST_LOGIN	IF IS_REMOTE=Y this can override the Unix Account for SERVER_HOSTNAME that is identified in the password files. If not defined, will use entries from server registration. (UNIX ONLY)
	SERVER_HOST_PASSWORD	IF IS_REMOTE=Y this can override the Unix Password for SERVER_HOSTNAME that is identified in the password files. You should set it to something random if you are using rsh/ssh (it probably needs to find a password even if it wont use it - we havent tested use of a SERVER_HOST_LOGIN without a SERVER_HOST_PASSWORD). If not defined, will use entries from server registration. (UNIX ONLY)
SYBASE_COMPRESSION_LEVEL		Internal Compression Level (Sybase 12.5+ Only). Sybase Internal Compression Level. Set to 0 to not use internal compression (ie server version < 12.5). If non-zero, external compression utilities will NOT be used.
DO_EXTERNAL_COMPRESS		[y|n] - Use External Compression on Backups When Completed. Use the SYBASE_COMPRESSION_LEVEL variable if you wish to use sybase internal compression.
	COMPRESS	Full path to external compression program. Only used if not using sybase internal compression. e.g. /usr/local/bin/gzip.
	UNCOMPRESS	Path to External UnCompression Program use if DO_EXTERNAL_COMPRESS=Y. Only used if not using sybase internal compression (SYBASE_COMPRESSION_LEVEL). e.g. /usr/bin/gzip -d.
	COMPRESS_LATEST	[y|n] - should you do external compression on the LATEST backup files Requires DO_EXTERNAL_COMPRESS=Y. Not setting means the latest will not be compressed but the older backups will be.
DO_UPDSTATS		[y|n] - Plan Includes Running Update Statistics (update_stats.pl)
	UPD_STATS_FLAGS	flags for update_stats.pl. try -iv60 (for 60 buckets / index stats ) or -i (for sybase index stats)
DO_AUDIT		[y|n] - Plan Includes Running A Configuration Audit with config_report.pl
	AUDIT_PURGE_DAYS	Days of audit results to keep if DO_AUDIT=Y. Default=30.
DO_DBCC		[y|n] - Plan Includes Running DBCC Checks (dbcc_db.pl)
	DBCC_IGNORE_DB	Requires DO_DBCC=Y. In addition to DATABASE_IGNORE_LIST these databases are ignored by DBCC. You would set this if you had large databases that you want backed up but which can not be DBCC'ed every night due to time constraints. [ db1|db2|db3 ]
DO_BCP		[y|n] - should you do bcp's as part of normal backup. This will normally be N but perhaps you wish to bcp out critical tables. These will be identified by BCP_TABLES.
	BCP_COMMAND	Requires DO_BCP=Y. Full path to your bcp command if its not in the path
	BCP_TABLES	Requires DO_BCP=Y. A pipe separated list of tables to copy out using bcp.
DO_REORG		[y|n] - should you do reorg rebuild/reorg compact of normal backup (Sybase Only)
	REORG_ARGS	Requires DO_REORG=Y - The arguments that reorg_sybase.pl will take. See command docs. Default is --REORG_COMPACT.
DO_DUMP		[y|n] - Plan Includes Full or Incremental Backup
	DUMP_FILES_PER_SUBDIR	A concession to data warehouses - allows you to put striped sybase dumps into multiple subdirectories. (Sybase Only).
	NUMBER_OF_STRIPES	[ A Number >=1 ] Number Of Stripes (Sybase Only). This applies to both full and incremental backups. It is recommended that you set this to 4 and leave it at 4 for all your servers/plans.
	DO_TRUNCLOG_BEFOREDUMP	[y|n] - clear transaction logs before full backups
DO_INDEXES		[y|n] - should you perform our indexes matching as part of normal backup. This step is not a rebuild index step - it is a index doublecheck step that uses a configuration file to ensure that your databases have at least the indexes you want. You create the list of indexes and then this step will recreate them if they are missing.
	REBUILD_INDEX_DB	[db|db|db] Databases To Rebuild Indexes For
	REBUILD_INDEX_FILE	[file|file|file] Index File To Use For Rebuilding OF Indexes

VARIABLES FOR LOG SHIPPING

These varialbes are used for log shipping.  Log shipping will be invoked every time you run backup.pl.

VARIABLE		EXPLANATION
DO_LOAD		[y|n] - Plan includes incremental tran loads which you will ship and load Into Another Server.
DO_ONLINEDB		[y|n] Run online db after done with loads (Sybase Only). This is normally set to N because you will be in a log shipping situation - so you will not online your sybase dbs. But if you wanted the db to be live after you ship it - for example you are syncing a development copy o.f the db from production on a daily basis
XFER_TO_SERVER		[ pipe separated list ] server SERVER_NAME or ODBC names you want to copy to
XFER_TO_DB		[ pipe separated list ]  databases to copy into in XFER_TO_SERVER. use '*' for all databases
XFER_FROM_DB		[ pipe separated list ]  database on primary to copy from
XFER_BY_FTP		[ Y|N ftp the files to the remote system? ] If 'N' will use direct file copy
XFER_TO_DIR		Directory on Target (full pathspec) as seen by Caller (ie //mysrv/D$/logship). This directory will have the standard dbdumps/logdumps subdirs  that the dumps go into as seen by the loading host (XFER_TO_HOST).
XFER_SCRATCH_DIR		[ directory on local system for tmp space ] this is an intermediate directory we copy to on the server running the backups. This should not be in /tmp. It will contain 1 of the stripes of your backups.
XFER_TO_HOST		[ hostname to transfer files to ]
XFER_TO_DIR_BY_TARGET		Directory on Target as seen by Target (ie D:/logship)

Because our architecture involves scheduling your dumps out of a central location, we must have a solution for purging old backup files and for compressing them. Sybase 12.5 and later servers support internal backup compression, but Sybase 11.9.2 and SQL Server do not. Our solution to this is seamless. One caveat. It takes time to compress backups. It takes SIGNIFICANTLY longer to compress a backup file than it does to create it. You can, however, get up to 90% compression.

Our scripts support purging and compression in two modes. The first mode, direct file access, assumes that the monitoring / scheduling server can directly access the backup files. This is true if you are backing up to an NFS share or when running under Windows Networking. Plans using this solution will set IS_LOCAL=y and setup SERVER_DIRECTORY to the appropriate directory path. This system uses full path urls on windows (ie. SERVER_DIRECTORY=//system/c$/backups).  Be careful to use the UNIX file structure (separate directories with the / character not the \ character). The '\' character is often used in perl to escape metacharacters. As always, test your backups.

On UNIX, the system supports file purging & compression using and FTP, RSH, and SSH. You are responsible for creating the communication path (ie setting up the .rhost file if you want to use RSH). 

If you are not using sybase internal compression (either because you have an 11.9.2 or earlier sybase server or because you are working with a microsoft sql server), be aware of some facts with the optional compression.  Firstly, to compress you need double the disk space of the dump files (compress will make a copy that it compresses).  This can cause all  kinds of out of space disk errors if you are not careful.  Secondly, the compression will fail on UNIX due to permission violations when run by any account except Sybase or root (not recommended).  The error will be : permission violation (cant modify to file).  Finally, your compression on remote systems will be done over the network - which can be slow - it is not recommended once your databases exceed 20GB or so in size. The plus of this compression scheme is that database backups can be greatly shrunk with compress.  It is normal to see a 1GB dump file shrink to 100MB!  This can alleviate space concerns on your systems.  If you have no space issues, you do not need to compress.

These scripts can be scheduled using the windows task scheduler or unix cron.  On unix this is done with crontab -e.  If your editor doesn't work here, set the environment variable EDITOR (e.g. export EDITOR=/bin/vi).  If you get a permission violation when running crontab -e, contact your unix support person and have them set you up to run cron jobs.  As the output of backup.pl already goes to the session file, you can redirect cron output to /dev/null so you dont get voluminous mail. (ie. /home/programs/bin/backup.pl -JMYSERVER >/dev/null). You probably want to dump tran logs every ten-twenty minutes on production systems.  Transaction log backups are done by passing -t to backup.pl.  You can also pass -n to backup.pl to copy your transaction logs but not load them - and can pass -s to skip the transfer  and load step alltogether.  The fix_logship.pl script will identify differences between your production and dr site and copy missing files and load all unapplied files as appropriate. 

These scripts provide a default error handler. This will basically an email that is sent on failure and on successful full backup.  You an also easily configure the error handler to integrate with your own error handler.  The scripts run the perl module Log::Dispatcher out of the file on_error.pl.  If you wish to modify the default behavior of this error handler (which is to put errors in the errors sub directory and session logs in the sessionlog sub directory, and then to mail to a specified set of users), you simply need modify the on_error.pl.  Log::Dispatcher is easy to use and supports a variety of log and error handling options.

The package does not work from your scheduler, but runs fine from the command line: There could well be some environmental difference that is breaking things.  Specifically, check that the environment variable SYBASE is defined.

I get permission violations when running: The scheduler should run out of the same account that runs the Sybase server.  On UNIX it should not run as 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).

How can i find if there were problems last night: A session log AND a separate error log are created for each run.  The error log is removed after the run if it is empty.  So, to tell if there were problems just look in the errors subdirectories in the output file tree.  If there are any files there, there were problems.  Remove these files each day and you will know if there are any events that need attention.  Additionally, the default error handler sends you mail with any problems.

I need to set up paging for problems:   You will need a script that sends pages and to add it to the on_error.pl file.  See the section on error handling.

The code looks funny (tabbing is wierd): All scripts can be viewed and edited best with tabstop=3.

Are there any bad table names? If you have tables with the string 'corrupt' in it, you may have trouble with the dbcc program. corrupt is a keyword that i search for to find dbcc errors.  There is no current work around for this but it would not be hard to implement. 

How can i use BCP to copy out tables

 Here are details on what happens when you specify BCP options

 a) config file read - specifically DO_BCP, BCP_TABLES and BCP_COMMAND directives.
 b) @tables = split(/[\|\,]/,$BCP_TABLES
       - tables are the BCP_TABLES directive split by pipes and commas.
 c) PSEUDOCODE:
    foreach $table in (@tables) {
         print "copying out table $table"
         $cmd= $BCP_COMMAND || bcp
                 - command is the BCP_COMMAND directive or just straight bcp if that is not defined
          execute : $cmd out $base_bk_dir/bcp/$table.bcp -Uxxx -Pxxx -Sxxx -n
    }

    the execute: statement should print the command to be executed (with password as xxx of
    course so nobody reading the log file can get the  sa password!)  You should see a line saying
    copying out table <nm> followed by something that looks and smells like a bcp command line.
  d) to test you can just run your backup with ONLY the bcp turned on.  to do this run the command:
      backup.pl -JJOBNAME -Rb

     to see diagnostic messages you can also add the -d flag.

These scripts are an integral part of the Generic Enterprise Manager (GEM). GEM provides a full featured graphical user interface to these scripts, and provides an additional layer of reporting and alarming for your backups. GEM effectively reports on the *state* of your maintenance scripts, cross checking backup file sizes, server log files, and script output so you can guarantee that your systems were effectively backed up. GEM also provides real time Alarming and Monitoring of your maintenance scripts.

These scripts are free, but if you like them, give GEM a try.

In addition to a command line interface, the package provides a simple user interface through an ascii menu (menu.pl) and from a web page (cgi.pl).

The basic flow of menu.pl is as follows:

IF THERE ARE MULTIPLE SERVERS DEFINED

WELCOME SCREEN

   1. SELECT SERVER XXX
   2. SELECT SERVER YYY

IF ONLY ONE SERVER IS DEFINED OR YOU HAVE SELECTED A SERVER FROM THE ABOVE LIST

SERVER XXX BACKUP MENU

   1. Test Connectivity
   2. Test RSH Access
   3. Check Configuration
   4. Perform Full Server Backups
   5. Backup A Single Database
   6. Restore A Database
   7. Start Database Server
   8. Stop Database Server
   9. Start Backup Server
   10. Stop Backup Server
   11. View Backup Error Logs
   12. Cleanup Backup Log Files
   13. Show Database Server Error Log (Filtered)
   14. Show Database Server Error Log (Filtered, Last 2 Days Only)
   15. Show Backup Server Errorlog
   16. Debug This Server
   17. QUIT  

TO DO ITEMS

There really is not much to do on the packages. The scripts will need an update for any new features in SQL Server 2005 and will need to be updated to run dbcc checkstorage (if anyone needs that - contact us). Also, some of the scripts use the -U/-S/-P/-J format and others use --USER=/--SERVER=/--PASSWORD= format. I prefer the latter, and will probably eventually migrate all the scripts to that format. Its easier to read

The following are examples of configure.cfg settings for a variety of systems. They are intended for illustration only.

You must test your setup. I recommend backup.pl -JJOBNAME -Dmaster. I use master because it is fast and a good db to try stuff on while setting things up. If your backup completes, then you can schedule the job. If you are using GEM, .ksh and .bat files are automatically created for each of your backup server jobs in the GEM Batch Jobs directory. You should run these.

A LOCAL SERVER

The following is an example of the simple case of backing up a local unix database server to /export/home/sybase-dump.

 MYRDBMS_SERVER_NAME=MYRDBMS
 MYRDBMS_CLIENT_PATH_TO_DIRECTORY=/export/home/sybase-dump
 MYRDBMS_DATABASE_IGNORE_LIST=model|tempdb|sybsyntax
 MYRDBMS_SYBASE_COMPRESSION_LEVEL=1
 MYRDBMS_COMPRESS_LATEST=n
 MYRDBMS_IS_REMOTE=n
 MYRDBMS_UPD_STATS_FLAGS=-i

 MYRDBMS_DO_ONLINEDB=y
 MYRDBMS_XFER_TO_SERVER=MYRDBMS
 MYRDBMS_XFER_FROM_DB=emb_test
 MYRDBMS_XFER_TO_DB=emb_test2
 MYRDBMS_XFER_BY_FTP=N
 MYRDBMS_XFER_TO_DIR=
 MYRDBMS_XFER_TO_DIR_BY_TARGET=

A REMOTE SERVER

The next example of backing up a simple non-local server that resides on the hostname myrdbms to the directory /export/home/sybase-dump on that system. Note that when this plan runs, it will run an AUDIT of your configuration (ie. reverse engineer setup stuff & bcp out core system tables so you can recover), and will DBCC, Backup, and Update Statistics on all databases not in the DATABASE_IGNORE_LIST.

 MYRDBMS_SYBASE_COMPRESSION_LEVEL=1
 MYRDBMS_DO_AUDIT=y
 MYRDBMS_DO_DBCC=y
 MYRDBMS_DO_LOAD=n
 MYRDBMS_DO_DUMP=y
 MYRDBMS_DO_PURGE=y
 MYRDBMS_DO_REORG=n
 MYRDBMS_DO_INDEXES=n
 MYRDBMS_DO_UPDSTATS=y
 MYRDBMS_DO_BCP=n
 MYRDBMS_SERVER_NAME=MYRDBMS
 MYRDBMS_DATABASE_IGNORE_LIST=sybsystemdb|model|junkdb1|junkdb2|text_db|tempdb
 MYRDBMS_IS_REMOTE=y
 MYRDBMS_MAIL_TO=ebarlow@myco.com,mydba@myco.com
 MYRDBMS_NUM_DAYS_TO_KEEP=2
 MYRDBMS_SERVER_DIRECTORY=/export/home/sybase-dump
 MYRDBMS_SERVER_HOSTNAME=myrdbms

Sybase Server Copying Single Database Within The Server

 MYRDBMS_COPY_SERVER_NAME=MAGIDDB
 MYRDBMS_COPY_SERVER_DIRECTORY=/export/home/sybase-dump
 MYRDBMS_COPY_NUM_DAYS_TO_KEEP=2
 MYRDBMS_COPY_SYBASE_COMPRESSION_LEVEL=1
 MYRDBMS_COPY_DATABASE_IGNORE_LIST=sybsystemdb|testing
 MYRDBMS_COPY_MAIL_TO=me@x.com
 MYRDBMS_COPY_DO_LOAD=y
 MYRDBMS_COPY_IS_REMOTE=y
 MYRDBMS_COPY_SERVER_HOSTNAME=mydb
 MYRDBMS_COPY_DBCC_IGNORE_DB=testing
 MYRDBMS_COPY_DO_ONLINEDB=Y
 MYRDBMS_COPY_XFER_TO_SERVER=MAGIDDB
 MYRDBMS_COPY_XFER_FROM_DB=ettrddb
 MYRDBMS_COPY_XFER_TO_DB=testing
 MYRDBMS_COPY_XFER_BY_FTP=N
 MYRDBMS_COPY_XFER_TO_DIR=
 MYRDBMS_COPY_XFER_TO_DIR_BY_TARGET=

SQL Server With Tran Log Shipping

The SQL SERVER ABCD has large and small databases, which are backed up separately and are transferred to the remote log shipping dr server DRSERVER001. We would scheule these jobs like

 # FULL DUMP SCRIPTS
 backup.pl -JMYRDBMS_SMALL
 backup.pl -JMYRDBMS_BIG -DMPdb
 backup.pl -JMYRDBMS_BIG -DWebReports
 backup.pl -JMYRDBMS_BIG -DMPdb2
 # TRAN DUMP SCRIPTS
 backup.pl -JMYRDBMS_SMALL -t
 backup.pl -JMYRDBMS_BIG -DMPdb -t
 backup.pl -JMYRDBMS_BIG -DWebReports -t
 backup.pl -JMYRDBMS_BIG -DMPdb2 -t

 MYRDBMS_SMALL_SYBASE_COMPRESSION_LEVEL=0
 MYRDBMS_SMALL_DO_CLEARLOGSBEFOREDUMP=n
 MYRDBMS_SMALL_DO_EXTERNAL_COMPRESS=y
 MYRDBMS_SMALL_DO_LOAD=y
 MYRDBMS_SMALL_DO_UPDSTATS=n
 MYRDBMS_SMALL_SERVER_NAME=ABCD
 MYRDBMS_SMALL_DATABASE_IGNORE_LIST=model|tempdb|sybsyntax|master|msdb|pubs|pubs2|Northwind|MPdb|WebReports|MPdb2
 MYRDBMS_SMALL_IS_REMOTE=n
 MYRDBMS_SMALL_CLIENT_PATH_TO_DIRECTORY=E:/backups
 MYRDBMS_SMALL_NUMBER_OF_STRIPES=0
 MYRDBMS_SMALL_NUM_BACKUPS_TO_KEEP=1
 MYRDBMS_SMALL_SERVER_DIRECTORY=E:/backups
 MYRDBMS_SMALL_UNCOMPRESS=
 MYRDBMS_SMALL_XFER_BY_FTP=N
 MYRDBMS_SMALL_XFER_FROM_DB=*
 MYRDBMS_SMALL_XFER_TO_DB=*
 MYRDBMS_SMALL_XFER_TO_DIR=//drserver/d$/logshipbuffer
 MYRDBMS_SMALL_XFER_TO_DIR_BY_TARGET=D:/logshipbuffer
 MYRDBMS_SMALL_XFER_TO_HOST=
 MYRDBMS_SMALL_XFER_TO_SERVER=DRSERVER001
 MYRDBMS_BIG_SYBASE_COMPRESSION_LEVEL=0
 MYRDBMS_BIG_DO_CLEARLOGSBEFOREDUMP=n
 MYRDBMS_BIG_DO_EXTERNAL_COMPRESS=n
 MYRDBMS_BIG_DO_DUMP=y
 MYRDBMS_BIG_DO_LOAD=y
 MYRDBMS_BIG_DO_UPDSTATS=n
 MYRDBMS_BIG_SERVER_NAME=ABCD
 MYRDBMS_BIG_DATABASE_IGNORE_LIST=model|tempdb|sybsyntax|master|msdb|pubs|pubs2|Northwind
 MYRDBMS_BIG_IS_REMOTE=n
 MYRDBMS_BIG_CLIENT_PATH_TO_DIRECTORY=//abcd/e$/backups
 MYRDBMS_BIG_CLIENT_PATH_TO_DIRECTORY=//abcd/f$/backups
 MYRDBMS_BIG_CLIENT_PATH_TO_DIRECTORY=E:/backups
 MYRDBMS_BIG_NUMBER_OF_STRIPES=0
 MYRDBMS_BIG_NUM_BACKUPS_TO_KEEP=1
 MYRDBMS_BIG_SERVER_DIRECTORY=E:/backups
 MYRDBMS_BIG_SERVER_DIRECTORY=F:/backups
 MYRDBMS_BIG_UNCOMPRESS=
 MYRDBMS_BIG_XFER_BY_FTP=N
 MYRDBMS_BIG_XFER_FROM_DB=*
 MYRDBMS_BIG_XFER_TO_DB=*
 MYRDBMS_BIG_XFER_TO_DIR=//drserver/d$/logshipbuffer
 MYRDBMS_BIG_XFER_TO_DIR_BY_TARGET=D:/logshipbuffer
 MYRDBMS_BIG_XFER_TO_HOST=
 MYRDBMS_BIG_XFER_TO_SERVER=DRSERVER001

Large Sybase Unix Db

Here we set up two plans that only differ in their options and schedule them. I have called them MYRDBMS (backup) and MYRDBMSDBCC (once per week for dbcc & reorg only). Note that these scripts basically only differ in the DO_* flags

 MYRDBMS_DBCC_SYBASE_COMPRESSION_LEVEL=1
 MYRDBMS_DBCC_DO_DBCC=y
 MYRDBMS_DBCC_DO_LOAD=n
 MYRDBMS_DBCC_DO_DUMP=n
 MYRDBMS_DBCC_DO_REORG=y
 MYRDBMS_DBCC_DO_UPDSTATS=y
 MYRDBMS_DBCC_SERVER_NAME=MYRDBMS
 MYRDBMS_DBCC_IGNORE_DB=sybsystemdb|model|junkdb1|junkdb2|text_db|tempdb
 MYRDBMS_DBCC_IS_REMOTE=y
 MYRDBMS_DBCC_MAIL_TO=ebarlow@myco.com,mydba@myco.com
 MYRDBMS_DBCC_NUM_DAYS_TO_KEEP=2
 MYRDBMS_DBCC_SERVER_DIRECTORY=/export/home/sybase-dump
 MYRDBMS_DBCC_SERVER_HOSTNAME=myrdbms

 MYRDBMS_SYBASE_COMPRESSION_LEVEL=1
 MYRDBMS_DO_DBCC=n
 MYRDBMS_DO_LOAD=n
 MYRDBMS_DO_UPDSTATS=n
 MYRDBMS_SERVER_NAME=MYRDBMS
 MYRDBMS_DATABASE_IGNORE_LIST=sybsystemdb|model|junkdb1|junkdb2|text_db|tempdb
 MYRDBMS_IS_REMOTE=y
 MYRDBMS_MAIL_TO=ebarlow@myco.com,mydba@myco.com
 MYRDBMS_NUM_DAYS_TO_KEEP=2
 MYRDBMS_SERVER_DIRECTORY=/export/home/sybase-dump
 MYRDBMS_SERVER_HOSTNAME=myrdbms
 MYRDBMS_UPD_STATS_FLAGS=-i

SQL Server With All the Bells And Whistles

Logshipping, Compression,etc...

 MYRDBMS_COMPRESS=D:/PROGRA~1/MKSToo~1/mksnt/mkszip.exe -l1
 MYRDBMS_SYBASE_COMPRESSION_LEVEL=0
 MYRDBMS_DO_CLEARLOGSBEFOREDUMP=n
 MYRDBMS_DO_EXTERNAL_COMPRESS=n
 MYRDBMS_DO_DUMP=n
 MYRDBMS_DO_LOAD=y
 MYRDBMS_DO_UPDSTATS=n
 MYRDBMS_SERVER_NAME=PLATINUM
 MYRDBMS_DATABASE_IGNORE_LIST=model|tempdb|sybsyntax|master|msdb|pubs|pubs2|Northwind
 MYRDBMS_IS_REMOTE=n
 MYRDBMS_CLIENT_PATH_TO_DIRECTORY=//adcluster400/e$/logdump
 MYRDBMS_CLIENT_PATH_TO_DIRECTORY=//platinum/e$/logdump
 MYRDBMS_NUMBER_OF_STRIPES=0
 MYRDBMS_NUM_BACKUPS_TO_KEEP=1
 MYRDBMS_SERVER_DIRECTORY=D:/backups
 MYRDBMS_XFER_BY_FTP=N
 MYRDBMS_XFER_FROM_DB=*
 MYRDBMS_XFER_TO_DB=*
 MYRDBMS_XFER_TO_DIR=//adsdr012/f$/logshipbuffer
 MYRDBMS_XFER_TO_DIR_BY_TARGET=f:/logshipbuffer
 MYRDBMS_XFER_TO_SERVER=DRSERVER012

Another Sybase Server

note we are using index stats and 60 buckets

 MYSYBASE_SYBASE_COMPRESSION_LEVEL=1
 MYSYBASE_COMPRESS_LATEST=y
 MYSYBASE_DO_EXTERNAL_COMPRESS=y
 MYSYBASE_DO_DBCC=n
 MYSYBASE_DO_DUMP=y
 MYSYBASE_DO_LOAD=n
 MYSYBASE_DO_PURGE=y
 MYSYBASE_DO_UPDSTATS=n
 MYSYBASE_DO_UPDSTATS=y
 MYSYBASE_SERVER_NAME=MYSYBASE
 MYSYBASE_IS_REMOTE=y
 MYSYBASE_NUM_DAYS_TO_KEEP=1
 MYSYBASE_NUM_DAYS_TO_KEEP=2
 MYSYBASE_SERVER_DIRECTORY=/export/home/sybase-dump
 MYSYBASE_SERVER_HOSTNAME=ssdb
 MYSYBASE_SERVER_HOSTNAME=ssdb2
 MYSYBASE_UPD_STATS_FLAGS=-i
 MYSYBASE_UPD_STATS_FLAGS=-iv60
 MYSYBASE_XFER_BY_FTP=Y
 MYSYBASE_XFER_FROM_DB=tlm|mlp_tlm
 MYSYBASE_XFER_SCRATCH_DIR=/apps/sybmon/tmp
 MYSYBASE_XFER_TO_DB=tlm|mlp_tlm
 MYSYBASE_XFER_TO_DIR=/export/home/sybase-dump
 MYSYBASE_XFER_TO_HOST=elmssdb
 MYSYBASE_XFER_TO_HOST=elmssdb2
 MYSYBASE_XFER_TO_SERVER=MYSYBASEDR

Another Sql Server

 SQLSVR1_COMPRESS=D:/PROGRA~1/MKSToo~1/mksnt/mkszip.exe -l1
 SQLSVR1_SYBASE_COMPRESSION_LEVEL=0
 SQLSVR1_DO_CLEARLOGSBEFOREDUMP=n
 SQLSVR1_DO_EXTERNAL_COMPRESS=n
 SQLSVR1_DO_DUMP=n
 SQLSVR1_DO_LOAD=y
 SQLSVR1_DO_UPDSTATS=n
 SQLSVR1_SERVER_NAME=SUNGARD
 SQLSVR1_DATABASE_IGNORE_LIST=model|tempdb|sybsyntax|master|msdb|pubs|pubs2|Northwind
 SQLSVR1_IS_REMOTE=n
 SQLSVR1_CLIENT_PATH_TO_DIRECTORY=//sqlsvr1/f$/logshipbuffer
 SQLSVR1_NUMBER_OF_STRIPES=0
 SQLSVR1_NUM_BACKUPS_TO_KEEP=1
 SQLSVR1_SERVER_DIRECTORY=D:/backups
 SQLSVR1_XFER_BY_FTP=N
 SQLSVR1_XFER_FROM_DB=*
 SQLSVR1_XFER_TO_DB=*
 SQLSVR1_XFER_TO_DIR=//adsdr007/e$/logshipbuffer
 SQLSVR1_XFER_TO_DIR_BY_TARGET=E:/logshipbuffer
 SQLSVR1_XFER_TO_SERVER=DRSERVER007

Some databases are copied and loaded once a night

 SYB1_TO_SYB2_SERVER_NAME=SYB1
 SYB1_TO_SYB2_SERVER_DIRECTORY=/export/home/sybase-dump
 SYB1_TO_SYB2_DATABASE_IGNORE_LIST=model|tempdb|sybsyntax|clientdata|shareddata|archivedata|xREP1_RS_RSSD|master|syblogins|sybsystemdb|sybsystemprocs|xvue|xvue_test|xvue_test2|xvue_new|xvue3|xvue2
 SYB1_TO_SYB2_DO_REORG=n
 SYB1_TO_SYB2_DO_EXTERNAL_COMPRESS=n
 SYB1_TO_SYB2_COMPRESS_LATEST=n
 SYB1_TO_SYB2_DO_UPDSTATS=n
 SYB1_TO_SYB2_DO_DBCC=n
 SYB1_TO_SYB2_DO_DUMP=y
 SYB1_TO_SYB2_DO_LOAD=y
 SYB1_TO_SYB2_DO_ONLINEDB=y
 SYB1_TO_SYB2_DO_PURGE=y
 SYB1_TO_SYB2_IS_REMOTE=y
 SYB1_TO_SYB2_UPD_STATS_FLAGS=-iv60
 SYB1_TO_SYB2_SERVER_HOSTNAME=syb1
 SYB1_TO_SYB2_XFER_TO_HOST=syb2
 SYB1_TO_SYB2_XFER_TO_SERVER=SYB2
 SYB1_TO_SYB2_XFER_FROM_DB=blahsys|blahmgmt|blahdata|blahbatch|consolidation|mlparchive|dbvue
 SYB1_TO_SYB2_XFER_TO_DB=blahsys|blahmgmt|blahdata|blahbatch|consolidation|mlparchive|dbvue
 SYB1_TO_SYB2_XFER_BY_FTP=Y
 SYB1_TO_SYB2_XFER_SCRATCH_DIR=/export/home/tmp
 SYB1_TO_SYB2_XFER_TO_DIR=/export/home/sybase-dump

Another Log Shipping Example

Existing DB MXDBDR..repproddb goes to MXMYRDBMS_..dr_repproddb

   {Normal Backup Params)
   MYRDBMS_XFER_TO_SERVER=MXMYRDBMS_
   MYRDBMS_XFER_TO_DB=dr_repproddb
   MYRDBMS_XFER_FROM_DB=repproddb
   MYRDBMS_XFER_TO_HOST=mxproddb
   MYRDBMS_XFER_TO_DIR=/export/home/sybase-dump
   MYRDBMS_XFER_SCRATCH_DIR=/export/home/sybase/tmp
   MYRDBMS_XFER_BY_FTP=Y
   MYRDBMS_DO_AUDIT=N

   perl backup.pl -JMYRDBMS -Drepproddb

   nightly: perl backup.pl -JMYRDBMS -n -Drepproddb
   hourly:  perl backup.pl -JMYRDBMS -t -Drepproddb

USAGE

backup.pl -S SERVER -l|-f

DESCRIPTION

backup.pl is the main driver script for your operations and will appropriately call the other scripts in this package to get its work done. It is probably the only program you will add to your scheduler. It reads the configuration file and process servers based on the options you have specified. Operations are performed in the following order:

        Purge Old Dumps (if necessary use rsh)
        Dbcc Databases
        Dump Tran Log
        Full Dump
        Check for Required Database Loads
        Update Statistics
        Run Audit
        Optional Table Level Bcps
        Compress The Dumps (if necesary use rsh)
        Rebuild Indexes

USAGE

Basic Usage:

 Usage: backup.pl -JJOB -t|-f [-dh]

Advanced Usage:

Usage: backup.pl -JJOB -t|-f [-dh] -mmode

This is THE Master backup script. This script performs appropriate tasks as defined by maintenance plans (specified with -JJOB) which are defined in configure.cfg (located in backup scripts directory or one level above it).

 -x noexec mode                         (purges will exec but other stuff wont)
 -n copy but dont load for logshipping
 -t for transaction log dumps only
 -f for full backups    (default).
 -m mode = SSH|FTP|RSH  (override communication mode)
 -d is debug mode
 -b batchid (for alarming)
 -h is html output.
 -s skip transfer and load step
 -DDB_LIST (pipe separated) restriction on databases
 -RlpdDuabCir will run only the (l)oad, (p)urge, (d)ump, (D)bcc, (r)eorg,
      (u)pdate stats, (a)udit, (b)cp, (C)ompress, and (i)ndex steps.
 -p dont purge tranlogs

SYNOPSIS

This is the master driver for the backup scripts. It relies on the Job being defined in the configure.cfg (located in backup scripts directory or one level above it). It follows instructions and performs the appropriate tasks as needed. -t for transaction log dumps only, -f for full backups. -d is debug mode and -h is html output.

You can also specify -DDB_LIST to restrict databases operations are performed on further.

Notes

A transaction log dump (with no_log) is always done prior to the full dump.

tempdb and model are NEVER dumped

Optionally Compress the dumps

Update Statistics and Recompile will never work on tempdb/model

Audit Reports require extended procs to be installed

On production servers, you will also wish to run backup.pl -l. This 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. Log dumps are placed in dated files in the .../logdumps sub directory.

SYNOPSIS

Creates a configuration report for a server that can be used to recreate that server from scratch.

AUTHOR

By Ed Barlow

USAGE

config_report.pl -USER=SA_USER -SERVER=SERVER -PASSWORD=SA_PASS [-OUTFILE=FILE]

        -or-

   -BATCHID=BatchDesignator
   -OPTDIAG=path_to_optdiag # if set run optdiag output
   -OUTDIR=dir              # directory for output - otherwise stdlocation
   -STDLOCATION             # make the output file be in standard location ~gem/data/...
   -OPTDIAG=path_to_optdiag
   -HTML                    # output in html format
   -NOBCP                   # Do not bcp out key files
   -ERRORLOG=errorlog   (optional)
   -LOGFILE=sessionlog (optional)
   -KEEPVERSIONS={versions to keep (default=7)}

Creates a configuration report for a server that can be used to recreate that server from scratch.

DESCRIPTION

This program creates a configuration report for a server that can be used to recreate that server from scratch. This is intended as documentation for use by the system administrator in case your server crashes or has a problem. This program should produce virtually everything about your server that you might care about except user object DDL.

Requires: Requires the extended stored procedure library

WHAT IT PRINTS

   SERVER:  srvname
   @@version
   helpdb
   configure
   helpmirror
   vdevno
   helpdevice
   helplogin
   helpuser by db
   all the reverse engineering routines

AUTHOR

By Ed Barlow

DESCRIPTION

An inteligent dbcc that works on table sizes and tries to keep running only for a specified amount of minutes. Note that DBCC does not stop if it might go over that limit, so we run until the total time taken exceeds the MINUTES passed in.

If you pass a database list it will create the file listing in FILE. This consists of database, object, and pages. This list is run through sequentially. The system calculates an estimate of the number of seconds that the table will take. If time_taken + estimate > minutes_parameter, then the dbcc checktable will not run.

If you pass in an ESTIMATEFILE as an optional parameter - better statistics are automatically created and managed. Default statistics are based on total pages processed and total time taken *in this run*. Better statistics (from the ESTIMATEFILE) are based on the historical average of rows per second for a single table and grouped into categories based on table size. The default estimate will be conservative because there is overhead factored in. The better statistics are required if you wish to work on files other than in straight linear order - smallest to fastest.

Say you did the first 500 tables (smallest) on one saturday. The use of estimated statistics could allow you to do the next 40 (largest) on the following saturday. You could not do this safely with default stats.

USAGE

USAGE: dbcc_checktable.pl -UUSER -SSERVER -PPASS -FFILE -TMINUTES -DDATABASE_LIST -EESTIMATEFILE

If you pass a database list it will create the file listing in FILE. MINUTES is the number of minutes to run before stopping (estimate). ESTIMATEFILE is optional parameter - to keep track of time estimates.

AUTHOR

By Ed Barlow

USAGE

dbcc_db.pl -USA_USER -SSERVER -PSA_PASS -DDB_LIST

   -p          print all output from dbcc
   -T Type       Sybase or Odbc
   -D DB_LIST    pipe separated list with wildcards of databases
   -d       debug mode
   -h       html output
   -o outputdir  for raw output (file is server.db.dbcc.datestamp)
   -e errorlog   optional
   -l sessionlog optional

   Runs standard dbcc on the list of databases

DESCRIPTION

Runs dbcc checkdb, checkalloc, and checkcatalog on your databases. Only prints output if there is a problem.

DESCRIPTION

Backs up database or does a transaction log dump. Can be set for tran log dumps with -t or will, by default, perform full database dumps. Places database dumps in DIRECTORY/$SERVER.$(database)_dbdump.yyyymmdd.hhmmss and tran log dumps into DIRECTORY/$SERVER.$(database).logdump.yyyymmdd.hhmmss It will ignore databases as appropriate, following the following rules:

             1) It will not perfore a transaction log dump if "trunc. log on chkpt" set

             2) model,tempdb, and sybsyntax are not dumped unless specifically identified with -d option

             3) It will not dump transaction logs if data and log on the same device

It will also warn if user databases have select into set but do not have trunc. log on chkpt set. The dump files will have a .tmp in them until the dump is completed, at which point they will be renamed with the .dbdump or .logdump extension if the -r option is specified.

USAGE

dump_database.pl -nnum_stripes -USA_USER -SSERVER -PSA_PASS -DDB_LIST -oDIRECTORY -tdhfc -X0-10 -MType

   -n Num stripes (.SX appendended to dump name)
   -v Num         use devices: Num dumps per subdirectory (device_#)
   -D DB_LIST     (pipe separated list with wildcards of databases)
   -o DIR         (output directory)
   -t             (Dump Transaction Log only)
   -f             (Full Dump - default)
   -M             Server Type for DBI connection String (ODBC, Sybase...)
   -d             (debug mode - for odbc errors use -z)
   -h             (html output)
   -e errorlog    (optional)
   -l sessionlog  (optional)
   -s time        (set time on dump files)
   -c             (clear tran log prior to dumping)
   -X             Compression Level On The Dumps (Sybase only)
   -Y             force std output prints (wont print to stdout if cron job)

   Dump a database to the specified directory in with the name
      {SERVER}.{DATABASE}.dbdump.yyyymmdd.hhmmss
   or (if striped)
      {SERVER}.{DATABASE}.dbdump.yyyymmdd.hhmmss.S?

MORE DESCRIPTION

Backs Up Databases. Can be set for tran log dumps with -t or will, by default, back up full database dumps. Places databases in

   DIRECTORY/$SERVER.$(database)_dbdump.yyyymmdd.hhmmss

and tran log dumps into

   DIRECTORY/$SERVER.$(database).logdump.yyyymmdd.hhmmss

It will ignore databases as appropriate. Specifically the ruls are as follows:

   Cant Tran Dump if "trunc. log on chkpt" set
   model,tempdb, and sybsyntax are not dumped unless specifically set
      with -d option
   Cant Tran Dump if data and log on the same device

It will also warn if user databases have select into set but do not have trunc. log on chkpt set.

USAGE

fix_logship.pl -Jjob -Ssourcedir -hsourceUnixHostname -Ttargetdir -ttargetUnixHostname -Ddatabase -UN -u/usr/bin/gunzip

 -f full dumps resync (will load dumps too)
 -y since yesterday (24 hours)
 -s since time - yyyymmdd.hhmmss
 -p purge target only files
 -N nocopy - just rename .done extensions (must pass -U)
 -U is used to move .done to unnamed extensions\n";
 -P print differences
 -u uncompress program for source side
 -D database name - comma separated list

 -F filespec - match patern for files.  Otherwise the following will be used
   .$db.logdump.\d+.\d+   if database name is passed
   logdump.\d+.\d+      if no database is passed

where \d+ is the perl string for a set of digits

SYNOPSIS

Works on both unix and windows. Will sync two directories. By default it only copies from the source to the target (no removes on target) but with full dumps or if -p is called it will purge the target too.

The only caveat of this program is that the way it copies files is to use a temporary local directory. This means that if you running from machine a to copy SYBA to SYBA_DR, it copies SYBA->a and then a->SYBA_DR. Two copies. Might be slow with large databases. Recommend that you run locally (on either SYBA or SYBA_DR) if your databases are larger than a 100 gigs or so.

EXAMPLE

IMDBDATE=`/usr/local/bin/perl /apps/sybmon/dev/ADMIN_SCRIPTS/dbi_backup_scripts/get_latest_filedate.pl -C"ssh sybase@sybhost ls /export/home/sybase-dump" -Dmydb` echo loading db for date=$IMDBDATE echo ---- DB=mydb DATE=$IMDBDATE >> $FILE echo set nocount on >> $FILE echo 'select getdate()' >> $FILE echo go >> $FILE echo load database mydb >> $FILE echo from \"compress::/export/home/sybase-dump/sybhost.mydb.dbdump.$IMDBDATE.S1\" >> $FILE echo stripe on \"compress::/export/home/sybase-dump/sybhost.mydb.dbdump.$IMDBDATE.S2\" >> $FILE echo stripe on \"compress::/export/home/sybase-dump/sybhost.mydb.dbdump.$IMDBDATE.S3\" >> $FILE echo stripe on \"compress::/export/home/sybase-dump/sybhost.mydb.dbdump.$IMDBDATE.S4\" >> $FILE echo go >> $FILE echo online database mydb >> $FILE echo go >> $FILE echo 'select getdate()' >> $FILE echo go >> $FILE

USAGE

 kill_users_db.pl -USA_USER -SSERVER -PSA_PASS -Ddb -d

DESCRIPTION

Kills all users in a database.

SYNOPSIS

This is the primary driver to load more than one transaction log. It applies file filters and runs load_database.pl on each file that survives the filtering in order. It renames the files to append .done to the ones applied.

USAGE

load_all_tranlogs.pl -JJOBNAME

or

load_all_tranlogs.pl

   -USER=SA_USER      -SERVER=SERVER      -PASSWORD=SA_PASS
   -DATABASE=db       -INDIR=file_root    -TYPE=Type
   -DESTDIR=dest_dir  -JOB=job            -HOSTNAME --SKIPPRIOR
   -UNIXLOGIN         -DELETE             -UNIXPASSWORD -STRIPES
   -SOURCESERVER - (for reporting only - this is the source server)
   -FULL   - full database loads (only on microsoft for now)
   -DEBUG debug mode  --FILEPATERN=pat --COMPRESSED (sybase compression)
   -SKIPDONE skip files with .done extension
   -MICROSOFT microsoft sql svr format (see below)
   -RENAME rename files to .done extension
   -INDIR identifies a directory / files as seen from this program
   -DESTDIR identifies the same directory/files as seen by remote server
      (or pass HOSTNAME)

   Will Load, in order, tranlogs that have the following name format
      {SERVER}.{DATABASE}.dbdump.yyyymmdd-hhmmss
   or {DB}_tlog_yyyymmddhhmm.TRN (sql server format - if --MICROSOFTpassed)

DESCRIPTION

Works on standard SQL server and My standard file naming conventions. If your files are not using standard naming (yyyymmdd.hhmmss) then that needs to be looked at.

OTHER NOTES

If you get in a situation where your files are jumbled, just run with --SKIPPRIOR. This option will stop the check that skips logs once an error is found. All logs will be applied not in order! This can be used to unjumble tran logs when there are ordering issues.

USAGE

load_database.pl -nnum_stripes -USA_USER -SSERVER

                           -PSA_PASS -Ddb -ifile_root -tdhk -MType

   -n Num stripes (.SX appendended to dump name)
   -v Num         use devices: Num dumps per subdirectory (device_#)
   -D DB_NAME     (may NOT contain wildcards)
   -i DIR         (input file root directory)
   -k             dont kill users
   -M servertype  Sybase or ODBC
   -t             (Transaction Log only)
   -d             (debug mode - for dbi debugging use -z)
   -h             (html output)
   -r extension   (rename file extension (dbdump part of name) when done)
   -e errorlog    (optional)
   -l sessionlog  (optional)
   -s silent mode (only print summary)
   -X             dumps are compressed using internal sybase compression
   -Y             force std output prints (wont print to stdout if cron job)
   -O             no online database needed

   Load a database from the specified file in with the name
      {SERVER}.{DATABASE}.dbdump.yyyymmdd-hhmmss[.S?]
   where .S? represents optional stripe numbers if the backups are striped.
   The Root of the file (specified by -i) is the full name (excluding the
   .S? stripe name).

If -t and no file exactly matches the file_root, ALL files that start with the root are loaded in order. If full load, and no file matches the -i root, the LATEST file starting with the root will be loaded.

EXAMPLE

Load 4 striped compressed dumps into dbname (files have .S1 .. .S4 suffixes)

load_database.pl -n4 -Usa -SSVRNAME -Ppass -Ddbname -i/export/home/dumps/SRVNAME/dbdumps/SRVNAME.dbname.dbdump.20061231.210404 -X

DESCRIPTION

Loads Databases. Can be set for tran log dumps with -t or will, by default, back up full database dumps.

Loads a database from either a full backup or a transaction log dump. The file root of the command is the path name to the dump file. It should exclude the .S[num] extension for striped dumps. The program attempts to be intelligent about what file to load. If a tran log is implied and no file exactly matches the file_root, the -i option identifies a file root and ALL files that match the root are loaded in order of timestamp (from the file name). If there is no file with exactly the correct root on a full load, the LATEST file matching the root of the command will be loaded. The -r prefix can be used to rename files when they are done loading. For example, you may use -rloaded to rename SRVR.MYDB.dbdump.20001109.022233 to SRVR.MYDB.loaded.20001109.022233. The database name may NOT contain wildcards.

Standard naming for dump files should be

      {SERVER}.{DATABASE}.dbdump.yyyymmdd-hhmmss
   or
      {SERVER}.{DATABASE}.dbdump.yyyymmdd.hhmmss.S1
     through
      {SERVER}.{DATABASE}.dbdump.yyyymmdd.hhmmss.SN

SYNOPSIS

rebuild.pl - Rebuild Hashbang And use lib statements

COPYRIGHT

 Copyright (c) 2002-3 By Edward Barlow
 All Rights Reserved
 Explicit right to use can be found at www.edbarlow.com
 This software is released as free software and should be shared and enjoyed

USAGE

Usage: G:\dev\AHMR52~F\bin\rebuild.pl [--UNSET] --OVERRIDE --LIBDIR=DIRECTORY,DI RECTORY --FILENAME=FILENAME,FILENAME --DIRECTORY=DIRECTORY,DIRECTORY [--DEBUG] - -PERL=PERL

   USE --UNSET to reset your perl to default hashbangs

DESCRIPTION

Resets the perl hashbang lines at the top of the file. If the first line starts with a hashbang #! then the next part is a command to reformat_file. This can either be of the format

    #!/usr/local/bin/perl

or if you pass --UNSET

   : # use perl
       eval 'exec perl -S $0 "$@"'
       if $running_under_some_shell;

These lines are reformated correctly based on the perl in your path or based on the perl you pass

USAGE

   rebuild.pl file file file...

   rebuild.pl *

   rebuild.pl -D.

   rebuild.pl -u

      to undo changes to generic format

 --OVERRIDE will work on restricted file types

NOTES

Reset perl on first or first 3 lines as necessary. If the first line starts with a hashbang #! then that word is reformatted as necessary. If the first 3 lines looks like:

   : # use perl
   eval 'exec perl -S $0 "$@"'
   if $running_under_some_shell;

skips files named configure.pl or rebuild.pl. Those should always be full path name to the perl executable using the above syntax.

DESCRIPTION

This procedure builds missing indexes in a server. It relies on a file extract from another server to understand what indexes to rebuild. The magic of this proc is in its error handler, which understands the errors that can be generated by index generation and in its duplicate row processing. You can rely on the fact that when this program is done, your indexes will be correct. Use it to maintain indexes between identical servers.

Note: this is not a script to recreate existing indexes. This is a script that corrects indexes in a server that might be incorrect by making them like the indexes in a file (which can be extracted from another server).

The extract file is is just index create scripts, 1 per line. For syntactical clarity, it is recommended that the extract file be based on the extended stored procedure sp__revindex, which is found in Ed Barlows extended procedure library. The program can be used to extract output into a file (using -o) which can later be run into another server (using -i). Duplicate rows are cleaned up if the -c option is used

USAGE

Usage: rebuild_index.pl -UUSER -PPASS -SSERVER -DDB [-oOUTFILE|-iINFILE] -c

Index Comparator. Extracts indexes for a DB and then allows you to compare the saved indexes to the indexes on your servers.

 -o : if outfile specified then creates a file with the indexes from the
      server.  This file can be used as an infile for further comparisons.
 -c : correct duplicate keys in indexes.  Deletes duplicate data.  One random
      row in unique indexes will be kept.
 -r : report only - this will report missing indexes
      as well as duplicate data but will not rebuild any indexes.

note that this program requires sp__revindex (extended stored proc library)

USAGE

reorg_sybase.pl -USER=SA_USER -DATABASE=db -SERVER=SERVER -PASSWORD=SA_PASS

or

reorg_sybase.pl -RUNONALLSERVERS

Additional Arguments

   --OUTFILE   : Output File (all output also goes here)
   --ASNEEDEDONLY
   --MAXTIMEMINS
   --CONFIG_FILE
   --HTML
   --DEBUG
   --REORG_COMPACT
   --REORG_REBUILD
   --NOEXEC

ARGUMENTS

You can run on all your servers with --RUNONALLSERVERS or you can specify a server with --SERVER/--USER/--PASSWORD. If no --DATABASE is specified, it will run on all user databases.

The command run will be reorg REORG_COMPACT (--REORG_COMPACT) or reorg REORG_REBUILD (--REORG_REBUILD).

If you specify --ASNEEDEDONLY, it will only reorg tables that need to be reorged. The default behavior is to reorg all tables. If not, it will get the info from systabstats looking for tables with forwarded/deleted rows.

You can also pass in --NOEXEC to print what will happen but not run the commands

Output can be directed to --OUTFILE and can be saved in HTML format if --HTML is passed.

The MAXTIMEMINS argument will specify the max time in minutes that the entire operation can run (all servers all databases). You can specify --CONFIG_FILE to store the order and time of this run for the future...

USAGE

set_dboption.pl -USA_USER -SSERVER -PSA_PASS -DDB_LIST -Ooption -Vvalue -k

  multi database option setting with checkpoint and kills if those r needed
  (ie... issue kills if set single user and user in the db)

   -O OptionName : option to set
   -D DB_LIST: may include wildcards or pipe separated list of databases
   -d debug mode
   -h html output
   -l logfile
   -e errorfile
   -k do kills (otherwise just prints it)
   -V value true or false

DESCRIPTION

Sets Db Options As Appropriate. DB_LIST can contain wildcards. Kills users in database if read only, single user or dbo use only. Runs chekcpoint to close out the functionality.

USAGE

show_configvars.pl

SYNOPSIS

This is a diagnostic utility

show_configvars.pl

CURRENTLY DEFINED PLANS ARE :

SYB1 PLATINUM MMMDBDB_COPY SYBDEV1 SYBDEV2 SYBDEV2_UPDSTATS MLPSYBDBCC CALYDB SYB2_UPDSTATS IMAGREP2SYB ADS060 ADS088 SYB1_TO_SYB2

or

show_configvars.pl -JJOB

show variables for the plan

 debug: CONFIG{AUDIT_PURGE_DAYS}=30
 debug: CONFIG{BASE_BACKUP_DIR}=/apps/sybmon/dev/data/BACKUP_LOGS
 debug: CONFIG{BCP_COMMAND}=
 debug: CONFIG{BCP_TABLES}=
 debug: CONFIG{CODE_LOCATION_ALT1}=
 debug: CONFIG{CODE_LOCATION_ALT2}=
 debug: CONFIG{COMPRESS}=/usr/local/bin/gzip
 debug: CONFIG{SYBASE_COMPRESSION_LEVEL}=0
 debug: CONFIG{COMPRESS_LATEST}=n
 debug: CONFIG{DBCC_IGNORE_DB}=
 debug: CONFIG{DO_AUDIT}=y
 debug: CONFIG{DO_BCP}=n
 debug: CONFIG{DO_CLEARLOGSBEFOREDUMP}=y
 debug: CONFIG{DO_EXTERNAL_COMPRESS}=y
 debug: CONFIG{DO_DBCC}=y
 debug: CONFIG{DO_DUMP}=y
 debug: CONFIG{DO_INDEXES}=n
 debug: CONFIG{DO_LOAD}=n
 debug: CONFIG{DO_ONLINEDB}=n
 debug: CONFIG{DO_PURGE}=y
 debug: CONFIG{DO_REORG}=y
 debug: CONFIG{DO_UPDSTATS}=y
 debug: CONFIG{SERVER_NAME}=DACSSERVER
 debug: CONFIG{DUMP_DIR_BY_BKSVR}=/export/home/sybase-dump/DACSSERVER/dbdumps
 debug: CONFIG{DUMP_FILES_PER_SUBDIR}=
 debug: CONFIG{DATABASE_IGNORE_LIST}=model|tempdb|sybsyntax
 debug: CONFIG{IGNORE_SERVER}=
 debug: CONFIG{IS_REMOTE}=y
 debug: CONFIG{JOBNAME}=DACSSERVER
 debug: CONFIG{CLIENT_PATH_TO_DIRECTORY}=//samba/sybmon/dev/data/BACKUP_LOGS
 debug: CONFIG{CLIENT_PATH_TO_DUMP_DIR}=
 debug: CONFIG{CLIENT_PATH_TO_LOG_DIR}=
 debug: CONFIG{MAIL_HOST}=mail1.mlp.com
 debug: CONFIG{MAIL_TO}=ebarlow@mlp.com,ryi@mlp.com
 debug: CONFIG{NT_CODE_LOCATION}=//samba/sybmon/dev
 debug: CONFIG{NT_PERL_LOCATION}=C:/perl/bin/perl.exe
 debug: CONFIG{NUMBER_OF_STRIPES}=4
 debug: CONFIG{NUM_BACKUPS_TO_KEEP}=2
 debug: CONFIG{NUM_DAYS_TO_KEEP}=4
 debug: CONFIG{SERVER_HOST_LOGIN}=sybase
 debug: CONFIG{SERVER_DIRECTORY}=/export/home/sybase-dump
 debug: CONFIG{SERVER_DUMP_DIRECTORY}=
 debug: CONFIG{SERVER_HOSTNAME}=dacs2
 debug: CONFIG{SERVER_LOG_DIRECTORY}=
 debug: CONFIG{SERVER_HOST_PASSWORD}=
 debug: CONFIG{REORG_ARGS}=--maxtimemins=60 --reorg_compact
 debug: CONFIG{SRVR_DIR_BY_BKSVR}=/export/home/sybase-dump/DACSSERVER
 debug: CONFIG{SUCCESS_MAIL_TO}=ebarlow@mlp.com,ryi@mlp.com
 debug: CONFIG{SYBASE}=/apps/sybase
 debug: CONFIG{TRAN_DIR_BY_BKSVR}=/export/home/sybase-dump/DACSSERVER/logdumps
 debug: CONFIG{UNCOMPRESS}=/usr/bin/gzip -d
 debug: CONFIG{UNIX_CODE_LOCATION}=/apps/sybmon/dev
 debug: CONFIG{UNIX_PERL_LOCATION}=/apps/sybmon/perl/bin/perl
 debug: CONFIG{UPD_STATS_FLAGS}=-i
 debug: CONFIG{XFER_BY_FTP}=
 debug: CONFIG{XFER_FROM_DB}=
 debug: CONFIG{XFER_SCRATCH_DIR}=
 debug: CONFIG{XFER_TO_DB}=
 debug: CONFIG{XFER_TO_DIR}=
 debug: CONFIG{XFER_TO_DIR_BY_TARGET}=
 debug: CONFIG{XFER_TO_HOST}=
 debug: CONFIG{XFER_TO_SERVER}=

DESCRIPTION

Stops your sybase server cleanly

USAGE

Stop_Sybase.pl $opt_S $SAPASSWORD [$SYBASEDIR]

note server must be running on the unix system in question and be using an errorlog named errorlog_$opt_S in $SYBASE/install and a runserver file of RUN_$opt_S in the same directory unless it is called SYBASE (will use defaults of RUN_opt_S and errorlog

COPYRIGHT

Copyright (c) 1996 by Edward Barlow All Rights Reserved

You are welcome to use and redistribute this software, without charge, provided you make no money from it. 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.

AUTHOR

By Ed Barlow

DESCRIPTION

update statistics on all tables in db. Also recompiles. If Db Not Passed, will work on all user databases in the server. Pass -s if you want no output.

USAGE

USAGE: update_stats.pl -UUSER -SSERVER -PPASS [-DDB]

   -p samplepct   adds 'with sampling = samplepct' (sybase 12.5.0.3+)
   -D DB_LIST     (pipe separated list with wildcards of databases)
   -E TABLES      (comma separated list of tables to EXCLUDE)
   -I TABLES      (comma separated list of tables to INCLUDE)
   -s             (silent mode)
   -k             keep old stats (ie no delete stats)
   -d             (debug mode)
   -vVAL                                number of steps/values to use (suggest 60 - dflt 20)
   -h             (html output)
   -i             update index stats
   -a                                   update all stats
   -n                           noexec
   -e errorlog    (optional)
   -l sessionlog  (optional)

ARGUMENTS

You can control pretty much everything here. Will do a delete stats, update stats, and then an sp_recompile on the object in question.