How to work with Maintenance Plan Wizard in SQL Server

by Manzoor Siddiqui · Published July 31, 2015 · Updated September 24, 2015

439views

SQL Server maintenance plan wizard is inbuilt feature which helps in creating automated maintenance jobs for database integrity checks, rebuild and reorganize indexes, update statistics, backup databases and performs maintenance cleanup tasks etc. These maintenance tasks helps in keeping database health proper and functioning. You can create maintenance plan for single server or multiserver and can schedule and run jobs on regular basis. Following are the maintenance plan wizard tasks which you can create and perform.

-> Check Database Integrity
-> Shrink Database
-> Reorganize Index
-> Rebuild Index
-> Update Statistics
-> Clean Up History
-> Execute SQL Server Agent Job
-> Back Up Database (Full)
-> Back Up Database (Differential)
-> Back Up Database (Transactional Log)
-> Maintenance Cleanup Task

Here we will see step by step process for maintenance cleanup task and system database backups. Once you will understand this process then you will be able to create other remaining tasks in the list also.

Basically this is four step process.

1. Create Maintenance Plan

2. Define Maintenance Cleanup Task

3. Define Back Up Database (Full) Task

4. Select Reporting Options

To start maintenance plan wizard go to Object Explorer –> connect to database instance –> got to Management –> right click on Maintenance Plans –> select Maintenance Plan Wizard.

It will pop-up SQL Server Maintenance Plan Wizard as shown below.

Click Next button to start creating task. You have to select plan properties, specify plan name, description and schedule as shown below.

Click on Change button to create the job schedule for the process. It will open new job schedule window where you have to set job frequency and timings and click OK.

Click on Next button of Plan Properties.Now you have to select maintenance task, I have selected for full database backup and maintenance cleanup task then click Next button as shown below.

In next window, you can select the sequence of task for execution then click Next button, it will show maintenance cleanup task. You have to mention folder and file extension to delete based on age criteria then click Next.

Here, it will open window to mention databases to backup, path for backup and file extension then click Next.

If you want to keep record for every time job executes then you can select the option to write a report and mention path for the same and click Next button to continue with complete wizard.