MSDOS Bat/Exe SQL Dump scheduler

Posted on 2003-11-06
Last Modified: 2012-08-13
Hi - I am looking to develop a small batch file that is scheduled (cron) to create a SQL dump of MYSQL databases at regular intervals e.g. every hour. These files are part pf the daily backup. Furthermore, a developer will then copy the SQL files via FTP at certain periods during the week for contingency. The scheduler will delete the contents of the folder weekly. What I am looking for is an application to perform this task. It can be an exe or bat file. The application must be able to run under the system account. What I was thinking was a little bat that got the latest time as a variable and then used this variable with the dump command? Any suggestions and help appreciated.
Question by:txtphile
LVL 17

Expert Comment

ID: 9693947
Too bad this had to be under Windows, under Linux I have a script that does a regular mysqldump, compresses it, and then FTPs it to the server of your choice.

That being said, if the files are only being pulled off the server intermittently, you are better off turning on the binary log. The binary log will store every query that affects data, and you can then just FTP away the latest log files and the occasional dump o the data (flushing the logs when yuo do). This will be much more space efficient and result in faster transfers over FTP, otherwise you copy the full table every time, which can be redundant if only two rows have changed.

Assisted Solution

heskyttberg earned 200 total points
ID: 9696081

First of all deleteing files older than x hours is a pain in windows, there just arent and cmd line tools for this, so I

recommend the following:
Download this:

Unzip, make sure the files are in the path of the system.

Then there is an excellent tool in there called find, I usually keep i in c:\gnu, so I do like this:
c:\gnu\usr\local\wbin\find c:\oracle\database\archive -ctime +10 -exec rm -f {}

For rm command to work the c:\gnu\usr\local\wbin, needs to be in your path.
The above command will result in all files older than 10 days will be deleted from the c:\oracle\database\archive dir.

This is so useful whne it comes to transaction logs, at least in Oracle.
We use hot backups each night, and in this way I can keep as many days of archive logs as I want, currently we keep 20 days

online on disk.

As for ftp batching please consider this:

Dumping MySQL, consult the MySQL documentation:

With theese tools you shouldn't have any trouble at all creating a batch file which you could schedule with Task Manager to

run as often as you like.


Expert Comment

ID: 9725252
to make things easier for you, you might want to install cygwin in your computer and make use of cron ! ... then if Squeebee is kind enough he could send you his script :)
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

LVL 17

Expert Comment

ID: 9726487
If he gets cron and cygwin online I would be happy to post it.

Accepted Solution

nevahj earned 300 total points
ID: 9728562
You can use VBS to accomplish this. The following is a single VBS file that I named "mysql_dump.vbs". First, it looks for files older than 2 days and deletes them. Then it uses "mysqldump" to dump a specific table to a specified location. Finally, it uses an FTP script to FTP the file to another server.

You can separate this script into three different files to do just one thing -- like dump databases. On windows you can use the Task Scheduler to run this or on NT machines use the DOS AT command. Personally, I use "AutoTask" -- it can run things based on conditions (file exists).

Here is my VBS script file:
strPath = "C:\temp\mysql\"      'path to location of files to delete
intDays = 2                  'number of days to keep

Function FileCreateDate(filename)
   Dim fname
   Set fname = objFSO.GetFile(filespec)
   ShowFileInfo = fname.DateCreated
End Function

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)
For Each objItem In objFolder.Files
   if objItem.DateLastModified < date() - intDays then
      objFSO.DeleteFile(strPath & objitem.Name)
   END if
Next 'objItem

Set objItem = Nothing
Set objFolder = Nothing
Set objFSO = Nothing

strDBPath = "C:\MySQL\"            'Location of MySQL
strDumpPath = "C:\Temp\MySQL\"      'Location to dump .sql file
strUser = "root"            'User for MySQL
strPass = "friday13"            'Password for MySQL
strDB = "dbMain"            'Database to dump

'NOTE: Win9x uses "command" -- NT uses "cmd"
'If running on Windows 9x ADD apostrophe to beginning of next line
strCMD = "cmd /c " & strDBPath & "bin\mysqldump -u" & strUser & " -p" & strPass & " --opt " & strDB & " > " & strDumpPath & strDB & ".sql"

'If running on Windows 9x REMOVE apostrophe from beginning of next line
'strCMD = "command /c " & strDBPath & "bin\mysqldump -u" & strUser & " -p" & strPass & " --opt " & strDB & " > " & strDumpPath & strDB & ".sql"

'Run OS command
set wshShell = createobject("")
rc = (strCMD,0,true)

'strPath = "c:\temp\"                        'location to create script file (I am using strPath from above)
strServer = ""                  'FTP server ip
strFTPUser = "iamadmin"                        'FTP user name
strFTPPass = "letmein"                        'FTP password
strPutLoc = "mysql"                        'location on ftp host to store file
strPutFile = strDumpPath & strDB & ".sql"      'file to upload

'Create FTP script
Set objFile1=Server.CreateObject("Scripting.FileSystemObject")
Set scrfil=objFile1.OpenTextFile(strPath & strPutFile & ".scr"), 8, TRUE)
scrfil.Write("open " & strServer & vbcrlf)
scrfil.Write(strFTPUser & vbcrlf)
scrfil.Write(strFTPPass & vbcrlf)
scrfil.Write("hash" & vbcrlf)
scrfil.Write("lcd " & strPutLoc & vbcrlf)
scrfil.Write("put " & strPutFile & vbcrlf)
scrfil.Write("bye" & vbcrlf)
'Run OS command FTP using the FTP script
'NOTE: Win9x uses "command" -- NT uses "cmd"
set wshShell = createobject("")
'rc = ("command /c ftp -s:" & strPath & strPutFile & ".scr",0,true)
rc = ("cmd /c ftp -s:" & strPath & strPutFile & ".scr",0,true)

Set f2 = objFile1.GetFile(strPath & strPutFile & ".scr")

Hope this helps,

Author Comment

ID: 9730082
All interesting comments ... Charles - nice suggestion.

I ended up using this - to schedule as a service - very nice little app.


This to create - please note the command line zip command ... sweet.


@ echo off
for /F "tokens=1-4 delims=:., " %%a in ('time/T') do set TIME=%%a%%b%%c

for /F "tokens=1-4 delims=/- " %%A in ('date/T') do set DATE=%%B%%C%%D


cd mysql\bin

md d:\SQLDB\%DATE%\

mysqldump --user dbsys --password=psswrd dbname > d:\SQLDB\%DATE%\dbname-%DATE%-%TIME%.sql



this to clean up weekly

@ echo off

xcopy D:\SQLDB\%DATE%\*.* D:\Scratch\%DATE%\ /a /e /k /y



xcopy D:\Scratch\*.* D:\SQLDB /a /e /k /y

RD D:\scratch\ /S /Q

md d:\SQLDB\; d:\SQLZIP\; d:\scratch\


And then with cron on a remote machine I have nightly backup off site using

wget - that downloads the zip files via http (I prefer this to ftp …) - one line of code. Bing Bang bosh. Hourly zipped SQl dumps to your desktop.

If anybody and improve the method I have used please improve. Everybody gave suggestions and although I will not be using them I think Charles solution is the most comprehensive and an interesting alternative to mine - points to brain on brass.

Thanks all!!

Author Comment

ID: 9730152
hhhhhhmmmm actually heskyttberg offered nice little alternative doing it another way but just as valid ... split points!! Squeebee ... what is your script ... interested to see all possibilities.

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need Wordpress help to link from outside to image item number. 6 50
paypal ipn to mysql 3 80
when to use sequences in mysql 4 39
SubQuery link 4 45
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question