• Creator
  • #2138621

    Batch file manually runs fine, does not using Task Schedulter


    by the weekly geek ·

    We have a customer who uses a ticketing software developed elsewhere. They provide their own backup which is a batch file. When you manually double-click on it, it runs fine. When I make it a scheduled task, the first part runs but not the second.
    Basically, it appears to run a local backup to the hard drive then copies that backup to a mapped drive on another computer. Under Scheduled tasks, it does not copy to the mapped drive.
    here is the text of the file

    “@echo off
    rem This database backup script integrates with Theatre Manager to support provide an automatic
    rem backup script of the PostgreSQL database. It also DELETES an backup files that are older than xx days
    rem if desired, you can specify a remote FTP site tosend the database to
    rem Alter this source code as you see fit to suit your needs

    rem Please note that this script assumes that it is being run off the server machine and that
    rem the server has ‘trust’ enabled for or localhost in the pg_hba.conf file for PostgreSQL.
    rem The program from
    rem is assumed to be in the BoxOffice folder, where-ever the user wants it. So if you change
    rem the backup path, then please move program into that folder.
    rem Based on the default settings, the standard location of the files are:
    rem C:\BoxOffice – the main BoxOffice data folder
    rem C:\BoxOffice\Backups – the location where the database backups will be saved
    rem C:\BoxOffice\BackupTM.bat – this file you are reading. It is the backup script.
    rem C:\BoxOffice\ – required to be in the BoxOffice folder
    rem To setup the Windows Scheduled Task to run this script every day:
    rem 1) In Windows, goto Start >> Settings >> Control Panel >> Scheduled Tasks
    rem 2) Double Click on Add Scheduled Task
    rem 3) Follow the prompts to run the program C:\BoxOffice\BackupTM.bat
    rem 4) note: you can set this to run multiple times in one day if you wish
    rem If you want to run this backup script outside of a regular scheduled backup:
    rem Option A) Via the windows command prompt
    rem 1) In Windows, goto Start >> Run…
    rem 2) Enter cmd
    rem 3) Click OK
    rem 4) Enter C:\BoxOffice\BackupTM.bat
    rem 5) Press or
    rem Option B) Via the created Windows Scheduled Task
    rem 1) In Windows, goto Start >> Settings >> Control Panel >> Scheduled Tasks
    rem 2) Right Mouse Click on the schedule task that you created
    rem 3) Select ‘Run’ from the pop-up context menu
    rem To edit the Windows Scheduled Task that you created:
    rem 1) In Windows, goto Start >> Settings >> Control Panel >> Scheduled Tasks
    rem 2) Right Mouse Click on the schedule task that you created
    rem 3) Select ‘Properties’ from the pop-up context menu
    rem ————————————————————————————————
    rem —— CUSTOMIZE SCRIPT VARIABLES HERE ———————————————————
    rem Step #1 – Change the ‘DATABASE_NAME’ to be the name of the database you need to backup
    rem Note: This setting is normally always changed. Set it to match exactly the PostgreSQL database name
    set DATABASE_NAME=Baldknobbers
    rem Step #2 – Change the ‘POSTGRESQL_PATH’ to be the location where PostgreSQL is installed
    rem Note: This setting is normally set as C:\Program Files\PostgreSQL\9.1
    rem even on 64 bit machines, this should be C:\Program Files\PostgreSQL\9.1
    set POSTGRESQL_PATH=C:\Program Files\PostgreSQL\9.1
    rem Step #3 – Change the ‘BOXOFFICE_DIR’to be the location of the main BoxOffice folder.
    rem Note: This setting is normally set as C:\BoxOffice
    set BOXOFFICE_DIR=C:\BoxOffice
    rem Step #4 – Change the ‘BACKUP_FOLDER’ to be the name of the folder where the backup files will be saved.
    rem Note: This setting is normally set as Backups
    set BACKUP_FOLDER=Backups
    rem Step #5 – Change the ‘KEEP_FILES_FOR’ to be the number of days that the backup files will be retained.
    rem Note: This setting is normally set as 30
    rem Set this to 0 if you want to keep the backup files forever
    set KEEP_FILES_FOR=30
    rem Step #6 Verify that the backup does a YYMMDD dump for the file name. The default is set for a windows
    rem installation that uses USA date time format. If you are not using USA format, but are using a more
    rem metric date, you may wish to see some alternate suggestions for determining the date. Look for the
    rem ‘datestr’ and ‘sqltime’ variables.
    rem Step #7 Verify the parameter for the dir /-C /N command below to get the right file size parameter
    rem you may need to change it from /tokens=4 to /tokens=3
    rem Step #8 indicate if you want to send information to an FTP site afterward by entering the following parameters
    rem Note: if the server is left blank, then no FTP will occur
    rem and make absolutely sur ethat there is no spaces after the text on the commands
    rem if you supply a remote folder, it must start and end with ‘/’. The script does not use CD to
    rem change the working directory.
    rem Note: FOR PCI COMPLIANCE, you will need to ensure that the data files are sent via a VPN
    rem or secure tunnel if the data file is to leave your internal lan. You will also need to ensure
    rem that the offsite storage location and/or vendor is included in your PCI checklist and adheres
    rem to PCI standards.
    set FTPSERVER=
    rem set FTPSERVER=yourserver
    set FTPUsername=yourusername
    set FTPPassword=yourpassword
    rem set FTPFolder=/directory/
    set FTPFolder=
    rem —— DO NOT MAKE CHANGES BELOW HERE ———————————————————-
    set hours=%Time:~0,1%
    if (“%hours%”) == (” “) set hours=0
    rem the format of the date commands below are dependant on the windows setting for dates. This seems to work
    rem for dates and times formated slightly differently that the standard USA setting
    rem pick one or the other. The default is the second set for USA. rem those out if you need the first setting.
    set datestr=%Date:~-4,4%%Date:~-7,2%%Date:~-10,2%_%hours%%Time:~1,1%%Time:~3,2%%Time:~6,2%
    set sqltime=%Date:~-4,4%-%Date:~-7,2%-%Date:~-10,2% %hours%%Time:~1,1%:%Time:~3,2%:%Time:~6,2%
    rem the format of the date commands below are dependant on the windows setting for dates. This assumes standard USA setting
    set datestr=%Date:~-4,4%%Date:~-10,2%%Date:~-7,2%_%hours%%Time:~1,1%%Time:~3,2%%Time:~6,2%
    set sqltime=%Date:~-4,4%-%Date:~-10,2%-%Date:~-7,2% %hours%%Time:~1,1%:%Time:~3,2%:%Time:~6,2%
    rem parameters are:
    rem -U POSTGRES
    rem -o include OID’s in the dump unique rowID’s
    rem -v verbose. show the files as they are being done (optional)
    rem -F c dump in custom Postgres format.
    rem -b include blobs in the dump. These are things like theatre maps
    rem -Z 9 compression level for the dump. Do the most compression
    rem -U postgres runs the command under the postgres user id
    “%POSTGRESQL_PATH%\bin\pg_dump.exe” -U postgres -v -F c -Z 9 %DATABASE_NAME% > “%BACKUP_PATH%\%DATABASE_NAME%_TM_%datestr%.backup”
    echo Backup completed. Compressed database is located at %BACKUP_PATH%\%DATABASE_NAME%_TM_%datestr%.backup
    rem get the filesize of the backup file. The Dir Command syntax shows the file size in parameter 4.
    rem the actual parameters depends if your time is set to xx:xx AM/PM or xx:xxa .If it returns the first, parm = 4, second, parm=3
    rem If thats not what happens, change ‘4’ to the right parameter *** It may be 3 ****
    dir /-C /N “%BACKUP_PATH%\%DATABASE_NAME%_TM_%datestr%.backup” | find “%DATABASE_NAME%_TM_%datestr%” > temp.txt
    for /F “tokens=4 delims= ” %%i in (temp.txt) do set FileSize=%%i
    rem file size cannot be null, so set it to zero. It will generate a ‘not working right’ message in TM.
    if (%FileSize%) == (“”) set FileSize=0
    rem update the last time the backup script got this far along in the database for warning purposes
    echo Setting last backup time in %DATABASE_NAME% to %datestr% and filesize to %FileSize%
    set updatecmd=”update fSystemPreferences set SPR_PRIOR_BACKUP_SIZE = SPR_LAST_BACKUP_SIZE,SPR_LAST_BACKUP_SIZE =%FileSize%,SPR_DATE_LAST_BACKUP_SCRIPT_RUN = TIMESTAMP ‘%sqltime%’;”
    “%POSTGRESQL_PATH%\bin\psql.exe” -U postgres -d %DATABASE_NAME% -c %updatecmd%
    rem *** copy database to the Z: drive
    copy %BACKUP_PATH%\%DATABASE_NAME%_TM_%datestr%.backup Z:
    rem vaccum the database for performance
    echo Vacuum analyse the database %DATABASE_NAME%
    “%POSTGRESQL_PATH%\bin\psql.exe” -U postgres -d %DATABASE_NAME% -c “vacuum analyse;”

    rem —– now delete the old files using delage32 ————————–
    rem delage32 is written by horst schaeffer at
    rem refer to documentation at to see the parameters of this command
    rem format is generally:
    rem DelAge32 filespec days [options]
    rem key options are:
    rem /preview – show the files htat would be deleted, but don’t delete them
    rem /modified -use the modified time stamp for comparison (default option)
    rem /created – use the date created time stamp (warning… copying a file gives a new created date and retains the modified date)
    rem /includeRO – include read only files in deletion
    rem /includeH – include hidden files in deletion
    rem /includeS – include syst files in deletion
    rem /includeRHO – include hisyst files in deletion

    if “%KEEP_FILES_FOR%” == “0” (
    echo Purging of backup files is disabled.
    ) ELSE (
    echo Purging all backups files older than %KEEP_FILES_FOR% days
    “%BOXOFFICE_DIR%\delage32” “%BACKUP_PATH%\%DATABASE_NAME%_TM_*.*” %KEEP_FILES_FOR% /includeRO /includeH
    “%BOXOFFICE_DIR%\delage32” “Z:\%DATABASE_NAME%_TM_*.*” %KEEP_FILES_FOR% /includeRO /includeH

    rem if the FTPSERVER is filled in, then FTP the data up to the remote server after the backup is done
    if (“%FTPSERVER%”) == (“”) goto end
    rem ftp the file to the appropriate place
    echo FTP file to %FTPSERVER% – sending %BACKUP_PATH%\%DATABASE_NAME%_TM_%datestr%.backup
    set FTPscript=%BACKUP_PATH%\ftpscript.txt
    echo %FTPUsername%>%FTPscript%
    echo %FTPpassword%>>%FTPscript%
    echo type binary>>%FTPscript%
    echo put “%BACKUP_PATH%\%DATABASE_NAME%_TM_%datestr%.backup” “%FTPFolder%%DATABASE_NAME%_TM_%datestr%.backup”>>%FTPscript%
    echo quit>>%FTPscript%
    ftp -s:%FTPscript% %FTPSERVER%
    del “%BACKUP_PATH%\ftpscript.txt”
    Set FTPUsername=***
    Set FTPpassword=***


All Answers

  • Author
    • #2887986


      by the weekly geek ·

      In reply to Batch file manually runs fine, does not using Task Schedulter


    • #2887984

      REM is a great band

      by robo_dev ·

      In reply to Batch file manually runs fine, does not using Task Schedulter

      First of all, why is it setting the FTP name/password variables AFTER running the copy?

      “In order to use this script please setup all the variables to match your servers setup”

      thus you need to either explicitly set the variables in the batch file (or just remove the variables and put the actual values in)

      The SET command puts those variables in memory and I bet the batch file fails the first time you try it, then works?? (because those variables would stay in memory until machine rebooted).

      Ditto for your ftpscript ftpserver variables.

      The (incorrect) batch file above will not work because environmental variables set in a CMD window are not persistent.

      “added code to vacuum analyse the database” 🙂

      • #2887890

        Reponse To Answer

        by the weekly geek ·

        In reply to REM is a great band

        I apologize for the delay in getting back to you.
        This batch is correct, it runs manually every time. You can run it via the command prompt or by double-clicking on it.
        Yes, there is a lot of REM, I did not write it but the ticket company apparently wanted to be clear (though too much REM makes it unclear).
        So we are back to the issue, that it does run just fine if you manually run it but when you schedule it as a task in “Task Scheduler” (this is on Server 2008 R2 BTW) it runs partially.
        I removed some of the overhead REM but left the notes to help the reader.

    • #2887886

      Try having the scheduled task run as the user

      by slayer_ ·

      In reply to Batch file manually runs fine, does not using Task Schedulter

      I think by default it runs as the system user, maybe running as the local user would work better?

      Also, I can’t tell by your script, but make sure var’s like backuppath don’t assume a starting directory.

      • #2433159

        Reponse To Answer

        by rhulick ·

        In reply to Try having the scheduled task run as the user

        I also experience certain machines where the system “SET” variables %homedrive% & %homepath% do not populate when running a batch file from the Windows Task Scheduler, though they DO populate when running the actual batch file. If I type “SET” in the cmd window the vars are all there.
        If I use %USERPROFILE% instead, it WILL work, (except on machines where the user profile name has been changed and the path doesn’t reflect the username, which is why I need to use “%homedrive%” & “%homepath%”)

        I asssume that depending on the OS ver, and the method that the windows scheduler uses to run a file, perhaps somethimes the system itself doesn’t have a %homedrive% or %homepath%. In your case, one of the env variables must not be populated when running in the task scheduler. I don’t know enough about user and system variables and the way task scheduler functions internally to know what the rules are on this, but hopefully you have already found your solution.

    • #2887875

      The core issue, I believe, is environment variables are not being SET

      by robo_dev ·

      In reply to Batch file manually runs fine, does not using Task Schedulter

      I bet if you goto a cmd prompt on the box and type SET, you see the variables there….

      Do this:
      – Add a pause in the batch file somewhere before the actual copy part so when the scheduler kicks it off, the pause will give you ‘press any key to continue’

      – hit ctrl-C to terminate the batch job

      – Type SET

      I betcha a case of Molson your variables are not there.

    • #2887864

      Another possibility

      by gsg ·

      In reply to Batch file manually runs fine, does not using Task Schedulter

      I schedule a lot of batch jobs that copy data between servers. My AD account is an admin on both servers, so when I run the batch manually, it works fine, but when I schedule it, the batch file fails, which is pretty much what you described.

      The scheduler was running the bat file as the local account on that server, which did not have rights to the other server. I had an account created in AD, set to never expire, with a note that I’m responsible for that account and used as tge username and password when I set the schedule. I gave read/write on the share where the files were originating and read/write for that account to the receiving share and everything worked fine after that.

      Do go ahead and do what robo_dev suggests and put a pause at several spots in the bat file then schedule it to run a few minutes later. That way, if there are any error messages, you’ll be able to see them and track down the problem a little easier.

    • #2427148

      Did you verified permissions?

      by alexsts ·

      In reply to Batch file manually runs fine, does not using Task Schedulter

      Since you are on Win 2008 server it is most likely that you have batch process running under user name who does not have proper permissions to access (delete) files in that specific folder.
      I would suggest that you create specific user and assign for this user permission to access any involved folders /sub-folders (full access) and run batch files on the server.

Viewing 5 reply threads