Windows Daily backup of local database.

Daily backup of local database.md

Create a file daily-backup.bat with contents:

@echo off
setlocal

:: set the variables

set DATABASE_SERVICE_NAME=MariaDB
set DB_USER=root
set DB_PASSWORD=gpassword
set DB_NAME=ateliers
set BACKUP_FOLDER=C:\backup
set MYSQLDUMPEXE="C:\Users\me\Desktop\mariadb-10.3.38-winx64\bin\mysqldump.exe"

:: create backup directory if not exists
if not exist %BACKUP_FOLDER% mkdir "%BACKUP_FOLDER%"

:: get the current time
set TIMESTAMP=%date:~10,4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%

:: create the backup filename
set BACKUP_FILE=%BACKUP_FOLDER%\%DB_NAME%_%TIMESTAMP%.sql

:: wait until the database service is ready, this scipt will usually run before the datbase is ready.
:check_service
echo "Checking mariadb service status..."
sc query %DATABASE_SERVICE_NAME% | find "STATE" | find /v "RUNNING"
if %errorlevel% == 0 (
  echo "Mariadb service is not running yet. Waiting 10 seconds..."
  timeout /t 10 /nobreak
  goto check_service
)

:: run mysqldump
"%MYSQLDUMPEXE%" -u %DB_USER% -p%DB_PASSWORD% %DB_NAME% > %BACKUP_FILE%

Remeber the path of the created script.

Open cmd as administrator and add a new daily scheduled task to run your batch script.
make sure to change C:\Users\me\Desktop\daily-backup.bat to the location where you saved the script previously.

schtasks /create /tn "DailyBackup" /tr "C:\Users\me\Desktop\daily-backup.bat" /sc daily /st 12:35 /RL HIGHEST /f /ru System

we set the script to run as System user to hide the cmd console window when it is running.

Note: if the scheduled time of the task was missed(machine was powered down) the task will not be executed, to fix this you have to manually(because this option does not exist for the cmd command) open task scheduler ui with Win+R and type taskschd.msc right click on our task DailyBackup and go to properties then settings and check this box Run task as soon as possible after a scheduled start is missed