Windows Batch Script to only keep the N latest FOLDERS in a directory

Question

I have a directory into which I backup MySQL dbs in a folder with the current date.

Now after 30 or 60 days I like to only keep the n latest folders in that backup directory and delete the rest of the folders, each having gzipped dbs in them.

For backing up the MySQL dbs I use adityasatrio's batch script and creating the folder with the current date is also sorted thanks to this answer.

However only keeping the, for sake of this example, latest 3 folders and deleting all other folders in the backup directory seems a struggle.

This is what I have tried.

 :: deletes all files and folders in the backupDir
 :: for /f "skip=3 delims=" %%a in ('dir "%backupDir%\" /b /o-d') do echo del "%backupDir%\%%a"


 :: deletes only files in backupDir
 :: for /f "skip=3 delims=" %%a in ('dir "%backupDir%\" /b /a-d') do echo del "%backupDir%\%%a"


 :: deletes all files and folders BUT keeps 3 latest FOLDERS in backupDir
 :: for /f "skip=3 delims=" %%a in ('dir "%backupDir%\" /b') do echo del "%backupDir%\%%a"


 :: deletes all files and folders BUT keeps 3 latest FOLDERS in backupDir
 :: for /f "skip=3 delims=" %%a in (' dir "%backupDir%\" /o-d /b') do echo rd /S /Q "%backupDir%\%%a"

Please note I am not looking to delete any files that might be in that backupDir. I am after deleting all but the latest 3 folders in the backupDir, so basically remove old folders with gzipped .sql files in them.

These questions helped but still I am not doing it right somehow.

delete all but X most recent folders

How do I delete old files from a directory while keeping the most recent ones on Windows (files not folder)

Batch file to delete files older than N days (again, files not folder)

https://serverfault.com/questions/49614/delete-files-older-than-x-days (for good measure, again files not folders..)

This comment delete all but X most recent folders comes closest to what I am trying to do, but that also deletes any files that might be in the backupDir.

**edit1:**Just found Delete all folders except 2 lastest folders in Windows and will see if that helps in any ways.

The complete script with my humble efforts below popd is here. I hope to get this sorted with your help or input for further reading. Note I am totally new to Batch Scripting. Any help towards this is really much appreciated. Help me get all but those N latest folders deleted please. :)

     @echo off

 set dbUser=root
 set dbPassword=root
 set "backupDir=D:\MySQLDumps"
 set "mysqldump=C:\wamp\bin\mysql\mysql5.6.17\bin\mysqldump.exe"
 set "mysqlDataDir=C:\wamp\bin\mysql\mysql5.6.17\data"
 set "zip=C:\Program Files\7-Zip\7z.exe"

rem The four lines below will give you reliable YY DD MM YYYY HH Min Sec MS variables in XP Pro and higher.

for /f "tokens=2 delims==" %%a in ('wmic OS Get localdatetime /value') do set "dt=%%a"
set "YY=%dt:~2,2%" & set "YYYY=%dt:~0,4%" & set "MM=%dt:~4,2%" & set "DD=%dt:~6,2%"
set "HH=%dt:~8,2%" & set "Min=%dt:~10,2%" & set "Sec=%dt:~12,2%" & set "MS=%dt:~15,3%"

 set "dirname=%YYYY%-%MM%-%DD% %HH%-%Min%-%Sec%"

 echo "dirName"="%dirName%"
 :: pause

 :: switch to the "data" folder
 pushd "%mysqlDataDir%"

 :: create backup folder if it doesn't exist
 if not exist "%backupDir%\%dirName%\" mkdir "%backupDir%\%dirName%"

 :: iterate over the folder structure in the "data" folder to get the databases

 for /d %%f in (*) do (
 echo processing folder "%%f"

 "%mysqldump%" --host="localhost" --user=%dbUser% --password=%dbPassword% --single-transaction --add-drop-table --databases %%f > "%backupDir%\%dirName%\%%~nxf.sql"

 "%zip%" a -tgzip "%backupDir%\%dirName%\%%~nxf.sql.gz" "%backupDir%\%dirName%\%%~nxf.sql"

  del "%backupDir%\%dirName%\%%~nxf.sql"

 )
 popd

 :: delete all folders but the latest 3


 :: deletes all files and folders in the backupDir
 :: for /f "skip=3 delims=" %%a in ('dir "%backupDir%\" /b /o-d') do echo del "%backupDir%\%%a"


 :: deletes only files in backupDir
 :: for /f "skip=3 delims=" %%a in ('dir "%backupDir%\" /b /a-d') do echo del "%backupDir%\%%a"


 :: deletes all files and folders BUT keeps 3 latest FOLDERS in backupDir
 :: for /f "skip=3 delims=" %%a in ('dir "%backupDir%\" /b') do echo del "%backupDir%\%%a"


 :: deletes all files and folders BUT keeps 3 latest FOLDERS in backupDir
 :: for /f "skip=3 delims=" %%a in (' dir "%backupDir%\" /o-d /b') do echo rd /S /Q "%backupDir%\%%a"

pause

Answer 1

These two different lines work. Keeping all files there might be in the backupDir while deleting only the selected N latest nameDir folders.

This is adjusted from @JosefZ's comment Windows Batch Script to only keep the N latest FOLDERS in a directory

for /f "skip=2 delims=" %%a in ('dir "%backupDir%\" /B /O:-N /A:D') do echo rd rd /s /q "%backupDir%\%%a"

This also works taken from @Magoo's answer from here https://stackoverflow.com/a/17521693/1010918

for /f "skip=2 delims=" %%a in (' dir "%backupDir%\" /b /ad-h /o-d') do rd /s /q "%backupDir%\%%a"

Find the complete working Batch Script here Windows Batch Script to backup local MySQL databases & only keep N latest FOLDERS with backup files if you like.

Thank you all for your help!