Every database person might have came across the situation of maintenance tasks such as backing up of databases, re-indexing tables and other such tasks. We often schedule jobs for such tasks, so that they execute as per the scheduled set. But in the case where the job has to be executed "On demand" then it needs human interference and the person should obviously be a technical person.

This article shows a good way of executing such maintenance tasks by any user regardless of whether the person is a technical or not. Let's say I have an SQL job "BACKUPTEST" that does the work of backing up a database. I wanted to execute that job "On Demand". Whenever anyone wants to run the backup, they should not necessarily have knowledge of SQL Server. I wanted to make a batch file out of that, so that any user can execute the job easily with a single click.

There are various solutions of the problem stated above. One is write a VBS script and using DMO the job can be executed. The one that I have found to be a better solution is to execute the osql command in a batch file. The osql utility allows you to enter Transact-SQL statements, system procedures, and script files. This utility uses ODBC to communicate with the server.

The first step would be try executing the job through command prompt using 'osql'. Open the command prompt and execute the following osql command in it:

osql -S "SERVER INSTANCE" -E -Q"exec msdb.dbo.sp_start_job ' BACKUPTEST ' "

Here "SERVER INSTANCE" is the name of the SQL Server instance running on your machine and on which the job has to be executed. "BACKUPTEST" is the name of the job which will do the desired maintenance task. After executing the command above you will notice that the system stored procedure 'sp_start_job' executes the desired job on the desired server instance.

The next step is to make a batch file. Open a notepad and type the commands as:

Save the file as "job.bat".

The batch is now ready for use. Just double click on it and it will do the maintenance work without having any knowledge of SQL Server.