SQL Server Agent is a tool integrated into SQL Server that is primarily used for automating and scheduling tasks. Through SQL Agent, you can create Jobs and schedule individual tasks within those Jobs, such as executing scripts, running SSIS packages, using PowerShell, and much more. However, SQL Server Agent is only available in the Standard and higher editions of SQL Server, and it’s not included in SQL Server Express. In a previous article about SQL Server Command utility, we showed how to execute SQL scripts via the command line. In this article, we’ll demonstrate an alternative method using Windows Task Scheduler to define and schedule SQL tasks.

Task Scheduler as an Alternative to SQL Server Agent

As mentioned earlier, we’ll use the built-in Windows operating system tool called Task Scheduler as a replacement for SQL Agent. Task Scheduler allows you to create:

  • Tasks (similar to Jobs in SQL Server Agent)
  • Actions (similar to Steps in SQL Server Agent)
  • Triggers (similar to Schedules in SQL Server Agent)

The principle is straightforward, and we’ll compare it to SQL Server Agent and provide an example.

Creating SQL Server Agent Jobs and Steps

In SQL Server Agent, you create Jobs and place Steps (tasks) within each Job. You schedule the execution of a Job at a specific time (see images below).

SQL Server Agent - Job
SQL Server Agent - Detail Jednoho Stepu (úlohy)

How Windows Task Scheduler Works

For Tasks created in Windows Task Scheduler, the principle is similar, as shown in the image below, focusing on the details of Task 1 named “Test”:

  • Under the Actions tab, you can see the individual steps to be executed within the Task.
  • Under the Triggers tab, you can view the scheduled time for the Task to run.

Windows scheduler - Detail Tasku
Windows task scheduler - Trigger

Example – Replacing SQL Server Agent – Step by Step

Here, we’ll demonstrate one possible approach to achieving this. Let’s say we need to prepare two tasks. The first task is similar to the one discussed in the previous article about sqlcmd, and we’ll add another task. In other words:

  1. We create a table “Biportal_Step_1” and insert one record, “Hello World”.
  2. We also create a table “Biportal_Step_2” and insert five records, “Hello World 1”, “Hello World 2”, “Hello World 3”, “Hello World 4”, and “Hello World 5”.

Summarizing, we’ll execute two scripts via the task scheduler:

  • sqlcmd -S SQL_INSTANCE -Q “USE [Temp];CREATE TABLE Biportal_Step_1 ([ID] INT IDENTITY(1,1) PRIMARY KEY, [Text] VARCHAR(255));INSERT INTO Biportal_Step_1 ([Text]) VALUES(‘Hello world’);” (Step 1)
  • sqlcmd -S SQL_INSTANCE -Q “USE [Temp];CREATE TABLE Biportal_Step_1 ([ID] INT IDENTITY(1,1) PRIMARY KEY, [Text] VARCHAR(255));INSERT INTO [Temp].[dbo].[Biportal_Step_2] ([Text]) VALUES (‘Hello World 1’),(‘Hello World 2’),(‘Hello World 3’),(‘Hello World 4’),(‘Hello World 5’);” (Step 2)

Preparing a Folder for Batch Files

To execute tasks via the task scheduler, it’s best to create batch files. The process is simple:

  • Create a folder (e.g., “SQL Automation”).
  • Open Notepad and prepare batch files. Each .bat file represents one task. In our case, we’ll create two files.

Příprava batch souborů

Creating Batch Files and Placing Them in the Folder

In each batch file, insert your scripts in the following format (example for Step 1 – Create table Biportal_Step_1.bat):

Ukázka batch souboru

Explanation of the batch file: First, we start the command prompt (first “start:”). Then, we execute our script (second “start”), and finally, we terminate the session. Click “Next.”

Creating a Task in Task Scheduler

Once you have both files ready, open Task Scheduler and click “Create basic task.” Name your new task “Biportal_SQL_Automation.”

Create Task

Configure Task Scheduler Trigger and Start Time

Next, configure the Trigger, which determines how often the task should run. In our case, we have a one-time SQL task, but usually, you’ll want tasks to run repeatedly, e.g., daily. Choose “Daily” and click “Next.”

Task scheduler - Daily Trigger

In the next window, set the task’s start time and click “Next.”

Daily Trigger - nastavení času spuštění

Configure Action – Start a Program

In the following window, configure the action that Task Scheduler should perform. In our case, we want to start a program. Choose “Start a program” and click “Next.”

Windows task scheduler- Action

Set the Path to the Batch File

In the final step, set the path to your batch file (see 1) that should be executed and click “Next.”

Start program

Action Window – Task Created Successfully

A summary window will appear, and upon confirmation, your task is created. However, the task currently contains only one action (step). We need to set up the second step. To do this, right-click on your task, open it, switch to the “Actions” tab, click “New,” set the path to the second batch file, and confirm. Now, your task should have two actions. These actions will run at the time you specified in step 5.

Windows schedule Actions

Testing the Task

Now, let’s try running the task to see what happens. Right-click on the task and select “Run.” Check if everything is working correctly. It should be okay, and you have successfully replaced SQL Server Agent 😉

Výsledek příkladu windows task scheduler

Rate this post

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

Your email address will not be published. Required fields are marked *