Database updates

I needed a method to update a database application across a WAN, and found that I could do this with my Linux firewalls, and Windows batch scripting. The topology is this: Win2k Svr -> Linux FW -> Internet <- Linux FW <- WinNT Svr Win2k Svr batch file:

@ECHO OFF
SET _LOGFILE=mydb.log
Net Stop "SQL Anywhere - mydb" >> "%_LOGFILE%"
ATTRIB -R "C:\Program Files\mydb Software\mydb\mydb.db"
ATTRIB -R "C:\Program Files\mydb Software\mydb\mydb.log"
NET USE Z: \\win2ksvr\home\backup\backup\mydb admpass /USER:domain\admin
FOR /F "tokens=2,3,4 delims=/ " %%i in ('DATE /T') do set d=%%k%%i%%j
FOR /F "tokens=1,2,3 delims=:. " %%i in ('TIME /T') do set t=%%i%%j%%k
XCOPY "C:\Program Files\mydb Software\mydb\mydb.log" Z:
CALL :REPORT %ERRORLEVEL% "C:\Program Files\mydb Software\mydb\mydb.log"
XCOPY "C:\Program Files\mydb Software\mydb\mydb.db" Z:
CALL :REPORT %ERRORLEVEL% "C:\Program Files\mydb Software\mydb\mydb.db"
NET USE Z: /DELETE
NET START "SQL Anywhere - mydb" >> "%_LOGFILE%"
GOTO :EOF

:REPORT
IF %1==0 SET STATUS=OK
IF NOT %1==0 SET STATUS=Failed
ECHO %2 %STATUS% on %d% at %t% >> "%filename%"
SET STATUS=
GOTO :EOF

:EOF
END

Linux FW Script:

#!/bin/sh
# Source function library.
. /etc/init.d/functions

# Get config.
. /etc/sysconfig/network

# Check that networking is up.
if [ ${NETWORKING} = "no" ]
then
echo "Networking is not setup on this computer."
exit 0
fi


# Check that DSL is up.
host="ameritech.net"
ping -c1 $host 2>&1 1>/dev/null

if [ $? -gt 0 ]; then
echo "Can't ping ameritech.net. We must be down,\nor we are not resolving properly.\nPlease check it out" mail -s "We appear to be down." hostmaster@domain.org
exit 0
fi


## Check that Domain is up.
if ! wget -T 30 -O /dev/null -q http://www.domain.org/index.html; then
# Domain is down
echo "Domain is down, the first page of the \nwebsite is not available" mail -s "Domain is down." hostmaster@domain.org
exit 0
fi

## Zip up the db
cd /home/backup/rambackup/Mydb
if [ -e mydb.db -o -e mydb.log ]; then
zip -9 -m mydb.zip mydb.db mydb.log
cp mydb.zip /mnt/domain1
else
echo "There was no mydb.db or mydb.log file on /home/backup/rambackup/Mydb to zip up. Check and make sure they exist." mail -s "Mydb zipping error." hostmaster@domain.org
exit 0
fi

sftp -C -b /etc/cron.daily/mydb.batch -oUser=smbuser -oIdentityFile=/home/samba/.ssh/id_dsa domain 2> /tmp/mydb >> /tmp/mydb
mail -s "Mydb transfer results." hostmaster@domain.org < /tmp/mydb rm -f /tmp/mydb exit 0 

mydb.batch

lcd /home/backup/rambackup/mydb
cd /mydb
put mydb.zip
exit

WinNT Script

@ECHO OFF
CD "c:\MyDB Update Scripts\"
SET _LOGFILE=update.log

SETLOCAL 
SET OK=N 
FOR /F "Tokens=*" %%a in ('NET STOP "SQL Anywhere - MyDB"^|FIND /I "stopped successfully"') 
DO ( 
 SET OK=Y 
) 
If "%OK%" EQU "N" ( 
 @ECHO The MyDB server did not stop successfully. >> "%_LOGFILE%" 
 ) ELSE ( 
 @ECHO The MyDB server stopped successfully. >> "%_LOGFILE%" 
) 
ENDLOCAL


ATTRIB -R "c:\Program Files\MyDB Software\MyDB Fundraising\MyDB.db"
ATTRIB -R "c:\Program Files\MyDB Software\MyDB Fundraising\MyDB.log"
NET USE P: \\10.0.0.3\MyDB
FOR /F "tokens=2,3,4 delims=/ " %%i IN ('DATE /T') DO SET d=%%k%%i%%j
FOR /F "tokens=1,2,3 delims=:. " %%i IN ('TIME /T') DO SET t=%%i%%j%%k
XCOPY P:\MyDB.log "c:\Program Files\MyDB Software\MyDB Fundraising\"
CALL :REPORT %ERRORLEVEL% P:\MyDB.log
XCOPY P:\MyDB.db "c:\Program Files\MyDB Software\MyDB Fundraising\"
CALL :REPORT %ERRORLEVEL% P:\MyDB.db
NET USE P: /DELETE
NET START "SQL Anywhere - MyDB" >> "%_LOGFILE%"
GOTO :EOF

:REPORT
IF %1==0 SET STATUS=OK
IF NOT %1==0 SET STATUS=Failed
ECHO %2 %STATUS% on %d% at %t% >> "%_LOGFILE%"
SET STATUS=
GOTO :EOF