Batch file doesn't write the result in the desired directory?

Question

I have the following batch-file on windows-10 which is very close to working with only one small problem.

The .sql result file is written outside of the desired folder. The batch file properly creates the folder and the file, but fails to change into the desired directory when it creates the .sql result file.

For example, it creates D:\Backups\MySQL\MyDatabase__04-27-2018-14-22, and writes the file MyDatabase__04-27-2018-14-22.sql in the MySQL folder, rather than inside the folder with the exact same name.

I've tried variations of changing the directory with no success.

set dt=%date:~-10,2%-%date:~-7,2%-%date:~-4,4%-%time:~0,2%-%time:~3,2%
set dt=%dt: =0%

if exist "D:\Backups\" (mkdir D:\Backups\MySQL\MyDatabase__%dt%
    CD ..
    CD ..
    CD /D D:\Backups\MySQL\MyDatabase__%dt%
    START /WAIT C:\xampp\mysql\bin\mysqldump.exe --user=root --password=  --host=localhost --port=3306 --result-file="D:\Backups\MySQL\MyDatabase_%dt%.sql" --default-character-set=utf8 --single-transaction=TRUE --databases "MyDatabase"
) else if exist "H:\Backups\" (mkdir H:\Backups\MySQL\MyDatabase__%dt%
    CD ..
    CD ..
    H:
    CD H:\Backups\MySQL\MyDatabase__%dt%
    START /WAIT C:\xampp\mysql\bin\mysqldump.exe --user=root --password=  --host=localhost --port=3306 --result-file="H:\Backups\MySQL\MyDatabase_%dt%.sql" --default-character-set=utf8 --single-transaction=TRUE --databases "MyDatabase"
) else if exist "G:\Backups\" (mkdir G:\Backups\MySQL\MyDatabase__%dt%
    CD ..
    CD ..
    G:
    CD G:\Backups\MySQL\MyDatabase__%dt%
    START /WAIT C:\xampp\mysql\bin\mysqldump.exe --user=root --password=  --host=localhost --port=3306 --result-file="G:\Backups\MySQL\MyDatabase_%dt%.sql" --default-character-set=utf8 --single-transaction=TRUE --databases "MyDatabase"
) else mkdir C:\Users\Scotty\Desktop\Backups\MySQL\MyDatabase__%dt%
CD ..
CD ..
C:
CD C:\Users\Scotty\Desktop\Backups\MySQL\MyDatabase__%dt%
START /WAIT C:\xampp\mysql\bin\mysqldump.exe --user=root --password=  --host=localhost --port=3306 --result-file="C:\Backups\MySQL\MyDatabase_%dt%.sql" --default-character-set=utf8 --single-transaction=TRUE --databases "MyDatabase"
CD..
CD..
CD C:\Users\Scotty\Desktop\BackupBatchFiles\

The actual result is that the mysqldump result file is placed outside the directory created with the same name. I am expecting the .sql result file to be written INSIDE the directory that the batch file created with the same name.

Answer 1

Well, all lines with mysqldump.exe specify parameter --result-file with

Backups\MySQL\MyDatabase_%dt%.sql

instead of

Backups\MySQL\MyDatabase_%dt%\MyDatabase_%dt%.sql

I suggest following for the batch file which avoids repetitive code:

@echo off
setlocal EnableExtensions DisableDelayedExpansion
set "DateTime=%date:~-10,2%-%date:~-7,2%-%date:~-4,4%-%time:~0,2%-%time:~3,2%"
set "DateTime=%DateTime: =0%"
set "DoLocalBackup="

for %%I in (D H G) do if exist "%%I:\Backups\" set "BackupFolder=%%I:\Backups\MySQL\MyDatabase__%DateTime%" & goto MakeBackup

echo ERROR: Failed to find network backup drive.

:LocalBackup
set "BackupFolder=%UserProfile%\Desktop\Backups\MySQL\MyDatabase__%DateTime%"
set "DoLocalBackup=1"

:MakeBackup
mkdir "%BackupFolder%" 2>nul
if exist "%BackupFolder%\" (
    C:\xampp\mysql\bin\mysqldump.exe --user=root --password=  --host=localhost --port=3306 --result-file="%BackupFolder%\MyDatabase_%DateTime%.sql" --default-character-set=utf8 --single-transaction=TRUE --databases "MyDatabase"
) else (
    echo ERROR: Failed to create backup folder: "%BackupFolder%"
)
if not defined DoLocalBackup goto LocalBackup

endlocal
CD /D "%UserProfile%\Desktop\BackupBatchFiles\"

This batch file creates a backup in first found backup folder on a network drive and additionally one more backup in a subdirectory of current user's desktop directory.

Another solution is even easier because it just chooses from one of four possible backup locations as the batch file code in question.

@echo off
setlocal EnableExtensions DisableDelayedExpansion
set "DateTime=%date:~-10,2%-%date:~-7,2%-%date:~-4,4%-%time:~0,2%-%time:~3,2%"
set "DateTime=%DateTime: =0%"

for %%I in (D: H: G: "%UserProfile%\Desktop") do if exist "%%~I\Backups\" set "BackupFolder=%%~I\Backups\MySQL\MyDatabase__%DateTime%" & goto MakeBackup

echo ERROR: Failed to find network backup drive or local backup folder.
goto EndBackup

:MakeBackup
mkdir "%BackupFolder%" 2>nul
if exist "%BackupFolder%\" (
    C:\xampp\mysql\bin\mysqldump.exe --user=root --password=  --host=localhost --port=3306 --result-file="%BackupFolder%\MyDatabase_%DateTime%.sql" --default-character-set=utf8 --single-transaction=TRUE --databases "MyDatabase"
) else (
    echo ERROR: Failed to create backup folder: "%BackupFolder%"
)

:EndBackup
endlocal
CD /D "%UserProfile%\Desktop\BackupBatchFiles\"

Here is also a third solution which is similar to second solution, but continues the FOR loop if creation of the backup folder failed on a specified possible backup storage location.

@echo off
setlocal EnableExtensions DisableDelayedExpansion
set "DateTime=%date:~-10,2%-%date:~-7,2%-%date:~-4,4%-%time:~0,2%-%time:~3,2%"
set "DateTime=%DateTime: =0%"
set "BackupFolder=Backups\MySQL\MyDatabase__%DateTime%"

for %%I in (D: H: G: "%UserProfile%\Desktop") do if exist "%%~I\Backups\" (
    mkdir "%%~I\%BackupFolder%" 2>nul
    if exist "%%~I\%BackupFolder%\" (
        C:\xampp\mysql\bin\mysqldump.exe --user=root --password=  --host=localhost --port=3306 --result-file="%%~I\%BackupFolder%\MyDatabase_%DateTime%.sql" --default-character-set=utf8 --single-transaction=TRUE --databases "MyDatabase"
        goto EndBackup
    ) else echo ERROR: Failed to create backup folder: "%%~I\%BackupFolder%"
)
echo ERROR: Failed to find any specified backup folder or to create the
echo        current date backup folder in one of the backup folders.

:EndBackup
endlocal
CD /D "%UserProfile%\Desktop\BackupBatchFiles\"

Further I would suggest to change the third line to:

set "DateTime=%date:~-4,4%-%date:~-7,2%-%date:~-10,2%-%time:~0,2%-%time:~3,2%"

This results in international date format yyyy-MM-dd which is better than dd-MM-yyyy because of directories/files sorted alphabetically are at the same time sorted chronological using international date format.

Note: The date/time format used for date/time strings of referenced environment variables DATE and TIME as used in third line depends on which country/region is configured for the account which is used running this batch file. See for example Why does %date% produce a different result in batch file executed as scheduled task? for a region independent solution to get current date/time in a specified format.

For understanding the used commands and how they work, open a command prompt window, execute there the following commands, and read entirely all help pages displayed for each command very carefully.

• cd /?
• echo /?
• endlocal /?
• for /?
• goto /?
• if /?
• mkdir /? or md /?
• set /?
• setlocal /?