Calculating Time Difference in Batch

Sometimes you may want to know how much time it takes for a set of actions to run in batch. For example you have a Scheduled Task, or, you are doing some kind of benchmark you don't want to supervise. It is a bit hard to do time calculations in batch due to the lack of the time-calculating functions. But it's not impossible. The batch script below will gather the current system time through WMI (so it's regional-setting independent), convert it to seconds, and record in a variable. After running the tasks it will calculate the time again, and then format the difference to be human readable. In this example the difference will be echoed to the console, but it can be also recorded in a logfile by appending logfile.txt to the end of the echo line.

One case when the script gives incorrect values is at the end of the month (when the script starts this month but ends in the next month).

I will use this script in one of my next post when I will do a compression benchmark with 7-zip. It will calculate the time taken to compress a file.

So, here's the script:

@echo off
::::::::::::::::::::::::::::::::::::::::::
::  TimeDiff v1.00 by LEVENTE ROG       ::
::       www.thesysadminhimself.com     ::
::::::::::::::::::::::::::::::::::::::::::

::[ EULA ]:::::::::::::::::::::::::::::::::::::::::::::::::::::::::
::  Feel free to use this script. The code can be redistributed  ::
::  and edited, but please keep the credits.                     ::
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

::[ CHANGELOG ]::::::::::::::
::  v1.00 - First Version  ::
:::::::::::::::::::::::::::::


FOR /F "skip=1 tokens=1-6" %%A IN ('WMIC Path Win32_LocalTime Get Day^,Hour^,Minute^,Second /Format:table ^| findstr /r "."') DO (
 set Milisecond=%time:~9,2% 
 set Day=%%A
 set Hour=%%B
 set Minute=%%C
 set Second=%%D
)
set /a Start=%Day%*8640000+%Hour%*360000+%Minute%*6000+%Second%*100+%Milisecond%

::
::
:: PUT COMMANDS HERE
ping www.thesysadminhimself.com
::
::

FOR /F "skip=1 tokens=1-6" %%A IN ('WMIC Path Win32_LocalTime Get Day^,Hour^,Minute^,Second /Format:table ^| findstr /r "."') DO (
 set Day=%%A
 set Hour=%%B
 set Minute=%%C
 set Second=%%D
)
set Milisecond=%time:~9,2% 
set /a End=%Day%*8640000+%Hour%*360000+%Minute%*6000+%Second%*100+%Milisecond%
set /a Diff=%End%-%Start%
set /a DiffMS=%Diff%%%100
set /a Diff=(%Diff%-%DiffMS%)/100
set /a DiffSec=%Diff%%%60
set /a Diff=(%Diff%-%Diff%%%60)/60
set /a DiffMin=%Diff%%%60
set /a Diff=(%Diff%-%Diff%%%60)/60
set /a DiffHrs=%Diff%

:: format with leading zeroes
if %DiffMS% LSS 10 set DiffMS=0%DiffMS!%
if %DiffSec% LSS 10 set DiffMS=0%DiffSec%
if %DiffMin% LSS 10 set DiffMS=0%DiffMin%
if %DiffHrs% LSS 10 set DiffMS=0%DiffHrs%

echo %DiffHrs%:%DiffMin%:%DiffSec%.%DiffMS%

NOTE: To copy the entire code, just double-click and hit CTRL+C

Batch Compress SQL Backups and upload to FTP

I always repeat myself saying that compressing SQL backups is a really-really good thing. Storing backups at an offsite location is another good thing. Combining the two would be the first step in a disaster recovery plan. That's why I created a little script that will compress all files with .bak extension and upload them to an FTP server if needed. I added some variables for easier customization.

@echo off

::::::::::::::::::::::::::::::::
:: compress SQL backups v1.02 ::
::::::::::::::::::::::::::::::::

:: what's new
:: v1.02 - added ERRORLEVEL handling when forfiles find no files matching criteria
:: v1.01 - added BackupDeleteOlderThanDays function
:: v1.02 - fixing ERRORLEVEL

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: Variables:                                                                                                              ::
:: BACKUPDIRECTORY - the directory where the .bak files are located                                                        ::
:: BACKUPDELETEOLDERTHANDAYS - delete backup files older than x days. If no value specified, backups will not be deleted   ::
:: PACKLOWPRIORITY - use 1 to set 7-zip start in low priority mode. recommended.                                           ::
:: PACKONECPU - use 1 to set 7-zip use only one CPU for compression. recommended for servers with constantly high CPU load ::
:: PACKCOMPRESSION - use MAX or FAST - max will use LZMA2 Ultra compression while FAST will do PPMD Normal                 ::
:: UPLOADTOFTP and FTP* are self-explanatory                                                                               ::
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

set BackupDirectory=D:\SQLBackups\Scheduled
set BackupDeleteOlderThanDays=90
set PackLowPriority=1
set PackOneCPU=1
set PackCompression=MAX
set UploadToFTP=1
set FTPHost=10.192.32.11
set FTPUser=username
set FTPPass=password
set FTPDir=SQLBackups\Production

if %PackLowPriority% == 1 ( set PackLowPriority=/low ) ELSE ( set PackLowPriority=/normal )
if %PackOneCPU% == 1 ( set PackOneCPU=-mmt=off ) ELSE ( set PackOneCPU= ) 
if %PackCompression% == MAX ( set PackCompression=-mx9 -t7z -m0=lzma2 ) ELSE ( set PackCompression=-mx5 -t7z -m0=PPMd )
cd /d %BackupDirectory%

if %UploadToFTP% == 1 (
 echo user %FTPUser%> %temp%\daily_ftp.txt
 echo %FTPPass%>> %temp%\daily_ftp.txt
 echo cd %FTPDir%>> %temp%\daily_ftp.txt
 echo bin>> %temp%\daily_ftp.txt
)

FOR /r %%F IN (*.bak) DO (
 start "Pack" /w %PackLowPriority% "c:\Program Files\7-Zip\7z.exe" u %PackCompression% "%%F.7z" "%%F"
 if exist %%F.7z (
  IF !ERRORLEVEL! == 0 (
   del "%%F"
   if %UploadToFTP% == 1 ( echo put %%F.7z>> %temp%\daily_ftp.txt )
   )
 )
)

if %UploadToFTP == 1 (
 echo bye>> %temp%\daily_ftp.txt
 ftp -n -s:%temp%\daily_ftp.txt %FTPUser%
 del %temp%\daily_ftp.txt
 set FTPHost=
 set FTPUser=
 set FTPPass=
 set FTPDir=
)

if defined BackupDeleteOlderThanDays (
 set ErrorLevelOriginal=!ERRORLEVEL!
 Forfiles -p %BackupDirectory% -s -m *.bak -d -%BackupDeleteOlderThanDays% -c "cmd /c del /q @path" 
 Forfiles -p %BackupDirectory% -s -m *.zip -d -%BackupDeleteOlderThanDays% -c "cmd /c del /q @path" 
 Forfiles -p %BackupDirectory% -s -m *.rar -d -%BackupDeleteOlderThanDays% -c "cmd /c del /q @path" 
 Forfiles -p %BackupDirectory% -s -m *.7z -d -%BackupDeleteOlderThanDays% -c "cmd /c del /q @path" 
 set ERRORLEVEL=%ErrorLevelOriginal%
)

NOTE: To copy the entire code, just double-click and hit CTRL+C
NOTE: There are some issues with the syntax highlighter, some comment lines are colored red instead of green.

7-zip compress full paths from directory listing

7-zip is an excellent free tool for file compression, but it lacks a feature that is very annoying. 

This is preserving the absolute path of the files in an archive. When adding multiple files one-by-one, eg. with a batch script, this can cause lots of headaches. The same happened to me, until I wrapped up this little batch script that will CROP down the trailing drive letters from a directory listing. In this example I will list files that have been changed since a specific date but a dir /s /b > filelist.txt will also do the job (look out for the correct quotation marks that might differ between forfiles and dir)

@echo off
REM -- you need this for dynamic variables
setlocal ENABLEDELAYEDEXPANSION
REM -- date since files have changed
set Since=11/25/2010
REM -- root path for the data being archived as well as for the 7z file in this case
set MyAppPath=C:\Inetpub\wwwroot\PDF
REM -- change active directory to root so 7z will like the listing
cd /d C:\
REM -- generate file list
Forfiles -p %MyAppPath% /s /m *.* /D %Since% /C "cmd /C echo @path>>%MyAppPath%\filelist.txt"
REM -- take all lines from filelist.txt, crop down the trailing drive letter and add a " in front
REM -- then execute 7z with the result; 7z will use store compression in this case.
for /f "tokens=* delims= " %%a in (%MyAppPath%\filelist.txt) do (
 set PDFPath=%%a
 "c:\program files\7-zip\7z.exe" a -t7z -mx0 %MyAppPath%\archive.7z ^"!PDFPath:~4,-1!
)
del filelist.txt

And that's it :)