#!/bin/sh 

# name: dr.sh (was weekend.sh)
# author: Todd Boss
# purpose: script meant to run from cron that takes as a parameter
#          the SQL server name desiring to have dbcc's run on it.
#
# creation history
# date		author	purpose
# 12/20/96	T.Boss	creation
# 12/23/96	T.Boss	modified to use $server as parameter to script
#		        and functions...much nicer looking code.
# 2/25/97	T.Boss	added CC to list of servers checked
# 3/24/97	T.Boss	added in Disaster Recovery obtainment function,
#			changed name to weekend.sh
# 4/2/97	T.Boss	changed $server.databases to use /tmp not inplace,
#			added exception processing to exclude certain dbs
# 4/3/97	T.Boss	cleaned old servers from pw populating, did major
#			overhaul of get_dr functions to fix master backups, 
#			get buildmaster -yall based on server version
# 4/17/97	T.Boss	added crimson_dev to processing
# 4/21/97	T.Boss	split from dbcc, added a number of system table 
#			functions to drmisc, put sa password inside tsql script
# 7/8/97	T.Boss	moved to /export/syb_ops, cleaned up code a bit, 
#  			added servers, fixed prod11 info
# 7/10/97	T.Boss	added syscharsets to drmiscsystables, sysconfigures to
#			drconfigure functions
# 8/5/97	T.boss	added new_crimson to login info
# 8/18/97	T.Boss	fixed password file information

# usage information below: if parameter omitted, script exits.

server=$1

if [ -z "$server" ]
then
   echo "Usage: dr.sh "
   exit 1
fi

# get_dbs populates a flat file w/ the databases in a server, used to
# parse through for each dbcc iteration.  File ($server.databases) is
# deleted at end of script.  Required b/c no array functionality exists
# in plain bourne shell

get_dbs ()
{
  server=$1
  ISSA="/export/sybase11/bin/isql -Usa -S$server -I/export/sybase11/interfaces" 
$ISSA -o /tmp/$server.databases << EOTSQL
$sapw
select name from sysdatabases
go
EOTSQL
}

###### Disaster Recover functions: ########

# the goal of these functions is to collect all disaster recover information 
# for each server.  Split in to several functions b/c not all sql server 
# versions have all features

### Sysusages
drsysusages ()
{

   $ISSA -o $DRDIR/$server.sysusages << EOTSQL
$sapw
print "***"
print "*** Sysusages (ordered by dbid) ***"
print "***"
select * from sysusages order by dbid 
go

print "***"
print "*** Sysusages (ordered by vstart) ***"
print "***"
select * from sysusages order by vstart
go
EOTSQL
}

### Sysdatabases, sp_helpdb output
drdatabases ()
{
   $ISSA -o $DRDIR/$server.databaseinfo << EOTSQL
$sapw
print "***"
print "*** select * from sysdatabases ***"
print "***"
select * from sysdatabases
go
print "***"
print "*** sp_helpdb output"
print "***"
go

EOTSQL

   counter=0
   linecount=`wc -l /tmp/$server.databases | awk '{ print $1 }'`
   cat /tmp/$server.databases | while read database
   do
      counter=`expr $counter + 1`
      if [ $counter -gt 2 ] && [ $counter -lt $linecount ]
      then
$ISSA -o /tmp/sp_helpdb.tmp << EOTSQL
$sapw
sp_helpdb $database
go
EOTSQL
      fi
      cat /tmp/sp_helpdb.tmp >> $DRDIR/$server.databaseinfo 
      cat /dev/null > /tmp/sp_helpdb.tmp      
   done
}

### sysdevices, sp_helpdevice
drdevices ()
{
   $ISSA -o $DRDIR/$server.deviceinfo << EOTSQL
$sapw
print "***"
print "*** select * from sysdevices"
print "***"
select * from sysdevices
go

print "***"
print "*** sp_helpdevice output"
print "***"
exec sp_helpdevice
go
EOTSQL
}

### syslogins
drlogins ()
{
   /export/sybase11/bin/bcp master..syslogins out \
     $DRDIR/$server.syslogins.bcp_out -Usa -P$sapw -S$server -c 
}

### Misc minor system tables: bcp of syscharsets,
### sysservers, sysusers, syssegments, and sysremotelogins
drmiscsystables ()
{
   /export/sybase11/bin/bcp master..syscharsets out \
     $DRDIR/$server.syscharsets.bcp_out -Usa -P$sapw -S$server -c 

   $ISSA -o $DRDIR/$server.miscsystables << EOTSQL
$sapw
print "***"
print "*** select * from sysservers"
print "***"
select * from sysservers
go

print "***"
print "*** select * from syssegments"
print "***"
select * from syssegments
go

print "***"
print "*** select * from sysusers"
print "***"
select * from sysusers
go

print "***"
print "*** select * from sysremotelogins"
print "***"
select * from sysremotelogins
go

EOTSQL

}

### sysloginroles: only valid past 4.9.2 

drloginroles ()
{
   $ISSA -o $DRDIR/$server.loginroles << EOTSQL
$sapw
print "***"
print "*** select * from sysloginroles"
print "***"
select * from sysloginroles
go
EOTSQL

}

### sp_configure
drconfigure ()
{

   $ISSA -o $DRDIR/$server.sp_configure << EOTSQL
$sapw
print "***"
print "*** sp_configure"
print "***"
exec sp_configure
go

print "***"
print "*** select * from sysconfigures"
print "***"
select * from sysconfigures order by config
go
EOTSQL

}

### dump of master: won't work on 4.9.2 servers b/c they don't support
# the at  functionality...in fact they don't even support dumping
# to a specific file at runtime...always must dump to a created device...

drmaster ()
{
   $ISSA << EOTSQL
$sapw
dump database master to "$DRDIR/$server.master.dmp" at peter_backup
go
EOTSQL
sleep 10
/usr/local/bin/gzip -f $DRDIR/$server.master.dmp
}

### buildmaster -yall.  Basically this won't work b/c the master devices
# are owned and read only to sybase, and we are running as user tboss
# Plus, the two Ibm machines don't allow rsh, rlogin, or rdist.  Nice try.

drbuildmaster ()
{
   rsh $machine buildmaster -yall -d$masterdev > $server.buildmaster-yall
}

##################
# Main processing; case statement exists only b/c different sql servers
# have different sa passwords; these are stored in files in my home
# dir that are read-only to tboss

case $server in
   "prod11")
   sapw=`cat /export/syb_ops/.prod11sapw`
   ver="11"
   masterdev="/u/sybase11/dev/master.device"
   machine="dali"
   ;;
   "prod")
   sapw=`cat /export/syb_ops/.prodsapw`
   ver="10"
   masterdev="/dev/rdsk/c0t2d0s5"
   machine="pop"
   ;;
   "crimson")
   sapw=`cat /export/syb_ops/.crimsonsapw`
   ver="492"
   masterdev="/dev/rdb00"
   machine="degas"
   ;;
   "CC")
   sapw=`cat /export/syb_ops/.CCsapw`
   ver="492"
   masterdev="/dev/rdb00"
   machine="zeus"
   ;;
   "crimson_dev")
   sapw=`cat /export/syb_ops/.crimson_devsapw`
   ver="492"
   masterdev="/dev/rdb08"
   machine="degas"
   ;;
   "peter")
   sapw=`cat /export/syb_ops/.petersapw`
   ver="11"
   masterdev="/dev/rdsk/c0t2d0s3"
   machine="max"
   ;;
   "mdb")
   sapw=`cat /export/syb_ops/.mdbsapw`
   ver="11"
   masterdev="/dev/rdsk/c1t0d0s6"
   machine="156.40.105.151"
   ;;
   "syb_dicom_svr")
   sapw=`cat /export/syb_ops/.syb_dicom_svrsapw`
   ver="11"
   masterdev="/dev/rdsk/c0t1d0s3"
   machine="rr.od.nih.gov"
   ;;
   "new_crimson")
   sapw=`cat /export/syb_ops/.new_crimsonsapw`
   ver="11"
   masterdev="/u/sybase11/dev/new_crimson_master.device"
   machine="dali"
   ;;
esac

get_dbs $server

ISSA="/export/sybase11/bin/isql -Usa -S$server"
DRDIR="/export/syb_ops/dr"

drsysusages 
drdatabases 
drdevices 
drlogins 
drconfigure 
drmiscsystables

if [ "$ver" != "492" ]
then 
   drloginroles
   drmaster 
fi

#if [ "$ver" != "11" ]
#then 
#   drbuildmaster 
#fi

rm -rf /tmp/$server.databases

exit 0