Schedule daily task to upload database backup to SFTP Server on Windows

sachin

Step 1: Install Winscp

Step 2: Download 7-zip console version

Step 3: Open Notepad (or any text/code editor) and save it as mysqlbackup.bat and add below code and edit variables as per your system

:: turn on if you are debugging
:: @echo off

:: If the time is less than two digits insert a zero so there is no space to break the filename

:: If you have any regional date/time issues call this include: getdate.cmd  credit: Simon Sheppard for this cmd - untested
:: call getdate.cmd Fri 04/08/2022 (as April 8, 2022)

echo date %DATE%
echo time %TIME%

set year=%DATE:~-4%
set day=%DATE:~-7,2%
set mnt=%DATE:~-10,2%
:: set hr=%TIME:~0,2%
:: set min=%TIME:~3,2%

IF %day% LSS 10 SET day=0%day:~1,1%
IF %mnt% LSS 10 SET mnt=0%mnt:~1,1%
:: IF %hr% LSS 10 SET hr=0%hr:~1,1%
:: IF %min% LSS 10 SET min=0%min:~1,1%

set backuptime=%year%-%mnt%-%day%
echo %backuptime%

:: SETTINGS AND PATHS 
:: Note: Do not put spaces before the equal signs or variables will fail

:: Remote SFTP Connection 
:: https://riptutorial.com/batch-file/example/32077/escape-using-caret---
:: https://adamtheautomator.com/winscp-command-line/#WinSCPexe_Uploading_Files_without_a_Site


set sftpPath=sftp://root:securepassword@192.168.2.22/

:: Remote SFTP Upload Path
set uploadPath=/mnt/ALL_BACKUP/SERVER/

:: Name of the database user with rights to all tables
set dbuser=root

:: Password for the database user
set dbpass=DBpassw0rd

:: Error log path - Important in debugging your issues
set errorLogPath="C:\Users\Administrator\Desktop\mysql_backup\dumperrors.txt"

:: MySQL EXE Path
set mysqldumpexe="C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump.exe"

:: Local Backup Path
set backupfldr=C:\Users\Administrator\Desktop\mysql_backup\backup\

:: Path to zip executable
set zipper="C:\Users\Administrator\Desktop\mysql_backup\zip\7za.exe"

:: Path to winSCP executable
set winscpexe="C:\Program Files (x86)\WinSCP\WinSCP.exe"

:: Number of days to retain .zip backup files 
set retaindays=2

:: DONE WITH SETTINGS

%mysqldumpexe% -u%dbuser% -p%dbpass% database > "%backupfldr%db_bkp_file__%backuptime%.sql"

:: .zip option clean but not as compressed
::::echo "Zipping current backup file"
%zipper% a -tzip "%backupfldr%db_bkp_file__%backuptime%.zip" "%backupfldr%db_bkp_file__%backuptime%.sql"

::::echo "Deleting all the files ending in .sql only"
del "%backupfldr%db_bkp_file__%backuptime%.sql"

::::echo "Deleting zip files older than %retaindays% days now"
forfiles -p %backupfldr% -s -m *.* -d -%retaindays% -c "cmd /c del @path"

%winscpexe% /command "option batch continue" "open %sftpPath%" "cd %uploadPath%" "mkdir ALL_BACKUP_%backuptime%" "option batch abort" "exit"

::::echo "Uploading to SFTP server"
%winscpexe% /command "option batch abort" "open %sftpPath%" "put %backupfldr%db_bkp_file__%backuptime%.zip %uploadPath%ALL_BACKUP_%backuptime%/" "exit"

echo "done"

:: return to the main script dir on end
:: GO FORTH AND BACKUP EVERYTHING!
:: uncomment below line to test and want to exit script manually
:: pause
popd

Step 5: Search for "Task Scheduler" in windows search

1. Click on Task Scheduler Library
2. Click "New Folder" to create Folder and name it as mysqlbackup (Name can be of your choice)
3. Click "Create Task" and fill mandatory 3 tabs (General, Triggers, Action).

Here's the link to download already prepared files.

Auto-MySQL-Backup-Windows.zip