Auto delete files older than x number of days

Following script is ideal where you want to clear the space from backup repository and keep files created in last x number of day, I've been utilizing this to delete all SQL backup files *.bak from backup repository D:\foldername where anything older than 6 days should be deleted.

Create a batch file  Script.bat and Add it the Scheduled Tasks to run daily/weekly/monthly


forfiles /p "D:\foldername" /m *.bak /s /d -6 /c "cmd /c del @PATH"


Switches used:

/P    pathname
/M    searchmask
/S   recuse and check all the subdirectories
/D    date
Selects files with a last modified date greater than or equal to (+), or less than or equal to (-), the specified date using the "dd/MM/yyyy" format; or selects files with a last modified date greater than or equal to (+) the current date plus "dd" days, or less than or equal to (-) the current date minus "dd" days. A valid "dd"  number of days can be any number in the range of 0 - 32768. "+" is taken as default sign if not specified.


/C    command       Indicates the command to execute for each file.
                    Command strings should be wrapped in double quotes.

                    The default command is "cmd /c echo @file".

                    The following variables can be used in the command string:

                    @file    - returns the name of the file.
                    @fname   - returns the file name without extension.
                    @ext     - returns only the extension of the file.
                    @path    - returns the full path of the file.
                    @relpath - returns the relative path of the file.
                    @isdir   - returns "TRUE" if a file type is a directory, and "FALSE" for files.
                    @fsize   - returns the size of the file in bytes.
                    @fdate   - returns the last modified date of the file.
                    @ftime   - returns the last modified time of the file.

Internal CMD.exe commands should be preceded with "cmd /c".

SQL Flat File Daily Backup Job and Daily Checks script

Backup of MS SQL 2005/2008 Databases from SQL Maintenace job is way easier and quicker when it comes to restoring compared to BackupExec or similar products. 
Also SQL Agent maintains the log of scheduled maintenance task, successes and failures etc.

Following are the steps for creating automated SQL Backup Maintenance job and Batch script to check if your backup has been successful and drop you an email on daily basis
 

Create SQL Backup Maintenance Job
http://www.freetutorialssubmit.com/create-ms-sql-backup-maintenance-plan/1585

 
 Verify backup path and Backup file format


 

Create Batch script to monitor the Backup folders

Create a batch file sqlcheck.bat
  @echo off
FOR /F "TOKENS=1,2 DELIMS=/ " %%A IN ('DATE /T') DO SET mm=%%B
FOR /F "TOKENS=2,3 DELIMS=/ " %%A IN ('DATE /T') DO SET yyyy=%%B
FOR /F "TOKENS=1 DELIMS=/ " %%A IN ('DATE /T') DO SET dd=%%A

set efin=%mm%%dd%%yyyy%
if %dd% LSS 10 set dd=%dd:~-1%
set /A dd=%dd%-1
IF %dd% LSS 10 set dd=0%dd%


if %dd% equ 00 if %mm% equ 01 (
Set dd=31
Set mm=12

)

if %dd% equ 00 if %mm% equ 02 (
Set dd=31
Set mm=01

)

if %dd% equ 00 if %mm% equ 03 (
Set dd=28
Set mm=02

)

if %dd% equ 00 if %mm% equ 04 (
Set dd=31
Set mm=03

)
if %dd% equ 00 if %mm% equ 05 (
Set dd=30
Set mm=04

)
if %dd% equ 00 if %mm% equ 06 (
Set dd=31
Set mm=05

)
if %dd% equ 00 if %mm% equ 07 (
Set dd=30
Set mm=06


)
if %dd% equ 00 if %mm% equ 08 (
Set dd=31
Set mm=07

)

if %dd% equ 00 if %mm% equ 09 (
Set dd=31
Set mm=08

)

if %dd% equ 00 if %mm% equ 10 (
Set dd=30
Set mm=09

)

if %dd% equ 00 if %mm% equ 11 (
Set dd=31
Set mm=10

)

if %dd% equ 00 if %mm% equ 12 (
Set dd=30
Set mm=11

)


set ymd=%yyyy%%mm%%dd%
REM Backup file format Northwind20121217.bak

set y_m_d=%yyyy%_%mm%_%dd% 
REM Backup file format Northwind_2012_12_17.bak
 
:END

REM Examples
dir \\RepositoryServer\d$\backup\*.bak |find "/"
dir \\RepositoryServer\Backup\DatabaseName\*.bak | find "/"
dir \\RepositoryServer\Backup\DatabaseName\*%ymd%*1400.bak | find "/"
dir \\RepositoryServer\Backup\DatabaseName\*%ymd%*.bak | find "/"
dir \\RepositoryServer\Backup\DatabaseName\*%y_m_d%*1400.bak | find "/"

Schedule to run daily and email the report



download BMAIL.exe to email the report


Create second batch file sqlreport.bat to count number of lines to ensure all the databases are backed up and add it to Windows Scheduled Task:


sqlcheck.bat > sqlcheck.txt

REM following will count no of Databases backed up and add on the bottom of the txt file
for /f "tokens=* delims= " %%t in ('dir/b sqlcheck.txt') do (
for /f "tokens=* delims= " %%a in (%%t) do (
set /a N+=1
)
)
@echo ****************************** >> "SQLCheck.txt"
echo Total Backedup files count = !N! >>"SQLCheck.txt"
 
REM 120 are the number of databases my script was checking daily
Set /A p=120-%N%
echo Flat Files missed = %p% >>"SQLCheck.txt"


bmail -s smtpservername -t emailaddress@domain.com -f sendersaddress@domain.com-h -a "SQLCheck" -m SQLCheck.txt -c