Create a New SQL Server Maintenance Plan with the Maintenance Plan Designer

Problem

Maintenance Plan Designer can be very helpful as it possesses more features for designing a maintenance plan as compared to the Wizard.

Solution

In this article, we will explore the Maintenance Plan Designer. The advantages of the Maintenance Plan Designer over the Maintenance Plan Wizard will be discussed and a sample maintenance task will be created by using the Designer.

Introducing the SQL Server Maintenance Plan Designer

To start the Maintenance Plan Designer, we can perform a right-click on "Maintenance Plans" under "Management" in SQL Server Management Studio and choose "New Maintenance Plan…":

Then, we should type the name of a new maintenance plan. In the example below, we have typed "MaintenancePlan2" as the name of our new maintenance plan:

After clicking "OK", the Maintenance Plan Designer will open:

SQL Server Maintenance Plan Designer Toolbox

On the left side, we can see the Toolbox, where maintenance plan tasks are included. In case the Toolbox does not show, it can be displayed from "View" > "Toolbox" menu or by using the shortcut "Ctrl+Alt+X":

It is also possible to choose a specific task from the Toolbox by right-clicking on the Toolbox and choosing "Choose Items":

Then, some tasks, which are not necessary during a specific project, can be excluded by unchecking them from the list:

In this case, after clicking "OK", we will have only two tasks in the Toolbox:

To bring the Toolbox to its default state, select "Reset Toolbox":

SQL Server Maintenance Plan Designer Example

On the right, we can see the buttons for managing subplans, connections and so on. Subplans allow us to schedule maintenance plan tasks, unlike with the Maintenance Plan Wizard, where each task is automatically assigned to its own subplan. With the Maintenance Plan Designer, it is possible to create more than one subplan and each subplan can be assigned to more than one task. On the right side, under the subplans, is an area for designing maintenance tasks, which is called the Design Surface. To design a task, we can just drag and drop a task from the Toolbox to the Design Surface. In this article, like in the previous one, let's drag and drop "Check Database Integrity Task" from the Toolbox to the Design Surface:

This task runs a DBCC CHECKDB command for all objects in the specified database(s) in order to perform physical and logical integrity checking. We can see from the picture that the "No connection manager is specified". To specify a connection, we should double-click on the task and select the appropriate connection. In our example, we have chosen only one database for integrity checking:

After clicking "OK", a new window appears where we can choose some options for checking database integrity:

Let's click "OK" and continue. We can change the name of the task by right-clicking on it and choosing "Rename":

After changing the name of the task to "Check ABC_TestDB Database Integrity" we will set the schedule for the plan by clicking on the "Schedule" under the "Subplan" and choose or setup the preferred schedule:

Now, we will click on the "Save" button in SSMS and the plan is ready. It can be found and executed in SSMS under "Management" > "Maintenance Plans"

After clicking "Execute", we can see the progress and that the plan successfully completed.

Conclusion

In conclusion, the Maintenance Plan Designer is a useful tool for designing and developing maintenance plans. While the Maintenance Plan Wizard has some limitations in designing maintenance plans, the Maintenance Plan Designer adds much more flexibility to this process and allows you to design complicated maintenance solutions.