Batch file to delte and move files

I'm not certain I get what you are after (completely). The above works for a single drive. Do you just want to extend that to get applied to 4 different drives, plus moving the remaining files to E:\SQL-EndOfMonth\Databases (including the respective DB folder)? In that case a simple  method (but not the fastest) is to do it step by step:

@echo off
set root=SQL-E\SQL-Backup\Databases
for %%F in (HFI Dynamics Master) do (
  for %%D in (D: F: G: H:) do (
    for /F "tokens=* skip=30" %%A in ('dir /b /a:-d /o:-d "%%D\%root%\%%F\*.bak" ') do del "%%D\%root%\%%F\%%~A"
    move "%%D\%root%\%%F\*" "E:\%root%\%%F\"
  )
  for /F "tokens=* skip=30" %%A in ('dir /b /a:-d /o:-d "E:\%root%\%%F\*.bak" ') do del "E:\%root%\%%F\%%~A"
)

Select allOpen in new window

where the 4 disks are D:, F:, G:, and H:

I don't see how that finds those files that are end of month files, for instance DYNAMICS_db_201303302145.BAK and HFI_db_201303302145.BAK and master_db_201303302202.BAK (and all the other end of month files) and move them to the endofmonth folder before deleting the rest, except the latest 30. With regard to the multiple drives I will copy all the files from the other 3 onto onto one drive, the vast majority already are on all 4, then use this solution to get and move the end of month files and then use that drive to make a mirror copy to the other 3 drives. I then plan to use this solution as part of the daily backups to ensure these drives only keep the latest 30 and then when the next end of month comes around, move it the endofmonth folder. Hope that makes sense--thanks.

Sorry, misread the question. I thought you wanted to keep the 30 most recent files for each DB.
I don't know what you want to gain by keeping 4 mirrors of backup files, but understand you want to have a set of
* 30 most recent files per DB on the mirrors
* all end-of-month files on E:

The first part is what you already have. Note that after running the deletion part, you won't have much to run the end-of-month part on, but I reckon you know that already.
I suggest not to move the end-of-month files, but just copy them. The 30 remaining files (after deleting all other) will then still count those in, and you have a continuous set of files (otherwise you have e.g. ...,29 March, 30 March, 1 April, ..., and the 31 March on E: only).

Date arithmetic is difficult in a batch file, unless we use external tools or VBS. However, the end of month can be stored as a fixed "array" of dates, and that keeps it easy.

@echo off
set root=SQL-E\SQL-Backup\Databases
set eom=0131 0228 0331 0430 0531 0630 0731 0831 0930 1031 1130 1231
for %%F in (HFI Dynamics Master) do (
  for %%T in (%eom%) do xcopy /D "D:\%root%\%%F\%%F_db_????%%T*.bak E:\%root%\%%F\ 2>nul
  for /F "tokens=* skip=30" %%A in ('dir /b /a:-d /o:-d "D:\%root%\%%F\*.bak" ') do del "D:\%root%\%%F\%%~A"
)

Select allOpen in new window

This one is slightly faster, as it will not have to run 12 xcopy each time. But I like the above code more.

@echo off
set root=SQL-E\SQL-Backup\Databases
for %%F in (HFI Dynamics Master) do (
  pushd D:\%root%\%%F
  for %%T in (*_db????0131* *_db????0228* *_db????0331* *_db????0430* *_db????0531* *_db????0630* *_db????0731* *_db????0831* *_db????0930* *_db????1031* *_db????1130* 1*_db????231*) do xcopy /D "%%~T E:\%root%\%%F\ 2>nul
  for /F "tokens=* skip=30" %%A in ('dir /b /a:-d /o:-d *.bak ') do del "%%~A"
  popd
)

Select allOpen in new window

I will try the first one you suggested. And you are right I will run the code to keep the last 30 last. I know there is a way to test it to echo what it will do or to show what it will do without actually doing it. Can you help with that--maybe send which files it will copy to a log file so I can make sure it is doing it right before actually doing it? And won't MOVEing it be quicker than XCOPYing it?

Move is only faster if it is on the same file system, as it will only change a file system entry (move a reference). For another drive (or junction point or the like), move has to perform a copy and delete.

For debugging, best to put echo before each command you want to see. For the first script I would use a mixture of echo for del and the /L (show files, don't copy) for xcopy:

@echo off
set root=SQL-E\SQL-Backup\Databases
set eom=0131 0228 0331 0430 0531 0630 0731 0831 0930 1031 1130 1231
for %%F in (HFI Dynamics Master) do (
  for %%T in (%eom%) do xcopy /D /L "D:\%root%\%%F\%%F_db_????%%T*.bak E:\%root%\%%F\ 2>nul
  for /F "tokens=* skip=30" %%A in ('dir /b /a:-d /o:-d "D:\%root%\%%F\*.bak" ') do echo del "D:\%root%\%%F\%%~A"
)

Select allOpen in new window

I will use move then because the files are on the same drive, although that was not clear from my description. so would this be right if I don't want to actually move but test move
for %%T in (%eom%) do echo move "D:\%root%\%%F\%%F_db_????%%T*.bak E:\%root%\%%F\ 2>nul

It's ok, but you will always see 12 move commands that way.

Will this work so that I have a log of what is done each day. Note I changed the destination folders of the MOVE or XCOPY
@echo off
set root=SQL-E\SQL-Backup\Databases
set eom=0131 0228 0331 0430 0531 0630 0731 0831 0930 1031 1130 1231
for %%F in (HFI Dynamics Master) do (
for %%T in (%eom%) do xcopy /D /L "D:\%root%\%%F\%%F_db_????%%T*.bak D:\SQL-EndOfMonth\%%F\ 2>C:\Backup\SQL-Moved.Txt
or
for %%T in (%eom%) do move "D:\%root%\%%F\%%F_db_????%%T*.bak D:\SQL-EndOfMonth\%%F\ 2>C:\Backup\SQL-Moved.Txt

for /F "tokens=* skip=30" %%A in ('dir /b /a:-d /o:-d "D:\%root%\%%F\*.bak" ') do echo del "D:\%root%\%%F\%%~A" >C:\Backup\SQL-DailyDelete.Txt
)

2> is the error output. Since we do not check whether there are any files matching the date mask, it is very likely we will get 11 "not found" and 1 "move" (or copied) message. We do not want to know about those "not found" ones. (My second script does not have that issue - the command is run only for existing files).
In addition, and in general, you should append to the log, else you only have the last result. Appending is done with >> instead of > (for error, it's  2>> of course). This is particular true for the result of FOR commands - each FOR go will overwrite the prior output! For demonstration use the following example (directly in the command prompt):

@for %i in (1 2 3) do @echo %i > temp.txt

Select allOpen in new window

You will only see the last result, 3, in the file. There are two approaches to correct that:

(@for %i in (1 2 3) do @echo %i) > temp.txt

Select allOpen in new window

or

@for %i in (1 2 3) do @echo %i >> temp.txt

Select allOpen in new window

The second is slightly slowler, as the file is reopened with each FOR step, while the first one collects all output, and then writes it as a whole into the file. The con of the first is that you will see the final result only after the FOR is completed. Both does not matter here.

Using MOVE, the script would then be:

@echo off
set root=SQL-E\SQL-Backup\Databases
set eom=0131 0228 0331 0430 0531 0630 0731 0831 0930 1031 1130 1231
for %%F in (HFI Dynamics Master) do (
  for %%T in (%eom%) do move "D:\%root%\%%F\%%F_db_????%%T*.bak D:\SQL-EndOfMonth\%%F\ 2>nul >>C:\Backup\SQL-Moved.Txt
  for /F "tokens=* skip=30" %%A in ('dir /b /a:-d /o:-d "D:\%root%\%%F\*.bak" ') do del "D:\%root%\%%F\%%~A" >>C:\Backup\SQL-DailyDelete.Txt
)

Select allOpen in new window

OK tried my first test and got this error
set eom=0131 0228 0331 0430 0531 0630 0731 0831 0930 1031 1130 1231
The syntax of the command is incorrect.
Would it be better to read a file?

for %%T in (%eom%) do move "D:\%root%\%%F\%%F_db_????%%T*.bak D:\SQL-EndOfMonth\%%F\ 2>nul >>C:\Backup\SQL-Moved.Txt
Is not creating a log file whereas the other command is.

That FOR will do nothing if the set eom fails, because the var's content is assumed to be empty.
However, that set line is syntactically correct. Make sure you do not have extra characters anywhere, in particular at the very beginning of the line. I copy&pasted the command as you showed it in http:#a39141690, and it works.

OK the eom thing is fixed but the move is still not logging--no file created for a log

I spotted a missing double quote in the MOVE line:

@echo off
set root=SQL-E\SQL-Backup\Databases
set eom=0131 0228 0331 0430 0531 0630 0731 0831 0930 1031 1130 1231
for %%F in (HFI Dynamics Master) do (
  for %%T in (%eom%) do move "D:\%root%\%%F\%%F_db_????%%T*.bak" "D:\SQL-EndOfMonth\%%F\" 2>nul >>C:\Backup\SQL-Moved.Txt
  for /F "tokens=* skip=30" %%A in ('dir /b /a:-d /o:-d "D:\%root%\%%F\*.bak" ') do del "D:\%root%\%%F\%%~A" >>C:\Backup\SQL-DailyDelete.Txt
)

Select allOpen in new window

I do have several other folders, one with a space OLD HFI how do I add that to my list?
for %%F in (HFI Dynamics Master "OLD HFI") do (

Can you use single quotes or is this the way it must be done?

Single quotes are not really supported. They do work sometimes, but I wouldn't trust in that. The proper way is what i showed you.

I've been following this thread with interest, but since you are in very capable hands with Qlemo haven't tossed in any different approaches.  But I did want to point out a couple of points that I see, in case they are important.

(1) As it currently stands leap year is ignored.  So if a backup was created during a leap year on 2/29, it will not be saved as the month end backup, but rather the backup from 2/28 will be saved.  This may not matter, but if it does we need to account for that.  I have some thoughts on how that could be handled, but no point getting into that if it doesn't matter.

(2) As it stands now, even though you are saving the last 30 backup files, you could/will be missing the last monthend backup file, since that will have been moved to the save area already.  Again, this may not matter, but if you truly need the exact last 30 backup in the active location we would need to add some code to handle that situation as well.

From a purely style point of view, I typically handle the folder list something like this.  Takes a little extra typing, but I think makes it clearer in the long run and easier to maintain.

@echo off
set root=SQL-E\SQL-Backup\Databases
set eom=0131 0228 0331 0430 0531 0630 0731 0831 0930 1031 1130 1231
set folders="HFI","Dynamics","Master","OLD HFI"
for %%F in (%folders%) do (
  for %%T in (%eom%) do move "D:\%root%\%%~F\%%~F_db_????%%T*.bak" "D:\SQL-EndOfMonth\%%~F\" 2>nul >>C:\Backup\SQL-Moved.Txt
  for /F "tokens=* skip=30" %%A in ('dir /b /a:-d /o:-d "D:\%root%\%%~F\*.bak" ') do del "D:\%root%\%%~F\%%~A" >>C:\Backup\SQL-DailyDelete.Txt
)

Select allOpen in new window

~bp

Bill,

I read the requirements as being less strict, so I ignored the leap year. In regard of the eom backup not being part of the "30 files", that is something I mentioned earlier. But of course both are valid points.

There are leap years involved so how do we deal with that--as you noted and are correct it is not all that strict so if it's "easy" to deal with then let's add it--if it's lots of work I think I can get by without it.  May be nice to have though so thanks.

Okay, here's a swing at it.  Took a little more code than I might like, but haven't looked for optimizations yet.  Adjust the SET lines at the top as needed.  Right now it will only write the DEL and MOVE commands it would execute to the log file, so you cxan see if it is working properly.  If it is then remove the two ECHO's on those lines.

@echo off
setlocal EnableDelayedExpansion

REM set BaseDir=D:\SQL-E\SQL-Backup\Databases
REM set SaveDir=D:\SQL-EndOfMonth
set LogFile=C:\EE\EE28118165\log.txt
set folders="HFI","Dynamics","Master","OLD HFI"

(
  for %%F in (%folders%) do (
    if exist "%BaseDir%\%%~F\*.bak" (
      for /F "tokens=* skip=30" %%A in ('dir /b /a:-d /o:-d "%BaseDir%\%%~F\*.bak" ') do (
        call :CheckEOM "%%~nA"
        if "!EOM!" EQU "Y" (
          call :Log "Moved   : '%BaseDir%\%%~F\%%~A'"
          ECHO move "%BaseDir%\%%~F\%%~A" "%SaveDir%\%%~F\"
        ) else (
          call :Log "Deleted : '%BaseDir%\%%~F\%%~A'"
          ECHO del "%BaseDir%\%%~F\%%~A"
        )
      )
    )
  )
) >>"%LogFile%" 2>&1

exit /b
                                            
:CheckEOM [filename]
  setlocal
  set _eom=N
  set _name=%~1
  set _yyyy=%_name:~-12,4%
  set _mm=%_name:~-8,2%
  set _dd=%_name:~-6,2%
  set /a _leap=%_yyyy% %% 4
  if %_leap% EQU 0 (
    set _end=0131 0229 0331 0430 0531 0630 0731 0831 0930 1031 1130 1231
  ) else (
    set _end=0131 0228 0331 0430 0531 0630 0731 0831 0930 1031 1130 1231
  )
  for %%a in (%_end%) do (
    if "%%a" EQU "%_mm%%_dd%" set _eom=Y
  )
  endlocal & set EOM=%_eom%
  exit /b                                   

:Log [message]
  set D=%DATE:~-10%
  set D=%D: =0%
  set T=%TIME:~0,8%
  set T=%T: =0%
  echo [%D% %T%] %~1
  exit /b

Select allOpen in new window

~bp

The year-modulo-4 rule to determine leap years will suffice for the next several years (2001 to 2099), but the correct formula is much more complicated (no leap year on century boundaries, but on multiples of 400, but not on milleniums, ...).

Since the order of dates to check is not important, I would prefer a small simplification for lines 36 to 40, because I hate repetitions:

  set _end=0131 0331 0430 0531 0630 0731 0831 0930 1031 1130 1231
  if %_leap% EQU 0 (set _end=%_end% 0229) else (set _end=%_end% 0228)

Select allOpen in new window

Since the order of dates to check is not important, I would prefer a small simplification for lines 36 to 40, because I hate repetitions:

Bill likes.

~bp

line 36 to 40 or line 37 to 40?

OK nothing happens--this is what the log file generates
Logfile.txt

If you do the following command at a command line, does it display files?

dir "D:\SQL-E\SQL-Backup\Databases\HFI\*.bak"

Select allOpen in new window

~bp

yes it does

Try adding an ECHO in as below and rerun to see if it is actually processing each file.

      for /F "tokens=* skip=30" %%A in ('dir /b /a:-d /o:-d "%BaseDir%\%%~F\*.bak" ') do (
        ECHO File:"%%~nA"
        call :CheckEOM "%%~nA"

Select allOpen in new window

~bp

Guys thanks for all the help. I got the batch to work somewhat but in some cases it is deleting some needed files (luckily I had backups) so I am using both your suggestions and way of doing it for day by day maintenance instead of trying to go back in history and get all the end of month files. I used Windows Explorer and did a search on *0131*-then moved all those files; the *0228*, etc. etc. and moved them. However i would prefer having a batch that works in the future when I encounter this again so could I ask to go back to your last solution and break down what each command does. if you can't or don't have time that's OK too I will reward the points anyway since this has been helpful if not a final solution--thanks for all your time on this--I do appreciate it very much

Thanks again

is there an easier, cleaner way to do this with VB script of powershell? Do either of you have a good place to go to learn powershell basics?

PowerShell is strange at the first glance, because it processes objects, and in a much reacher way than VBS does. But you can get an idea by reading the articles here on EE about PowerShell - there are a few, and they are for beginners.
Another good source for wisdom is the Scripting Guy (http://blogs.technet.com/b/heyscriptingguy/), as he blogs about real-life issues, and how to get a solution for it.
I can also recommend Mastering PowerShell (http://powershell.com/mastering-powershell.pdf), a free eBook. Though it is based on PS1, it covers the basics very well.

In this particular case I would not think about VBS (anymore), PS provides better means.

$BaseDir='D:\SQL-E\SQL-Backup\Databases'
$SaveDir='D:\SQL-EndOfMonth'
$LogFile='C:\EE\EE28118165\log.txt'
$Folders='HFI','Dynamics','Master','OLD HFI'

foreach ($folder in $Folders) 
{
  get-childitem "$BaseDir\$folder\*.bak" | 
    ? { !$_.PsIsContainer } |
    sort LastWriteTime -Descending |
    select -Skip 30 |
    % {
      if ($_.LastWriteTime.AddDays(1).Month -ne $_.LastWriteTime.Month)
      { # end of month file
        Write-Output "[$(Get-Date -format 'dd HH:mm')] Moved: $($_.FullName)"
        Move-Item $_ "$SaveDir\$folder\" -whatif
      } else {
        Write-Output "[$(Get-Date -format 'dd HH:mm')] Deleted: $($_.FullName)"
        Remove-Item $_ -whatif
      }
    }
} >> $LogFile 2>&1

Select allOpen in new window

This is the direct translation of Bill's code. The -whatif displays what would be done; to let it happen remove that switch. As you can see, there isn't much of a difference here - but date arithmetic is easy.

I did just notice the following two lines are commented out in my solutions, and shouldn't be.  I suspect I had been using different paths for local testing here, and forgot to uncomment these, argh.

REM set BaseDir=D:\SQL-E\SQL-Backup\Databases
REM set SaveDir=D:\SQL-EndOfMonth

I will add further comments and explanations to the BAT file if that's helpful, and could work up a VBS approach. But don't want to do that if you are leaning towards the PowerShell approach that Qlemo provided (nicely done).

~bp

No no VBS will go PS; I need to learn it for system admin too. but would like an explanation of what each command in batch does, if you have the time. thanks

Some basics first:
That much to the basics. You can always get help about a command by using e.g. get-help foreach*, even get-help *file*.

Now to the script. I'll explain by line numbers.

1-4: should be obvious.

6: goes thru elements of $Folder one by one, and stores the current one in $folder.
We are using strings here, but anything in PS is an object, even "simple" types like integers or strings. The commands between {} are executed for each loop go.

8: Get-ChildItem works similar to DIR, but can do much more (e.g. read the registry). Without any parameter besides the filename specification it will only retrieve the corresponding files and directories.

9: Because there might be folders named with .BAK extension, we need to filter those. ? is the alias of where-object, which applies a condition on the pipeline, and only let objects pass which meet the condition.
In a pipeline $_ is the "current object". In this particular case it is a file or folder object, and the IsPSContainer property is set for all folders.

10: We are sorting the objects (filtered by where-object) by the modification date, in descending order. sort is the same as sort-object, which is the full PS name for the command.

11: select-object (short select) cna do a lot of stuff; skipping input, getting only the first or last n objects, and only passing a few selected properties down the pipe. Here we only use it to skip data.

12: % is short for foreach-object, which is very similar to the foreach of line 6. The difference is that it works on pipe input; hence $_ is the current input object again.

At that point, we can be sure to have skipped the 30 most recent files.

13: This is kind of tricky: Adding one day to a date, and then looking if the month has changed. If it did, we are at the very last day of the month.

15/18: Write-Output pushes data into the pipeline for further processing. Here we only use it to generate a string for logging.
(Get-Date -format 'dd HH.mm') builds a date string from the current timestamp.

16/19: Move-Item and Remove-Item are generic cmdlets to do exactly what they tell ;-). If you stuff a file object into them, they will act on that. Other possible objects are registry keys, variables, environment variables, ... - just to give you an idea ;-).

22: I have used the file direction as we are used to. Instead of >> $Logfile I could have used the "full PS" code | out-file -append $LogFile, which is exactly the same. (However, there is no replacement for the 2>&1 part).

Here's a shot at further comments on each line of the BAT script, let me know if you have further questions.

@echo off
REM Enable delayed expansion of environment variables so that we can accurately
REM check the EOM variable inside the FOR loop
setlocal EnableDelayedExpansion

REM Define base folder to process, and locate to archive files to
set BaseDir=D:\SQL-E\SQL-Backup\Databases
set SaveDir=D:\SQL-EndOfMonth

REM Specify location for log file of all activity
set LogFile=C:\EE\EE28118165\log.txt

REM Define the specific subfolder names to process in the base folder
set folders="HFI","Dynamics","Master","OLD HFI"

REM Wrap all processing in a block and write all output to a log file
(
  REM Process each subfolder we want to scan
  for %%F in (%folders%) do (
    REM Only search for files if some exist
    if exist "%BaseDir%\%%~F\*.bak" (
      REM get a listing of all backup files, sort newest to oldest, and skip the newest 30 files, look at the rest
      for /F "tokens=* skip=30" %%A in ('dir /b /a:-d /o:-d "%BaseDir%\%%~F\*.bak" ') do (
        REM Call subroutine passing file name to check if it's the last day of the month file
        call :CheckEOM "%%~nA"
        REM If last day of month, then move to archive folder
        if "!EOM!" EQU "Y" (
          call :Log "Moved   : '%BaseDir%\%%~F\%%~A'"
          move "%BaseDir%\%%~F\%%~A" "%SaveDir%\%%~F\"
        REM Not end of month file, just delete it
        ) else (
          call :Log "Deleted : '%BaseDir%\%%~F\%%~A'"
          del "%BaseDir%\%%~F\%%~A"
        )
      )
    )
  )
) >>"%LogFile%" 2>&1

REM Exit script
exit /b
               
REM Subroutine to check the date part of a filename to see if last day of month                             
:CheckEOM [filename]
  REM Keep variables local to the subroutine
  setlocal
  REM Assume it's not the end of month file for now
  set _eom=N
  REM Get the name from passed parm
  set _name=%~1
  REM Extrac the date components from file name
  set _yyyy=%_name:~-12,4%
  set _mm=%_name:~-8,2%
  set _dd=%_name:~-6,2%
  REM Leap years are evenly divisible by 4, use modulo division to get remainder
  set /a _leap=%_yyyy% %% 4
  REM Define MMDD pairs that represent the last days of the month (all but February)
  set _end=0131 0331 0430 0531 0630 0731 0831 0930 1031 1130 1231
  REM Define MMDD value for February based on leap year flag, add to list of last days of month
  if %_leap% EQU 0 (set _end=%_end% 0229) else (set _end=%_end% 0228)
  REM Check each end of month day (MMDD) and see if it matches this files MMDD value
  for %%a in (%_end%) do (
    if "%%a" EQU "%_mm%%_dd%" set _eom=Y
  )
  REM End local variables and set return variable EOM
  endlocal & set EOM=%_eom%
  REM Leave subroutine
  exit /b                                   

REM Subroutine to format a text message for logging, adding a date and time stamp to it
:Log [message]
  REM Keep variables local to the subroutine
  setlocal
  REM Get rightmost 10 characters of current date environment variable (removes day name if present)
  set D=%DATE:~-10%
  REM Replace any leading spaces with zeros
  set D=%D: =0%
  REM Get leftmost 8 characters of current date environment variable
  set T=%TIME:~0,8%
  REM Replace any leading spaces with zeros
  set T=%T: =0%
  REM Write the log message adding the date time stamp to the line
  echo [%D% %T%] %~1
  REM End local variables
  endlocal
  REM Leave subroutine
  exit /b

Select allOpen in new window

~bp

OK guys--thanks so much for ALL your time and for sharing your expertize--it is much appreciated. Hope you have a great day, God bless you--thanks.