How to work with Maintenance Plan Wizard in SQL Server

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.

01_MaintenancePlanWizard

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

02_PlanProperties

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.

03_JobSchedule

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.

04_MaintenanceTask

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.

06_DefineMaintenanceCleanUpTask

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

07_DefineDBTask

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.

CLICK HERE to watch live practical.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

You may also like...

4 Responses

  1. mahidhar says:

    Hi your video is very use full. i request you could you please proved real time issues. that type of issues are very use full to us.

  2. Dyson says:

    On the Define Maintenance Cleanup Task page, specify the types of files to delete as part of the maintenance plan, including text reports created by maintenance plans and database backup files. This task does not automatically delete files in the subfolders of the specified directory.

  3. Antehunegne Dejene says:

    I have satisfy a lot…… a lot……. a lot…..10Q.

Leave a Reply

Your email address will not be published.