export/delete using windows task scheduler

Have you looked around for scripts?  There are many references to them

Here is the first one I found:
http://grommit.harvard.edu/it/NLM/DELOLD.bat

i thought a simple "del" command or something like that would do, i guess i am wrong.
I am not sure how to use the script posted.

I placed the question in the Oracle Database zone hoping that someone must have already done this, especially someone who uses Oracle 9i database on a Windows machine. How would you setup a weekly , database level back-up and clean-up on a Windows server? Please advice!

Thanks!

i request that "Oracle Database Zone" also be added to the question.

Just an observation....

You say you export the database once a week (in other words, weekly) so therefore, would it not be better and easier to delete files older than 28 days rather than 30 days (4 x 7 = 28) and, every month has at least 28 days (including february) so, when the batch file runs on or near the 28th of any month, the previous month's file is deleted. Like this:

1) export the database
2) if there are any files 28 days (or more) old, then delete them (or it)

No need to muck about with complex mathematics to find files 28 days or older. This will work just as well:

@echo off

set day=%date:~0,2%
set month=%date:~3,2%
set year=%date:~6,4%

set datestring=%year%%month%%day%

@EXP SYSTEM/TEST@TEST FILE=D:\WeeklyBkps\TEST_FULL_%datestring%.dmp LOG=D:\WeeklyBkps\TEST_FULL_exp_%datestring%.log GRANTS=N ROWS=Y INDEXES=Y TRIGGERS=Y FULL=Y

set /a month-=1

if %month% lss 1 (
  set month=12
  set /a year-=1
)

if %day% gtr 28 set day=28

for /f "tokens=*" %%a in ('xcopy "D:\WeeklyBkps\*.*" "%temp% /c /d:%month%-%day%-%year% /l /y ^| find /v "File(s)"') do (
  del /f /q "%%a"
)

Select allOpen in new window

NOTE: Check I have made the correct variable assignments in lines 3, 4, 5 and 7.

Thank you very much for the responce!

yes, it can be 28 days, 30 days is just an example.

i will try the script you provided and let you know.

appreciate it!

I think the date format i posted in the test script is differenet, i need DD/MM/YYYY.

Is that what you used in variable declaration?

what do you get when you enter the following command:

    ECHO %DATE%

The date format is as follows--

C:\Users\Administrator>date
The current date is: Wed 03/21/2012
Enter the new date: (mm-dd-yy)

------
@slightwv : no worries about the zone , i have got a responce from paultomasi and am working with him. thanks for looking into this.

>>C:\Users\Administrator>date

That is NOT the command they asked about:    ECHO %DATE%

>>i have got a responce from paultomasi and am working with him. thanks for looking into this.

A response does not mean a good response...

I thought in Windows DATE and ECHO %DATE% are the same --

anyways below is the output for ECHO %DATE% as well ---

-------
C:\Users\Administrator>date
The current date is: Wed 03/21/2012
Enter the new date: (mm-dd-yy)

C:\Users\Administrator>ECHO %DATE%
Wed 03/21/2012

C:\Users\Administrator>
------------

I have adjusted the date assignments in lines 3, 4 and 5.
Please chack line 7. Is this the correct datestamp format?

@echo off

set day=%date:~7,2%
set month=%date:~4,2%
set year=%date:~10,4%

set datestring=%day%%month%%year%

@EXP SYSTEM/TEST@TEST FILE=D:\WeeklyBkps\TEST_FULL_%datestring%.dmp LOG=D:\WeeklyBkps\TEST_FULL_exp_%datestring%.log GRANTS=N ROWS=Y INDEXES=Y TRIGGERS=Y FULL=Y

set /a month-=1

if %month% lss 1 (
  set month=12
  set /a year-=1
)

if %day% gtr 28 set day=28

for /f "tokens=*" %%a in ('xcopy "D:\WeeklyBkps\*.*" "%temp% /c /d:%month%-%day%-%year% /l /y ^| find /v "File(s)"') do (
  del /f /q "%%a"
)

Select allOpen in new window

I still suggest you not use xcopy for this.  Why copy a file to TEMP just to 'DELETE' it?

The reason I used the following code...

    set day=%date:~7,2%
    set month=%date:~4,2%
    set year=%date:~10,4%
    set datestring=%day%%month%%year%

instead of this single line...

    for /f "tokens=2-4 delims=/ " %%a in ('date /t') do set datestring=%%c%%a%%b

is because the values returned by %%a, %%b and %%c are used later on when calculating the previous 28 day date for use with XCOPY.

Incidentally, XCOPY does not copy any files. It is used purely to list files that are older than the date specified in the '/d:' command line option.

>> I still suggest you not use xcopy for this.  Why copy a file to TEMP just to 'DELETE' it?

As stated in my previous post, XCOPY does not actually copy any files at all. The reason why %temp% is used as a destination is because XCOPY does not allow the use of NUL (unlike the COPY command) and of course, a destination must be included for XCOPY to work. Another reason is, everyone's got a TEMP environment variable (but don't shoot me down on this).

>>XCOPY does not actually copy any files

Apologies.  I just set up a quick test and stand corrected.  It appears it does not actually copy a file.

I had to go by the docs:
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/xcopy.mspx?mfr=true

"/d[:mm-dd-yyyy] : Copies source files changed on or after the specified date only."

However, your script appears to have a syntax issue.

I believe:
'xcopy "D:\WeeklyBkps\*.*" "%temp% /c

should be:
'xcopy "D:\WeeklyBkps\*.*" "%temp%" /c

Here is the sample I ran to test it:

@echo off
echo hello > c:\junk\q.txt

dir c:\junk\q.txt

for /f "tokens=*" %%a in ('xcopy "C:\junk\q.txt" "%temp%" /c /d:03-21-2010 /l /y ^| find /v 

"File(s)"') do (del /f /q "%%a")

dir c:\junk\q.txt
dir %temp%\q.txt

Select allOpen in new window

Try the following script, It will work fine with deleting old files. You have change following two things as per your requirement.

set dest=D:\WeeklyBkps
set retention=30

NOTE: If you are in windows 2003+, script will work fine, else you have to download forfiles form following link and extract it into windows folder.

http://www.dynawell.com/download/reskit/microsoft/win2000/forfiles.zip

@echo off
color 0c
MODE CON: COLS=50 LINES=11
for /F "tokens=1-4 delims=/ " %%i in ('date /t') do (
set MM=%%j
set DD=%%k
set YYYY=%%l
set datex=%%k-%%j-%%l
set dirdate=%%j%%k%%l
)
for /f "Tokens=1-2 delims=/ " %%i in ('time /t') do (
set tm=%%i
set ampm=%%j
)
set dest=D:\WeeklyBkps
set retention=30
forfiles -p "%dest%" /m *.* /s /d -%retention% /c "cmd /C del @FILE"
@EXP SYSTEM/TEST@TEST FILE=%dest%\TEST_FULL_%datex%.dmp LOG=%dest%\TEST_FULL_exp_%datex%.log GRANTS=N ROWS=Y INDEXES=Y TRIGGERS=Y FULL=Y

Select allOpen in new window

slightwv / crishna1

You're quite right for spotting the missing closing double-quote around %temp% in line 20. It should have read as follows:

for /f "tokens=*" %%a in ('xcopy "D:\WeeklyBkps\*.*" "%temp%" /c /d:%month%-%day%-%year% /l /y ^| find /v "File(s)"') do (

Select allOpen in new window

As for testing the XCOPY yourself, you didn't acknowledge the reason why XCOPY doesn't actually copy any files. It's not because of the /D switch (which relates only to the file dates) it's because of the /L switch. I'm surprised you never spotted that.

TechnoChat

Unfortunately, FORFILES is not common to all versions of DOS / Windows. It's fine if you're working with Windows 2003, Windows 2008, Windows Vista or Windows 7 but no good for Windows XP to name just one.

TechnoChat

Just a few thoughts....

1) Not sure why you're using 'DELIMS=/' in line 11 (http:#37751010).

2) You make no use of any of the following variables whatsoever:
    DD, MM, YYYY, dirdate, tm and ampm
even thought you define them in your code.

3) I don't see a need  for the COLOR and MODE commands in lines 2 and 3 other than to muck up the screen?

paultomasi, i used your script , it worked fine the first time....generated a file with name "TEST_FULL_22032012.dmp".  

then , as a test, i changed the file name to TEST_FULL_22022012.dmp , assuming it will be deleted , since it is dated over 28 days. I ran the batch/script file again , but the file did not get deleted. it created another file "TEST_FULL_22032012.dmp".

What did i miss?

thanks!

The delete script is based on the create time of the file not anthing to do with the name of the file.

gotcha! so there is no way completely testing this untill 28 days from now? the export part works fine and creates the expected dmp and log file.

There are utilities out there that will allow you to change the dates/times for the different file attributes.

http://www.simplehelp.net/2009/06/15/how-to-change-the-timestamps-of-any-windows-file/

I've never used any of them and cannot say if they are OK to use or not.

The only other alternative I can think of is find a folder with some old files in it and test it there.  TEMP is almost always good to find some old junk laying around.

That's a good idea!

I copied some dump files i have, which are dated from 12/21/2011 into that folder and ran the task again. It created a new backup file , but did not delete the any files dated in that folder which are older than 28 days.

more info-- i tried the above on a Windows 2008 server.

moments ago - i tried the same script on a Windows 2003 server , it completed a full export fine and at the end it also delete the backup file, log file that were just exported and the script/batch file itself. so that tells me that it is deleteing everything dated today? i.e. the same day as the job runs. To test , i also copied some files from January into that folder and it did not delete them.

Below is how i am using the script--
------------
@echo off

set day=%date:~7,2%
set month=%date:~4,2%
set year=%date:~10,4%

set datestring=%day%%month%%year%

@EXP SYSTEM/oracle@TEST32 FILE=D:\EXP\TEST_DB_FULL_%datestring%.dmp LOG=D:\EXP\TEST_DB_FULL_%datestring%.log GRANTS=N ROWS=Y INDEXES=Y TRIGGERS=Y FULL=Y

set /a month-=1

if %month% lss 1 (
  set month=12
  set /a year-=1
)

if %day% gtr 28 set day=28

for /f "tokens=*" %%a in ('xcopy "D:\EXP\*.*" "%temp%" /c /d:%month%-%day%-%year% /l /y ^| find /v "File(s)"') do (
  del /f /q "%%a"
)

>>but did not delete the any files dated in that folder which are older than 28 days.

If you copy a file, the 'new' copy get's the current date.  Remember it is the date the file is created.  The 'copy' was created at the time it was copied.

Say I am 100 years old and you clone me.  How old is my clone?

on C:\ drive there is a folder with files dated 01/21/2012 , i copied them to D:\ drive into the folder from which i am running the back-up. The files are still date 01/21/2012.

I thought that should do, i can test the script from C:\ drive folder where there are some old files.

Also, what about the script deleting itself and the files it just created?

i ran the script (on Wondows 2008 server) from a folder which contains files from 11/17/2011 ,  the back-up worked but it did not delete any files.

>>the back-up worked but it did not delete any files.

I've played with the script provided by paultomasi and cannot get it working.  I've reread the docs on xcopy and /d states "Copies source files changed on or after the specified date only"

I do not see how it grabs files on the specified date or before.

I'm not sure why you continue to discount the script in the first link I posted.  I just tried it and it works flawlessly.

Please give it a try.

I have not been able to respond. Apologies.

I see there are isues with the code I provided so please allow me to confirm ...

@paultomasi Sorry for the late reply,

Unfortunately, FORFILES is not common to all versions of DOS / Windows. It's fine if you're working with Windows 2003, Windows 2008, Windows Vista or Windows 7 but no good for Windows XP to name just one.

User need a script for Oracle 9i, so, it can be assume that it will be in server OS, or else it might be in client OS, that's why I mentioned the download link for the FORFILES. Even Windows 2000 have a FORFILES. FORFILES is really good for any kind of retention activity.

1) Not sure why you're using 'DELIMS=/' in line 11


2) You make no use of any of the following variables whatsoever:
    DD, MM, YYYY, dirdate, tm and ampm
even thought you define them in your code.

I also never tried to understand that part, as the script is handling the date and time variable quite fine, I used this script on one of our server for exact same requirement, and about those extra variables, I didn't remove, coz, it's quite helpful, you can add specific time stamp to the file name if required.

3) I don't see a need  for the COLOR and MODE commands in lines 2 and 3 other than to muck up the screen?

It's just a UI improvement while executing script. :)

PS: I don't have any intention to hijack this topic, I thought this might be helpful, so I commented. :)

There is a problem with the code in my program. The logic is reversed so I will amend the program and post it in my next comment.

Apologies.

Feel free but there is a tested working script already posted.  Not sure why the asker does not want to use it...

i am out on the road since couple of days, will try it most likely tomorrow and get back.

Thank you!

slightwv-  --- on 2012-03-21 at 21:31:26 -- i responded back to your script "I am not sure how to use the script posted." and did not see a reply from you.

I guess save that script as a batch file and execute it? and there are no changes required to the script? Let me know.

Thanks!

>>"I am not sure how to use the script posted." and did not see a reply from you.

Way back up top:  http:#a37750405

scriptname 28

Where <scriptname> is the name you saved the script to and <28> is the number of days old you want to delete.

>>I guess save that script as a batch file and execute it?

Yes.

>>and there are no changes required to the script? Let me know.

No changes.  Run it as-is

Thank you! and my apologies that i did not see your reply from 3/23.

I "think" i made the changes you mentioned , i am attaching the script file.
I acopied it into a folder where there are files from last year and executed the file, no result, what did i miss? I choose to delete files older than 40, tried 20 and 30 as well, no luck.
DELOLD40.txt

Does the ROBOCOPY command work on your system? This might provide a much shorter and neater solution to your problem.

The only change necessary is there.

I'm not sure what you missed.  I tested the script and it works.

What version of Windwos are you running?

i tested it on Windows 2008 , is it meant for 2003?
ultimately , i will be using it on Windows 2003 server.

It 'should' run on all windows.  I just need to test on my end on the same version.  When I tested it I was on Windows XP.

I'll test on 2003 and 2008 and let you know what I find.

It runs fine for me using 2008R2.

I cannot locate a 2003 test server at the moment.

Here are my steps:
open the link above.
ctrl-a to select all the text
run notepad
ctrl-v to paste the text
save to c:\mytest.bat
cd into a folder with old files
copy c:\mytest.bat .
mytest.bat 30

Since I didn't edit the file, it just echos the files but I see the list of files older than 30 days.

hmm...the only difference i see i s, i am saving the file as a .bat file , to a folder where there are older files and then double click on it. it flashes for a second and disappears. I put a pause at the end and attached is the screen shot of what it shows...
New-Microsoft-Office-Word-Docume.docx

That is the problem.  Do not double click on it.  See the usage?  You need to specify the number of days as a command line parameter.

Did you not read the syntax I gave you several times?

From a command prompt:  myscript.bat 30

Where myscript.bat is the name you saved it to and 30 is the number of days.

how will that work when you execute the script/batch file using Windows Task Scheduler?

You schedule a 'command'.  Just provide the parameter as part of the command.

The command you enter is:  myscript.bat 30

or however many days you want.

see attcahment....let me know whats wrong...it is not working using the task scheduler.

as you said , it works when ran from command line with a parameter.

stepping out for couple of hours, will check as asson as i get back , thnx!
Doc1.docx

You experts figure it out and please let me know.

Thanks!

>>let me know whats wrong...it is not working using the task scheduler.

Try adding the Start In value of: C:\Bkps

>>Nonsense! Hardcode the '30' into the batch file (it's a CONSTANT therefore, there's no need to pass it as a parameter)

Either hard code it as a constant or pass it in as a parameter, this is purely opinion.  If the requirements change to say 50 days, no need to touch the script (in some shops this is a configuration change issue) or change a simple parameter in a task.

>>I haven't tried that particular batch code myself however, I have seen many like it.

As bad as it may be, it works.  I have not seen anything you posted that does.  You have made great claims yet offered nothing that actually runs.

Try what I posted.  I think you will see that it does what it claims to do.

If you can offer up 'better' code that actually works, feel free.

Honestly, since this is going to run in Windows 2003 I would do this in PowerShell or VBScript and not even worry about BAT.

Also note that it you place the script in the same folder as the backups, it will eventually delete itself.

Place it in a safe location and set the "Start In" folder.

slightwv

>> "Also note that it you place the script in the same folder as the backups, it will eventually delete itself."

That script might but a well written one won't! If your filespec is *.* than you're gonna have to filter files against '%0'. Common sense isn't it?

Considering the askers lack of knowledge in this area and not fully knowing the complete requirements, better to cover all bases.

Again, if you can post 100% accurate, foolproof code that meets the complete requirements, please do.

I do not want to hijack this question with this.  If you wish to continue this, I will open a private discussion for us.

I will post code because I feel there is room for improvement...

Feel free.  Just make sure it is tested and remember that fewer lines does not imply better.  Personally I would add a second parameter folder to 'clean'.  Then the call would be: deleteold.bat c:\BKups 30

The asker just wants their question answered and code that works.  My first Expert post supplied that.  Everything after that has confused the question and the asker.

I can also post Powershell and VBScript code that has fewer lines and considered by many to be an improvement.  Doesn't mean there is benefit to the question asked.

Testing code...

>>I do hope slightwv is approving.

I would be if it worked....

I changed line 6 to C:\pics\
Line 19 to: for %%A in (*.jpg) do (
Line 21 to: if %%c%%a%%b lss %year%%month%%day% echo "%%~fA"

Here is the results:

C:\pics>dir
 Volume in drive C has no label.
 Volume Serial Number is 684A-0E1C

 Directory of C:\pics

03/28/2012  08:21 AM    <DIR>          .
03/28/2012  08:21 AM    <DIR>          ..
01/03/2007  03:52 AM         2,439,395 DSCN1449.jpg
01/03/2007  03:52 AM         2,608,287 DSCN1450.jpg
01/03/2007  03:53 AM         2,371,859 DSCN1451.jpg
01/03/2007  03:54 AM           597,185 DSCN1452.jpg
01/03/2007  03:54 AM           584,032 DSCN1453.jpg
01/03/2007  03:55 AM         2,316,925 DSCN1454.jpg
03/28/2012  08:21 AM               476 mytest.bat
               7 File(s)     10,918,159 bytes
               2 Dir(s)  49,660,542,976 bytes free


C:\pics>mytest.bat
Invalid parameter - /d:07-01-
Invalid parameter - /d:07-0-
Invalid parameter - /d:07--1-
Invalid parameter - /d:07--2-
Invalid parameter - /d:07--3-
Invalid parameter - /d:07--4-
Invalid parameter - /d:07--5-
Invalid parameter - /d:07--6-
Invalid parameter - /d:07--7-
Invalid parameter - /d:07--8-
Invalid parameter - /d:07--9-
Invalid parameter - /d:07--10-
Invalid parameter - /d:07--11-
Invalid parameter - /d:07--12-
Invalid parameter - /d:07--13-
Invalid parameter - /d:07--14-
Invalid parameter - /d:07--15-
Invalid parameter - /d:07--16-
Invalid parameter - /d:07--17-
Invalid parameter - /d:07--18-
Invalid parameter - /d:07--19-
Invalid parameter - /d:07--20-
Invalid parameter - /d:07--21-
Invalid parameter - /d:07--22-
Invalid parameter - /d:07--23-
Invalid parameter - /d:07--24-
Invalid parameter - /d:07--25-
Invalid parameter - /d:07--26-
Invalid parameter - /d:07--27-
^CTerminate batch job (Y/N)?

Select allOpen in new window

slightwv

The batch file was not written to run on your system. It was written for crishna1 - the asker!

If you care to look back at an earlier comment (http:#37750402) you will notice crishna1 confirms the output from 'ECHO %DATE%' is "Wed 03/21/2012". This indicates his DATE format is 'ddd MM/dd/yyyy'.

If you now take a look at your own system's DATE format you will notice it is different - probably something like 'MM/dd/yyyy'.

If you look at lines 2, 3 and 4 of the code, you will notice which elements of %DATE%, day, month and year, are set to.

Surely you did not expect the code to work correctly without first properly setting it up? That's why I stated "Please let me know if it needs tweaking" at the foot of my comment (http:#37776154).

You have not yet submitted any code written by yourself yet, you seem to be overy critical of my code.

I assure you my code works 100%. I wrote the code myself from the ground up. It is unique because I discovered the use of XCOPY as a way to validate dates myself.

>>The batch file was not written to run on your system. It was written for crishna1 - the asker!

I stand corrected.  After making adjustments for the date difference, it does work.

>>It is unique because I discovered

Not really unique.  I saw the XCOPY examples when I Googled for the link I posted.  When the question first came in, I knew someone had to have already written this script.  There are many examples out there using the xcopy trick.

>>You have not yet submitted any code written by yourself yet, you seem to be overy critical of my code.

Why rewrite what has already been writted and is all over the Internet?

Going from previous posts where you said "here, this will work" and it didn't.  You yourself stated there were problems with what you posted.

Experts, i really appreciate your input.

Given that, this thread went for a prolonged time with several iterations to the scripts, ofcourse the out come being a efficient script, can you please post the final script that i can use as a batch file via Windows Task Scheduler.

Thank you very much!

regards

Either of our scripts should do what you need.  There is no 'final'.  Pick the one that works best for you.

You can use the last one posted by paultomasi in http:#a37776154

or  try adding the startup folder to the script in the link I posted where you could manually run it and it worked.

paultomasi's script will require tweaking if you change from deleting a months worth to say a weeks worth.

The script I posted would be a scheduled task change (just change the parameter).

@paultomasi-- i used the last script you posted. It works from with teh TASK Scheduler, but it deletes all the .dmp and .log files in the folder , including the files it just exported.

seems like it is deleting any .dmp and .log files irrespective of the date.

i tried this on both 2008 and 2003 server.

Let me know what needs to be fixed.

thanks!

Did you try the start in folder to the script I posted?

I will test their script with a mix of new and old files tomorrow.  There were no new files in my previous test.

@slightwv,

you have posted several scripts and made changes to them. please point me to the script that will work and the one that you want me to use. The last time i tried it with "Start In" folder. Post me a screen shot if this is something different you are reffering to.

Thanks!

>>The last time i tried it with "Start In" folder

The image you posted in http:#a37773671 that shoes the task scheduler window shows the 'start in' field empty.
Are you saying that you had tryed adding the export folder and the script still didn't run?

slightwv,

i executed the batch file you provided using s"Scheduled Tasks" on a Windows 2003 server.
It deleted itself, which is not what i was looking for.

Attached is the screen shot from the task properties , you can see "Start in" folder added.

Also the parameter passed is 2, assuming that it will delete all files older than 2 days in that folder, but it did not. It just deleted itself (the batch file).
slightwv-err.docx

paultomasi , slightw  --- FYI: i will be around for another 15 minutes and will be back on Monday.

>>It just deleted itself (the batch file).
I commened on this back in http:#a37774633

Place the script in a different folder like D:\myscripts
The use the 'start in' to point to where it needs to delete files.

got it , thank you for the quick responce.

This means, i have two tasks to schedule , one to back up the database and the second to clean-up.

>>This means, i have two tasks to schedule

Can you not add the export command to the script that does the delete?

sure , but i wan the back-up's to run on a weekly basis and clean-up done on monthly basis.
having two tasks scheduled will not be a big deal.

>>but i wan the back-up's to run on a weekly basis and clean-up done on monthly basis. having two tasks scheduled will not be a big deal.

I missed that from the original question.  Two tasks is the way to go here.

Why the B grade and why were points awarded to http:#a37776154 when that code didn't work and wasn't used?