Section 17.5. Backup Automation Through Scripting

17.5. Backup Automation Through Scripting

This section gives you a simple mechanism to back up your Sybase servers. These examples are written in the Bourne shell and give you a minimal set of required server management functionality. They are provided for illustrative purposes, but the scripts can easily be used for production backups of your systems.

17.5.1. Backup Automation Basics

It's a good idea to create a scripts directory on each of your servers. For example, you could call this directory /export/home/sybase-scripts. The first script in this directory is called XSQL and looks like this:

$ cd /export/home/sybase-scripts $ cat XSQL #!/bin/ksh . /export/home/sybase/SYBASE.sh /export/home/sybase/OCS-12_5/bin/isql -Usa SMYSERVER       -Psecret -i/export/home/sybase-scripts/$1       -o/export/home/sybase-scripts/$1.out echo "Script Completed" >> $1.out date >> $1.out

This function of this script should be obvious. The SYBASE.sh file is sourced to set up environment variables, a necessary step if you are running a job out of cron. The XSQL script takes one argument, which is a script file, and runs it, placing the output in a file that is the name of the script file with .out appended.

Let's say you have a database named mydb to back up. You could create a file called backupmydb4way.sql that looks like the following:

$ cat backupmydb4way.sql set nocount on select getdate(  ) go DUMP DATABASE mydb  to "compress::1::/dumps/mydump-stripe1" STRIPE ON " compress::1::/dumps/mydump-stripe2" STRIPE ON " compress::1::/dumps/mydump-stripe3" STRIPE ON " compress::1::/dumps/mydump-stripe4" go select getdate(  ) go

This creates four files in /dumps that represent your backups. Schedule /export/home/sybase-scripts/XSQL /export/home/sybase-scripts/backupmydb4way.sql using cron to create backups at night.

17.5.1.1. A simple update stats script

update statistics is a bit more complicated to run because it must be run on each table in your database. The following script is an example of how to run a SQL command on every table in a database. It runs update index statistics tablename followed by exec sp_recompile tablename on all tables in your database.

$ cat updstatsmydb.sh #!/bin/sh . /export/home/sybase/SYBASE.sh ISQL="$SYBASE/$SYBASE_OCS/bin/isql -Usa Psecret -SMYSERVER -w160" SQLFILE=/tmp/updstats.sql LOGFILE=\Qdate '+updstats2_%Y%m%d'\Q cat << EOF | $ISQL  | sed -e 's/-//g' -e 's/([0-9]* [rows affected]*)//g' > $ SQLFILE select "select convert(varchar,getdate(  ))+ ' upd stats "+name+"'"+char(10)+"go"+char(10)+"update index statistics mydb.dbo."+name+char(10)+"go"+"exec sp_recompile +name+char(10)+"go" from mydb.dbo.sysobjects where type = 'U'  go EOF $ISQL -i$SQLFILE -o$LOGFILE 

This Bourne shell script file runs the commands update index statistics and sp_recompile on all the tables in the database mydb. The /tmp/updstats.sql script is created and then executed at the last line of the script. The /tmp/updstats.sql file contains print statements that print the time and table name followed by an update stats for each table in your database.

Note that char(10) happens to be a newline. The sysobjects table in each database contains a list of all objects in your database. Objects of type U are tables. The /tmp/updstats.sql file created by the script looks like this:

select convert(varchar,getdate(  ))+ ' upd stats tbl1' go update index statistics mydb.dbo.tbl1 go exec sp_recompile mydb.dbo.tbl1 go select convert(varchar,getdate(  ))+ ' upd stats tbl2' go update index statistics mydb.dbo.tbl2 go exec sp_recompile mydb.dbo.tbl2 go <etc>

17.5.1.2. A sample transaction logfiles backup script

You should dump compressed versions of your transaction logfiles, but you need not back these files up using striping. It is also recommended that the script you use to run dump transaction back the files up using 24-hour time stamping. Essentially, this means that the file should have date and time in the format yyyymmdd.hhmmss (year, month, day, hour, minute, second). Using this format ensures that a standard file listing shows your transaction logfiles in order. Here's an example of a command that does this:

DTIME=\Qdate "+Y%m%d.%H%M%S"\Q echo "dump transaction somedb to">> /tmp/dfile.sql echo "\"/export/home/sybase-dumps/somedb.$DTIME.trn\"" >> /tmp/dfile.sql echo go >> /tmp/dfile.sql XSQL /tmp/dfile.sql

17.5.1.3. Schedule backups

You will need to schedule backups of your database using cron or the Windows task scheduler. Here is an example of an entry using crontab:

0 2 * * * /export/home/sybase-scripts/XSQL backupmydb4way.sql

This runs your simple backup at 2:00 a.m. every day. A file /export/home/sybase-scripts/backupmydb4way.sql.out contains a log of the last run. You will need to run a backup of all your databases with the exception of model and tempdb. You can get a listing of your databases by running the stored procedure sp_helpdb.

17.5.1.4. Mailing crontab results

Put a .forward file in the Sybase home directory so that errors are mailed to your email account. This file should contain one email address per line.

17.5.2. Logical Backups

The sybase dump command backs up a single database at a time. If a backup of a single table is needed, this command cannot be used. The dump command cannot be used to copy a database between servers running different operating systems because the backup files are in an operating systemdependent format. The logical (table-level) backup utility bcp backs up only parts of a database or copies data between systems on different operating systems.

bcp provides another key feature. Because it refers to the internal structure of the database, it is an excellent tool to confirm whether there is corruption while also backing up the data. The bcp command displays an error if it has any problems reading the data.

The two drawbacks to bcp are:

• bcp takes much longer than dump to back up a whole database.

• bcp can back up only tables and views. Other database objects (stored procedures, triggers, and so on) must be exported using a different method.

17.5.2.1. Performing a logical backup

bcp can create an export of the entire database, or parts of it. Here are some examples. To copy a database in, use a command like the following:

# bcp mydb.dbo.zyx in /sybackups/zyx.bcp -c -S SERVER -U sa -P mypassword Starting copy... 192 rows copied. Clock Time (ms.): total = 1000   Avg = 5    (192.00 rows per sec.)

To copy a database out, use a command like this:

# bcp master.dbo.sysusages out /sybackups/sysusages.bcp -c -S SERVER -U \    sa -P mypassword Starting copy... 9 rows copied. Clock Time (ms.): total = 1000   Avg = 111    (9.00 rows per sec.)

17.5.2.2. Performing a logical restore

The bcp utility can run in fast or slow mode when importing data into a table. If the table does not have indexes or triggers, bcp uses a fast mode to insert the data. This mode is faster because changes are not logged into the transaction log. In fact, after this is done, a transaction log dump is invalid until a full database dump is done. Fast mode is used when a large amount of data must be inserted into a table. Remember to run a full database dump when the bcp completes.

If an index or trigger exists on the table, the slower, logged mode of bcp is used. Be careful when inserting large amounts of data. One prerequisite before using bcp to restore data into a table is to make sure the table exists. bcp does not create objects; it just transfers data in and out of the database. bcp is also handy when you need to import data from a delimited data source such as a comma-separated set of values (CSV) file. The latter topic is beyond the scope of this book, but more information can be found in the Sybase utilities manual. Here, we will discuss how to store table data in a file and how to restore it back into a database.

The main syntax difference between using bcp to import data into a table versus out of a table is changing the word out to in. All other parameters are exactly the same. A few important factors that do not come into play when using bcp to export data are - e errorfile and -m maxerrors. The -m option specifies the maximum nonfatal errors bcp allows before quitting. The -e option specifies the error file where the data rows from the bcp file that caused the nonfatal errors are stored for later review. (In both cases, any error messages display at the terminal.) These parameters allow the bcp command to continue after encountering nonfatal errors and to record the errors for later correction.

When the bcp file is created using the -c option, the records are stored in a character-based, transportable record format. This is very handy when adjustments need to be made to the data before using bcp to import them back into a database. This format allows the use of a simple text editor to make the changes. For example, if the third column in the bcp file represents department names, and one of the names has been changed, use an editor to open the file and do a replacement of the old name with the new name. Also, if a number of records are failing, and they are preventing the bcp -in from continuing, edit the file, and delete the problem lines. If you are using the c option to bcp, you will probably also need to specify the t (field terminator) argument. See the earlier section on bcp regarding the use of the t argument.

One consideration when using bcp to restore system tables is that the database always contains certain default entriessuch as the syslogins entry sa (an abbreviation for system administrator). When trying to restore using a bcp of this table, the bcp will fail because of the unique index on this table. To get around this, copy the syslogins bcp file and remove the entries from the copy that is already in the syslogins table. Once these records are gone, the bcp import will succeed. You can also set the batch size option to 1 row with b1. This causes duplicate rows to be ignored.

17.5.2.3. Auditing using bcp

The system tables suggested for auditing should be copied out of your server on a weekly basis. Use the -c option enables you to view the data in the datafiles even if the dataserver is down.

$ cat sysaudit_bcp.ksh cd /sysbackups bcp master.dbo.sysusages out sysusages.bcp c T~ -SSYB_MYDB -Usa Pmypassword bcp master.dbo.syslogins out syslogins.bcp c T~ -SSYB_MYDB -Usa Pmypassword bcp master.dbo.sysloginroles out sysloginroles.bcp c T~ -SSYB_MYDB -Usa -Pmypassword bcp master.dbo.sysdevices out sysdevices.bcp c T~ -SSYB_MYDB -Usa Pmypassword bcp master.dbo.sysdatabases out sysdatabases.bcp c T~ -SSYB_MYDB -Usa Pmypassword bcp master.dbo.syscharsets out syscharsets.bcp c T~ -SSYB_MYDB -Usa Pmypassword bcp master.dbo.sysconfigures out sysconfigures.bcp c T~ -SSYB_MYDB -Usa Pmypassword bcp master.dbo.sysservers out sysservers.bcp c T~ -SSYB_MYDB -Usa Pmypassword bcp master.dbo.sysremotelogins out sysremotelogins.bcp c T~ -SSYB_MYDB -Usa Pmypassword bcp master.dbo.sysresourcelimits out sysresourcelimits.bcp c T~ -SSYB_MYDB -Usa Pmypassword bcp master.dbo.systimeranges out systimeranges.bcp c T~ -SSYB_MYDB -Usa Pmypassword