Wednesday 21 July 2010

Debugging Windows batch files when used as scheduled tasks

Here’s a little gem that I pieced together thanks to Google this morning: how to trouble-shoot a Windows bat file when using it as a scheduled task.

We’re using SQL Express, and we want to make sure all our databases are safely backed up on a regular schedule. One thing that Microsoft cut out of SQL Server when pruning it to create the free version is SQL Agent, the tool that enables you to run scheduled tasks against the database.

No big deal: following Greg Robidoux’s advice I created a stored procedure to backup a database, and then wrote a batch file that used SQLCMD to execute it for each database on the server. Add to the batch file a call to RoboCopy to transfer the backups to our NAS drive, then set up a scheduled task against the batch file, and I’m done, I thought.

If only!

The first problem was how to get the task to run under the Local System account – I didn’t want to use a standard account, because then I have the hassle of password management (I’m using Windows Server 2003 here – if I was on Windows Server 2008 R2 I could use Managed Service Accounts and have the server take care of the passwords). Going through the Add Scheduled Task UI doesn’t give you the option of using the Local System account.

For that, I discovered, you need to use the at command:

at 20:00 /every:m,t,w,th,f,sa,su "D:\Backups\DoBackups.bat"

does the job, scheduling my batch file for 8:00pm every day of the week.

OK. Scheduled task appears in the list. Run it to check that it works. Uh oh!

The task clearly failed to complete, but how was I to find out why? Clicking Advanced > View Log in the scheduled tasks window brings up the log file – the completely useless log file that tells you that your task started and then stopped straight away “with an error code of (2)”. Right – could you be more … specific?

So I pushed further into the murky world of bat file programming.

Joshua Curtis saved the day. His post on Redirecting Output to a File in Windows Batch Scripts gave me exactly what I needed.

First, I refactored my batch script into DoBackupsCore.bat. Then, in DoBackups.bat I wrote this:

echo [%date% - %time%] Log start > D:\Backups\log.txt
CALL "D:\Backups\DoBackupsCore.bat" >> D:\Backups\log.txt 2>&1

On the first line the > command redirects output to the log file, erasing anything already in it. In the 2nd line, the >> command redirects the output of my actual backup script to the log file, but appends to what’s already there. The really clever part of this magic spell is the last 4 characters: "2>&1”. I refer you to Joshua for the details, but this basically makes sure that errors are logged to the file, as well as successful outcomes

So I got what I needed: lovely, wordy error messages enabling me to fix my script and go home with that lovely backed-up feeling.


Vincent said...

Helped us out a lot. Thanks

Joe said...

So helpful, thanks!

Atiqur Rahman Arman said...

Pro v6.0 (RoboCopy Alternative) with Open File Agent

GSCopyPro is a single command-line
tool (CLI) that can copy, replicate and move files from one folder to another.
This folder can be on the same machine/ server or another server elsewhere.
What makes GSCopyPro stand out from other competitors is the
fact it works on 32-bit as well as 64-bit systems and has no restrictions. It can easily be scheduled to run as a scheduled task
and fully automated. GSCopyPro also comes with an open file agent which can
copy files that are locked/ opened by other processes. This feature is
supported in all windows vSCersions from widows XP/ 2003 and later.

Go To:>>

Rahul Gupta said...

I have scheduled a simple batch file that copies files using the task scheduler. When the task is executed a command window flashes for a fraction of sec but the batch file seems not to be executed. However, task log shows executed successfully.
The batch file is working fine when executed separately. Any ideas?

Dave said...

You might consider putting a "pause" inline at the end and run it interactively to check to see what's up.

Post a Comment