DOS – Compressing and uploading files to FTP

Problem – I had been asked to upload a database backup to a FTP location on the first sunday of every month for one of our partner companies. I decided to add some logic into the process to do this automatically via a scheduled task, for which the thought process and commands are listed below.

O/S: Windows Server 2000

Solution: The solution was to first backup the database via SQL CMD (Not discussed here) and then to create a batch file which zipped up the backup, connected to the FTP location and uploaded the file. This was later enhanced by adding a 3 attempt policy so that it would try again if the upload failed.

zip_backups.bat

To zip up the backup file, a program called INFO-ZIP is used which allows command line compression – http://www.info-zip.org/

To Send the Mail a program called mailsend is used – http://www.muquit.com/muquit/software/mailsend/mailsend.html

The Retry is figured out by searching the ftp output log for a specific phrase which only occurs when a successful upload happens. This is then turned into logic using standard errorlevel syntax.

REM ##############################################################################
REM ## AUTHOR: MOHSIN PATEL                                                        ##
REM ## Developed to automatically zip up backup files for transfer                 ##
REM ## Date: 21/06/2010                                                            ##
REM ## Changes made on 05/01/2011 to add retry counts and to split up            ##
REM ## each backup job                                                            ##
REM ##############################################################################

set countcar=1
echo Starting MY_DB1 %date% %time% > MY_DB1_ftpout.log
echo. >> MY_DB1_ftpout.log
echo ========================================================= >> MY_DB1_ftpout.log

set countlights=1
echo Starting MY_DB2 %date% %time% > MY_DB2_ftpout.log
echo. >> MY_DB2_ftpout.log
echo ========================================================= >> MY_DB2_ftpout.log

:ftpcar
rm MY_DB1.zip >> MY_DB1_ftpout.log
E:\Backup_transfers\INFO-ZIP\zip.exe -q -r MY_DB1 MY_DB1.bak >> MY_DB1_ftpout.log
FTP -i -s:ftpconnect_CAR.txt >> MY_DB1_ftpout.log

find "Transfer OK" MY_DB1_ftpout.log
set error=%errorlevel%

if %error%==0 goto sendsuccesscar

if %countcar%==3 goto sendfailcar
else
goto retrycountcar


:sendsuccesscar
MAILSEND -d domain.co.uk -smtp 0.0.0.0 -t you@ftp.co.uk -f me@ftp.co.uk -sub "BACKUPS COPY COMPLETE - SUCCEEDED - MY_DB1" -m MY_DB1_ftpout.log >> MY_DB1_ftpout.log
goto ftplights

:sendfailcar
MAILSEND -d domain.co.uk -smtp 0.0.0.0 -t you@ftp.co.uk -f me@ftp.co.uk -sub "CAP BACKUPS COPY COMPLETE - FAILED - MY_DB1" -m MY_DB1_ftpout.log >> MY_DB1_ftpout.log
goto ftplights

:retrycountcar
set /A countcar+=1
echo. >> MY_DB1_ftpout.log
echo ****************** Retrying attempt %countcar% ******************* >> MY_DB1_ftpout.log
echo. >> MY_DB1_ftpout.log
echo Starting: %date% %time% >> MY_DB1_ftpout.log
echo ========================================================= >> MY_DB1_ftpout.log
goto ftpcar


:ftplights
rm MY_DB2.zip >> MY_DB2_ftpout.log
E:\2ndByte_Backup_transfers\INFO-ZIP\zip.exe -q -r MY_DB2 MY_DB2.bak >> MY_DB2_ftpout.log
FTP -i -s:ftpconnect_LIGHTS.txt >> MY_DB2_ftpout.log

find "Transfer OK" MY_DB2_ftpout.log
set error=%errorlevel%

if %error%==0 goto sendsuccesslights

if %countlights%==3 goto sendfaillights
else
goto retrycountlights

:sendsuccesslights
MAILSEND -d domain.co.uk -smtp 0.0.0.0 -t you@ftp.co.uk -f me@ftp.co.uk-sub "CAP BACKUPS COPY COMPLETE - SUCCEEDED - MY_DB2" -m MY_DB2_ftpout.log >> MY_DB2_ftpout.log
goto end

:sendfaillights
MAILSEND -d domain.co.uk -smtp 0.0.0.0 -t you@ftp.co.uk -f me@ftp.co.uk -sub "CAP BACKUPS COPY COMPLETE - FAILED - MY_DB2" -m MY_DB2_ftpout.log >> MY_DB2_ftpout.log
goto end

:retrycountlights
set /A countlights+=1
echo. >> MY_DB2_ftpout.log
echo ****************** Retrying attempt %countlights% ******************* echo. >> MY_DB2_ftpout.log
echo. >> MY_DB2_ftpout.log
echo Starting: %date% %time% >> MY_DB2_ftpout.log
echo ========================================================= >> MY_DB2_ftpout.log
goto ftplights

:end

ftpconnect file

open ftp2.ftp.com
USERID
p@ssw0rd
bin
delete MY_DB1.zip
put MY_DB1.zip
disconnect
bye
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: