Step 1: Batch Dump & Compression

I wrote a rich batch script that dumps all non-system MySQL databases into a temp folder, zips them using PowerShell’s Compress-Archive, and appends a timestamp for clarity. The script also checks if MySQL is running before proceeding and logs all activity for review. 

@echo off
setlocal enabledelayedexpansion

:: CONFIG
set MYSQL_BIN=C:\xampp\mysql\bin
set BACKUP_DIR=C:\xampp\mysql-backup
set TEMP_DIR=%TEMP%\mysql_backup_temp
set TIMESTAMP=%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%_%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%
set ZIP_PATH=%BACKUP_DIR%\mysql_backup_%TIMESTAMP%.zip
set LOGFILE=%TEMP%\mysql_backup_log.txt

:: Fix leading space in hour
if "%TIME:~0,1%"==" " set TIMESTAMP=%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%_0%TIME:~1,1%%TIME:~3,2%%TIME:~6,2%

:: Create temp and backup dirs
if not exist "%BACKUP_DIR%" mkdir "%BACKUP_DIR%"
if exist "%TEMP_DIR%" rmdir /s /q "%TEMP_DIR%"
mkdir "%TEMP_DIR%"

:: Check MySQL running
tasklist /FI "IMAGENAME eq mysqld.exe" | find /I "mysqld.exe" >nul
if errorlevel 1 (
    echo MySQL not running. Exiting.
    exit /b 1
)

:: Dump all non-system databases
for /f "skip=1 tokens=*" %%D in ('"%MYSQL_BIN%\mysql.exe" -u root --skip-password -e "SHOW DATABASES;"') do (
    if "%%D" neq "information_schema" if "%%D" neq "performance_schema" if "%%D" neq "mysql" if "%%D" neq "sys" (
        "%MYSQL_BIN%\mysqldump.exe" -u root --skip-password --databases %%D > "%TEMP_DIR%\%%D.sql"
    )
)

:: Zip the dump
powershell -Command "Compress-Archive -Path '%TEMP_DIR%\*' -DestinationPath '%ZIP_PATH%'"

Step 2: Upload with rclone

Once the SQL dump is zipped, I use rclone to upload the archive to Google Drive. The destination folder is named Important Backup → SQL DATA. This step ensures a daily off-site copy of the database exists outside my local machine. 

:: Upload ZIP to Google Drive using rclone
rclone copy "%ZIP_PATH%" gdrive:"Important Backup/SQL DATA"

Step 3: Email Notifications

To make the backup system aware of its own success or failure, I added email alerts using PowerShell. Two scripts handle the logic: one for successful runs and one for failures. They send notifications via Gmail’s SMTP server using app passwords and TLS encryption. 

# email_success.ps1 (simplified)
$msg = New-Object Net.Mail.MailMessage
$msg.From = "your@email.com"
$msg.To.Add("your+alerts@email.com")
$msg.Subject = "✅ MySQL Backup Completed"
$msg.Body = "The scheduled backup completed successfully at $timestamp."

$smtp = New-Object Net.Mail.SmtpClient("smtp.gmail.com", 587)
$smtp.EnableSsl = $true
$smtp.Credentials = New-Object System.Net.NetworkCredential("user", "secure-app-password")
$smtp.Send($msg)

# email_fail.ps1 (simplified)
$msg = New-Object Net.Mail.MailMessage
$msg.From = "your@email.com"
$msg.To.Add("your+alerts@email.com")
$msg.Subject = "❌ MySQL Backup FAILED"
$msg.Body = "The backup failed at $timestamp. Log file attached if available."

if (Test-Path $logPath) {
    $attachment = New-Object Net.Mail.Attachment($logPath)
    $msg.Attachments.Add($attachment)
}

$smtp = New-Object Net.Mail.SmtpClient("smtp.gmail.com", 587)
$smtp.EnableSsl = $true
$smtp.Credentials = New-Object System.Net.NetworkCredential("user", "secure-app-password")
$smtp.Send($msg)

Error Handling & Cleanup

Once the backup and upload steps complete, the script checks %ERRORLEVEL% to determine whether the process was successful. Based on this result, it triggers either the success or failure email script with the latest log file attached. Regardless of outcome, all temporary files and directories are removed to keep the system tidy and avoid residual clutter. 

:: Trigger appropriate email notification
IF %ERRORLEVEL% EQU 0 (
    powershell -ExecutionPolicy Bypass -File "%EMAILSCRIPT_SUCCESS%" -LogFile "%LOGFILE%"
) ELSE (
    powershell -ExecutionPolicy Bypass -File "%EMAILSCRIPT_FAIL%" -LogFile "%LOGFILE%"
)

:: Clean up temporary data
rmdir /s /q "%TEMP_DIR%"
del "%ZIP_PATH%"

Scheduled Automation

The entire backup system is triggered automatically each night at 11:59 PM using Windows Task Scheduler. The job is configured with the “Run task as soon as possible after a scheduled start is missed” option enabled — ensuring no day is skipped, even if the machine was off at the scheduled time.

Conclusion

What began as a reaction to unexpected data loss has evolved into a complete, hands-free solution for daily SQL backups. Built entirely with native Windows tools, PowerShell, and rclone, it delivers reliability without third-party overhead. The result is peace of mind — knowing my databases are safe, versioned, and offsite every single day.

Citations

Portions of the backup system — including batch scripting, PowerShell email automation, and error handling logic — were developed with the assistance of AI.

OpenAI. (2025). ChatGPT (April 2025 version) [Large language model]. https://chat.openai.com

Author’s Note

I had zero prior experience in scripting. I simply wanted to protect my data, and this system is the result of clear goals, AI support, and iterative learning.

Thanks for reading.
Aryan Sharma
dev-aryan@aboutsharma.com, aryan@aboutsharma.com