Automation of Database Maintenance in SQL Server 2019 Express Edition

This article explains how to automate the database maintenance tasks in SQL Server Express Edition. In terms of automation of database maintenance schedules, the SQL Server Express Edition has the following limits:

1. The SQL Server Agent is not supported, so we cannot automate the database maintenance tasks using SQL Server.
2. The SQL Server database mail is not supported, so we cannot send notifications or alerts.

When we install the SQL Server Express edition and connect to SQL Server Express Edition, you cannot see the SQL Server Agent node in the SQL Server Management Studio.

The Windows Task Scheduler is a tool that can automate the various maintenance tasks and run the specific application and commands. We can schedule the various tasks to run them on a specific date and time.

We can use any of the following methods to automate the maintenance of the database.

Method 1: Create an SSIS package with database maintenance tasks and create a batch file that executes a package using the dtexec utility.

Method 2: Create a stored procedure in a database and create a batch file that executes the stored procedure using the SQLCMD command.

In this article, we will learn how we can automate the SSIS package using Windows Task Scheduler.

Creating SSIS Package to Perform Database Maintenance

I have installed SQL Server 2019 Express Edition and created a named instance on it. You can read the article to learn the step-by-step process to install the SQL Server 2019 Express Edition.

We are using the SQL Server data tools to create the integration services package. The SQL Server data tools have basic database maintenance tasks that we can use to perform database maintenance. These tasks are simple and easy to use. First, create a project named DatabaseMaintenanceSolution. The project will contain multiple packages.

I am going to show the database consistency check task; therefore, drag and drop the Check Database Integrity Task in the control flow designer. The Check Database Integrity Task in the SSIS toolbox menu. Rename the task as Run Database integrity test.

Double-click the task to configure it. Another dialog box opens. Click New to create a connection to the Nisarg-PC\SQLEXPRESS.

In the Connection Properties dialog box,

1. Enter the desired connection name.
2. Enter the host name on which the SQL Server has been installed in the server name textbox. If you use the named instance, the server name is in <ServerName>\<InstanceName> format.
3. Select the authentication details to connect to the SQL Server. We are using Windows Authentication, so I have selected it.

Click OK to save the connection and close the dialog box. On the Run Database integrity test dialog box, the Databases drop-down is enabled. We can select the databases where we want to run the integrity check. We get four options:

1. All Databases: Run the consistency check on user and system databases. It is the preferred option.
2. System Databases: Run consistency check on system databases.
3. User Databases: Run consistency check on user databases. This option excludes master, msdb, and model databases.
4. Specific database: Run the consistency checks on the databases that we have selected from the list.

I have selected the All Databases option.

Click OK to save the selections and close the dialog box. Close the Run Database integrity test dialog box.

Now, we should send the outcome of the SSIS package to users. We can do it using any of the following methods.

1. Use the Notify operator task.
2. Use T-SQL Script in the execute T-SQL task.

The Notify operator task is easy to configure, but it does not provide the flexibility to customize the email body; therefore, I am using the T-SQL script task. The outcome of the package execution is success or failure. So, I have added two Execute T-SQL tasks from SSIS Toolbox to the control flow designer. Rename the tasks as follows:

1. DBCC CheckDB completed: This task will be executed when the database consistency check completes without errors.
2. DBCC CheckDB completed with errors: This task will be executed when the database consistency check completes with an error.

Let us configure the execute T-SQL Tasks.

Configuring T-SQL Task

Double-click the execute T-SQL tasks; in the Connection dialog box, specify the connection details of the SQL Server where you want to execute the script. We have configured the connection named SQLExpressConnection, so select it from the drop-down box.

Enter the following code in DBCC CheckDB completed.

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'SQLServer Express Edition',  
    @recipients = '[email protected]',  
    @body = 'The Consistancy check completed without error.',  
    @subject = 'No consistancy errors in the database.' ;

Screenshot of DBCC CheckDB Completed Task

Enter the following code in DBCC CheckDB completed with errors.

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'SQLServer Express Edition',  
    @recipients = '[email protected]',  
    @body = 'The Consistency check completed with error.',  
    @subject = 'Consistency errors found in the databases.' ;

Screenshot of DBCC CheckDB Completed Task

The integration service package looks like the following image.

To test the package, click the Start button from the menu bar of SQL Server data tools.

I have restored a sample database that is corrupted. So once the package execution completes, you will receive an email as shown below.

We can run the SSIS package using the dtexec command. In this demo, we are using the following command to execute the SSIS package.

C:\Users\Nisarg>dtexec /FILE "\"C:\Users\Nisarg\source\repos\DatabaseMaintenanceSolution\DatabaseMaintenanceSolution\Package.dtsx\""  /CHECKPOINTING OFF  /REPORTING E

To automate, we will run a batch file. To create it, open the text editor and paste the above command, and save the file with the appropriate name. The file extension must be *.bat. In our demo, the filename is consistency_check.bat.

Now, let us see how we can automate the execution of the SSIS package.

Automating the Package Using Windows Task Scheduler

Open the Windows Task scheduler ? Right-click the Task Scheduler Library and select Create Basic Task.

Specify the appropriate name and description of the maintenance task.

On the next screen, select the Daily option.

Select the date and time to enable the task from the Start: textbox. Enter the desired occurrence in Recur every: textbox.

Select the task that you want to trigger at the specified time. You can perform any of the following actions:

1. Start a program.
2. Send an email
3. Display a message.

In this demo, we are running a batch file, so select the Start a program option.

Enter the path of the batch/program file. If your script has input parameters, then specify them in Add argument. In this demo, the location of the batch file is ‘C:\DatabaseScript\Consistancy_Check.bat’, so enter it in the Program/Script textbox.

On the next screen, you can see the details of the task and schedule. Click Finish to create the task.

When the task is created successfully, you can see it in the Task Scheduler Library.

Summary

This article taught us to automate the database maintenance tasks in SQL Server Express Edition using Windows Task Scheduler. We have created an integration services package that has Check Database Integrity Task, which is used to check the integrity of the databases and schedule its execution. In the next article, we will learn how to run a stored procedure using Windows Task Scheduler.

Last modified: December 15, 2022