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.