When automating database backups on Windows using batch scripts, one common requirement is to include the current date in the filename. This helps with organization and makes it easier to identify when each backup was created. The solution involves Windows command prompt (CMD) syntax for date formatting.

Windows batch files use the %DATE% environment variable, but its format varies by system locale. Here's how to get a consistent format:

for /f "tokens=2-4 delims=/ " %%a in ('date /t') do (
    set currentdate=%%c-%%a-%%b
)

Here's a full example that creates a MySQL dump with the current date in YYYY-MM-DD format:

@echo off
setlocal

:: Get current date in YYYY-MM-DD format
for /f "tokens=2-4 delims=/ " %%a in ('date /t') do (
    set year=%%c
    set month=%%a
    set day=%%b
)
if %month% LSS 10 set month=0%month%
if %day% LSS 10 set day=0%day%
set filedate=%year%-%month%-%day%

:: MySQL dump command
"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe" -u root -pYourPassword --databases your_database > "C:\backups\db_backup_%filedate%.sql"

:: Optional: Compress the backup
"C:\Program Files\7-Zip\7z.exe" a "C:\backups\db_backup_%filedate%.7z" "C:\backups\db_backup_%filedate%.sql"

:: Clean up SQL file if compression succeeded
if exist "C:\backups\db_backup_%filedate%.7z" del "C:\backups\db_backup_%filedate%.sql"

endlocal

For more precise timestamps that include time (24-hour format):

@echo off
setlocal

:: Get datetime in YYYY-MM-DD_HH-MM-SS format
for /f "tokens=1-6 delims=/:. " %%a in ('echo %date% %time%') do (
    set datetime=%%c-%%a-%%b_%%d-%%e-%%f
)

:: Remove leading space if hour is single digit
set datetime=%datetime: =0%

:: Use in filename
echo Backup filename will be: backup_%datetime%.sql

endlocal

Locale-specific date formats: The above examples work with US locale settings. For other regions, adjust the token positions in the FOR command.

Time with leading spaces: When hours are single-digit (1-9), there's a leading space. The script handles this with set datetime=%datetime: =0% to replace spaces with zeros.

Scheduled tasks: When running as a scheduled task, test the script manually first to ensure the date format works correctly in that context.

When automating database backups via Windows CMD, a common requirement is to include timestamps in filenames to create unique, time-identified backups. Here's how to implement this efficiently in batch scripting.

The fundamental approach uses %date% environment variable with string manipulation:

@echo off
setlocal enabledelayedexpansion

:: Get current date in YYYY-MM-DD format
for /f "tokens=2-4 delims=/ " %%a in ('date /t') do (
    set currentdate=%%c-%%a-%%b
)

:: Example with MySQL dump
mysqldump -u root -pPassword123 mydatabase > backup_!currentdate!.sql

For more precise timestamps including time:

@echo off
setlocal enabledelayedexpansion

:: Create timestamp in YYYYMMDD_HHMMSS format
for /f "tokens=1-6 delims=/: " %%a in ('echo %date% %time%') do (
    set datetime=%%c%%a%%b_%%d%%e%%f
)

:: PostgreSQL backup example
pg_dump -U postgres mydb > dbbackup_!datetime!.dump

Different system locales may require alternative parsing:

:: Universal approach for different date formats
for /f "skip=1" %%d in ('wmic os get localdatetime') do if not defined datetime set datetime=%%d
set filedate=%datetime:~0,4%-%datetime:~4,2%-%datetime:~6,2%

:: SQL Server example
sqlcmd -S localhost -U sa -Q "BACKUP DATABASE AdventureWorks TO DISK='C:\backups\aw_%filedate%.bak'"

The same technique works for log files:

:: Redirect both output and errors to dated log file
somecommand.exe > backup_log_%date:/=%.txt 2>&1

Combine with Windows Task Scheduler for automated daily backups:

:: Example task scheduler command
schtasks /create /tn "NightlyDBBackup" /tr "C:\scripts\daily_backup.bat" /sc daily /st 23:00