asked on

Change batch to delete folder/directory older then 30 days

Can somebody help me to adjust the script that it will delete folders older the 30 days
This batch create a directory in c:\backup with the date and time(dutch), dump the sql backup, zip the file and delete the sql file

------
set ddump="c:\Program Files\MySQL\MySQL Server 5.7\bin"
set d7zip="C:\Program Files\7-Zip"
set ddate="%date:~-10,2%-%date:~-7,2%-%date:~-4,4%"
set ttime=%time:~0,2%.%time:~3,2%
set ddtback="c:\backup\%ddate%"" ""%ttime%"

md %ddtback%

%ddump%\mysqldump -u root -ppassword database > %ddtback%\database.sql"

%d7zip%\7z a -mx -tzip %ddtback%\archive.zip %ddtback%\*

del %ddtback%\database.sql
---------
Help is much appreciated Danny

oBdA

Try this; I rearranged it a bit.
In batch, it's usually best to only add quotes around a path at the last moment, that is, in the command you're using the variable.
I readjusted the time stamp format a bit (removed the space, and the dot as well, so it doesn't appear like an extension).
The time stamp and the cutoff date will be generated by a temporary VB script, because date calculation in batch is a mess.
The script will only delete backup folders that match the new time stamp format, so there's no danger of it removing your existing ones.
The format must remain in YYYYMMDD format, because the batch does a string comparison against the folder names.
It's in test mode and will only display the "rd" commands it would normally run. Remove the uppercase ECHO in line 21 to run it for real.

@echo off
setlocal enabledelayedexpansion
set dBackupRoot=C:\Backup
set dBin=%ProgramFiles%\MySQL\MySQL Server 5.7\bin
set d7zip=%ProgramFiles%\7-Zip
set AddDays=-30
call :GetTimeStamps %AddDays%
echo Time stamp today:      %TimeStampToday%
echo Time stamp calculated: %TimeStampCalculated%
set dBackup=%dBackupRoot%\%TimeStampToday%

md "%dBackup%"
"%dBin%\mysqldump.exe" -u root -ppassword database >"%dBackup%\database.sql" 
"%d7zip%\7z.exe" a -mx -tzip "%dBackup%\archive.zip" "%dBackup%\*"
del "%dBackup%\database.sql"

echo Cleaning up old backup directories ...
for /f "delims=" %%a in ('dir /b /a:d "%dBackupRoot%" ^| findstr.exe /i /r /c:"[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]_[0-9][0-9]-[0-9][0-9]"') do (
	if "%%a" LSS "%TimeStampCalculated%" (
		echo Removing '%%a' ...
		ECHO rd /s /q "%dBackupRoot%\%%a"
	) else (
		echo Keeping '%%a'.
	)
)

REM ===== Functions only below this line! =====
goto :eof
:GetTimeStamps <Days>
for /f "tokens=1 delims=[]" %%a in ('type "%~f0" ^| find /n "[VBScript]"') do set VBStart=%%a
(for /f "skip=%VBStart% delims=" %%a in ('type "%~f0"') do (echo %%a)) >"%Temp%\GetTimeStamp.vbs"
for /f "tokens=1,2 delims=|" %%a in ('cscript.exe /nologo "%Temp%\GetTimeStamp.vbs" %~1') do (
	set TimeStampToday=%%a
	set TimeStampCalculated=%%b
)
del "%Temp%\GetTimeStamp.vbs"
goto :eof

REM ===== No batch below this line! =====
[VBScript]
TodayDate = Now
TodayYear = Year(TodayDate)
TodayMonth = Month(TodayDate)	:	If TodayMonth < 10 Then TodayMonth = "0" & TodayMonth End If
TodayDay = Day(TodayDate)		:	If TodayDay < 10 Then TodayDay = "0" & TodayDay End If
TodayHour = Hour(TodayDate)		:	If TodayHour < 10 Then TodayHour = "0" & TodayHour End If
TodayMinute = Minute(TodayDate)	:	If TodayMinute < 10 Then TodayMinute = "0" & TodayMinute End If

CalculatedDate = DateAdd("d", WScript.Arguments(0), TodayDate)
CalculatedYear = Year(CalculatedDate)
CalculatedMonth = Month(CalculatedDate)		:	If CalculatedMonth < 10 Then CalculatedMonth = "0" & CalculatedMonth End If
CalculatedDay = Day(CalculatedDate)			:	If CalculatedDay < 10 Then CalculatedDay = "0" & CalculatedDay End If
CalculatedHour = Hour(CalculatedDate)		:	If CalculatedHour < 10 Then CalculatedHour = "0" & CalculatedHour End If
CalculatedMinute = Minute(CalculatedDate)	:	If CalculatedMinute < 10 Then CalculatedMinute = "0" & CalculatedMinute End If

Wscript.Echo TodayYear & "-" & TodayMonth & "-" & TodayDay & "_" & TodayHour & "-" & TodayMinute & "|" & CalculatedYear & "-" & CalculatedMonth & "-" & CalculatedDay & "_" & CalculatedHour & "-" & CalculatedMinute

Open in new window

Danny Kon

ASKER

oDba

"I readjusted the time stamp format a bit" --> Thats funny

Something is going wrong i rem echo and put a pause when it start 7zip
underneath i copy paste where it seems to go wrong
Danny
------
C:\Users\POS\AppData\Local\Temp\GetTimeStamp.vbs(2, 3) Compilatiefout Microsoft VBScript: Instructie wordt verwacht (It say something like a compilation fault and that a VB instruction is expected)

"C:\Backup\\database.sql"
mysqldump: [Warning] Using a password on the command line interface can be insecure.

C:\Users\POS\Desktop>"C:\Program Files\7-Zip\7z.exe" a -mx -tzip "C:\Backup\\archive.zip" "C:\Backup\\*"

7-Zip [64] 15.14 : Copyright (c) 1999-2015 Igor Pavlov : 2015-12-31

Open archive: C:\Backup\\archive.zip

ERRORS:
Headers Error
Unconfirmed start of archive

WARNINGS:
There are data after the end of archive

--
Path = C:\Backup\\archive.zip
Type = zip
ERRORS:
Headers Error
Unconfirmed start of archive
WARNINGS:
There are data after the end of archive
Physical Size = 47
Tail Size = 20971473

Error:
There is some data block after the end of the archive
Niet geïmplementeerd

System ERROR:
Niet geïmplementeerd

oBdA

Sorry, can't reproduce. Did you change anything in the script?
What's the output of the following (that doesn't actually do a lot).
Run it as

Whatever.cmd 2>&1 | clip.exe

Open in new window

(which puts the output straight into the clipboard), then paste it inside "Code Snippet" tags.

@echo off
setlocal enabledelayedexpansion
set dBackupRoot=C:\Backup
set dBin=%ProgramFiles%\MySQL\MySQL Server 5.7\bin
set d7zip=%ProgramFiles%\7-Zip
set AddDays=-30
call :GetTimeStamps %AddDays%
echo Time stamp today:      %TimeStampToday%
echo Time stamp calculated: %TimeStampCalculated%
set dBackup=%dBackupRoot%\%TimeStampToday%

ECHO md "%dBackup%"
ECHO "%dBin%\mysqldump.exe" -u root -ppassword database >"%dBackup%\database.sql" 
ECHO "%d7zip%\7z.exe" a -mx -tzip "%dBackup%\archive.zip" "%dBackup%\*"
ECHO del "%dBackup%\database.sql"

echo Cleaning up old backup directories ...
for /f "delims=" %%a in ('dir /b /a:d "%dBackupRoot%" ^| findstr.exe /i /r /c:"[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]_[0-9][0-9]-[0-9][0-9]"') do (
	if "%%a" LSS "%TimeStampCalculated%" (
		echo Removing '%%a' ...
		ECHO rd /s /q "%dBackupRoot%\%%a"
	) else (
		echo Keeping '%%a'.
	)
)

REM ===== Functions only below this line! =====
goto :eof
:GetTimeStamps <Days>
for /f "tokens=1 delims=[]" %%a in ('type "%~f0" ^| find /n "[VBScript]"') do set VBStart=%%a
(for /f "skip=%VBStart% delims=" %%a in ('type "%~f0"') do (echo %%a)) >"%Temp%\GetTimeStamp.vbs"
for /f "tokens=1,2 delims=|" %%a in ('cscript.exe /nologo "%Temp%\GetTimeStamp.vbs" %~1') do (
	set TimeStampToday=%%a
	set TimeStampCalculated=%%b
)
echo ----------------------------------------&type "%Temp%\GetTimeStamp.vbs"&echo ----------------------------------------&
del "%Temp%\GetTimeStamp.vbs"
goto :eof

REM ===== No batch below this line! =====
[VBScript]
TodayDate = Now
TodayYear = Year(TodayDate)
TodayMonth = Month(TodayDate)	:	If TodayMonth < 10 Then TodayMonth = "0" & TodayMonth End If
TodayDay = Day(TodayDate)		:	If TodayDay < 10 Then TodayDay = "0" & TodayDay End If
TodayHour = Hour(TodayDate)		:	If TodayHour < 10 Then TodayHour = "0" & TodayHour End If
TodayMinute = Minute(TodayDate)	:	If TodayMinute < 10 Then TodayMinute = "0" & TodayMinute End If

CalculatedDate = DateAdd("d", WScript.Arguments(0), TodayDate)
CalculatedYear = Year(CalculatedDate)
CalculatedMonth = Month(CalculatedDate)		:	If CalculatedMonth < 10 Then CalculatedMonth = "0" & CalculatedMonth End If
CalculatedDay = Day(CalculatedDate)			:	If CalculatedDay < 10 Then CalculatedDay = "0" & CalculatedDay End If
CalculatedHour = Hour(CalculatedDate)		:	If CalculatedHour < 10 Then CalculatedHour = "0" & CalculatedHour End If
CalculatedMinute = Minute(CalculatedDate)	:	If CalculatedMinute < 10 Then CalculatedMinute = "0" & CalculatedMinute End If

Wscript.Echo TodayYear & "-" & TodayMonth & "-" & TodayDay & "_" & TodayHour & "-" & TodayMinute & "|" & CalculatedYear & "-" & CalculatedMonth & "-" & CalculatedDay & "_" & CalculatedHour & "-" & CalculatedMinute

Open in new window

Danny Kon

ASKER

OdBa

My English is still not so good i dont understand what you mean with "then paste it inside "Code Snippet" tags."
I only change the dbname and the password i did the Whatever.cmd 2>&1 | clip.exe and paste the output in the attached file.
Let me know if i dont understand correct
The output of the echo %date% in a dutch windows 10 looks like
za 12-03-2016

Danny
output.txt

oBdA

When you post code (or output), either attach it as text file (like you did), or (a bit easier) use the "Style Code" tags (fourth from the right in the toolbar above the text box); makes it easier to copy and paste them.
The output looks pretty much okay, except that in the last line, the last "e" of "CalculatedMinute" went missing.
Please click the "Select all" link under the original code in my first post, hit Ctrl-C, and paste it into an empty notepad document, then save again.

Danny Kon

ASKER

oBdA

Thanks for the explanation never used it before for now i will attache the file.
It still is not working i did select all and paste it (Only changed password and db)
Attached the whole output when i removed echo and removed password

Danny
output1.txt

ASKER CERTIFIED SOLUTION

oBdA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Danny Kon

ASKER

oDbA

You are right sorry for the extra work

thanks for your help

Danny