We’ve a legacy application for our Bahrain operations and after a disastrous hardware failure we wanted to find a cost effective method to bring the database dumps to Kuwait for regular health checks.
Options in front of us were pretty limited. The business in Bahrain only had a basic internet connection(dial-up DSL) without static IP address, hence we were forced to workout something that is dependable. Finally we decided to FTP the dump file in compressed form after the dump export every Friday once after realizing that the total size of the compressed dump file was less than 100MB in size!
Here is the script file We’ve compiled after referring a number of blogs/tech forum visits. Please note, the methods we implement here uses passwords seen in clear text. Hence make sure that you take maximum attention while implementing our solution in your environments.
::http://blogs.msdn.com/b/oldnewthing/archive/2012/08/01/10334557.aspx @echo off SETLOCAL ::Get the latest dump file name, generated using exp command for /f "tokens=*" %%a in ('dir *.dmp /o:-d /b') do set NEWEST=%%a&& goto :next :next REM echo The most recently created file is %NEWEST% ::http://stackoverflow.com/questions/15567809/batch-extract-path-and-filename-from-a-variable FOR %%i IN ("%NEWEST%") DO ( REM ECHO filedrive=%%~di REM ECHO filepath=%%~pi SET ZIPNAME=%%~ni REM ECHO fileextension=%%~xi ) SET ZIPNAME=%ZIPNAME%.zip ::Creating ZIP file using Java Runtime ::Dump files could be huge and take hours to transfer in full size. So we will use Java RunTime Executable to create a zip file which ::Will be smaller in size ::Make sure you have JRE installed, and the installation path is set in the environment variables, if not call JRE using the full path ::eg: C:\java\jre echo Creating Zip file for transfer, filename ""%ZIPNAME%"" ::We will create the zip file without meta-info, hence the M switch is used ::jar -cfM %ZIPNAME% %NEWEST% ::Another option is to go with 7Zip, 3rd party utility that could create zip/7z files ::7z a %ZIPNAME% %NEWEST% ::Or using Windows built-in Zip utility zip %ZIPNAME% %NEWEST% ::You can use IP address or fully qualified domain names for the FTP server ::If the FQDN is not registered, you can add an entry with your hosts file ::FTP accepts parameter files, ftp -s:parameter file, so we will create the parameter during the initial running ::Please note, the parameter file created is permanent & clear text format, hence make sure that adequate security measures are practiced ::to avoid getting sensitive details exposed. echo open ftpservername(FQDN/IP)>ftp.txt echo username>>ftp.txt echo password>>ftp.txt echo binary>>ftp.txt echo put %ZIPNAME%>>ftp.txt echo disconnect>>ftp.txt echo quit>>ftp.txt REM echo put filename2.dat >>ftp.txt REM echo put filename3.dat >>ftp.txt REM echo put filename4.dat >>ftp.txt ::We referred the following thread for making the batch file ::Google search keywords: pass parameter to ftp script ::http://stackoverflow.com/questions/5170627/is-it-possible-to-pass-a-variable-into-a-windows-ftp-script-file ftp -s:ftp.txt ::Delete the zip file after transfer comples del %ZIPNAME% ::Optionally you can use "exit" the command windows-ftp-script-file ::Exit
and usually the ftp.txt should look like below
open ftp.myserver.com username password binary put exp_10032017.zip disconnect quit