Creating Maintenance Plan with Maintenance Plan Wizard in SQL Server 2005

In this post, we will discuss how to create Maintenance plan in SQL Server 2005. The environment is having SQL Server 2005 SP3 installed. Before creating the maintenance plan in the lab environment, let’s see our requirement.

Maintenance Plan Details

Name of the Maintenance Plan: Backup All User Databases

Maintenance Plan Details: This plan will backup all the user databases present in the current instance. It will clean up any old backup files older than 24 hours (1 day).

Let’s see the steps to create this maintenance plan.

Steps to be Followed

i. Right click on the Maintenance Plans (Management –> Maintenance Plans) to select Maintenance Plan Wizard.


ii. Once you select “Maintenance Plan Wizard”, you will get the below screen. Click on “Next” to continue.


iii.  Enter the Name of the Maintenance plan along with the Description. Click on the “Change” button to create a schedule for the maintenance plan.


iv. Enter the following details in your Job Schedule Properties like Schedule Type, Occurs (Frequency), Time of Occurrence. Once you furnish the required details, click on the “OK” button to create the job schedule.


v. The following screen will be displayed. Now the schedule is attached with our maintenance plan. Click “Next” to proceed.


vi. Select the Maintenance Tasks from the available list. In this case, we will choose two maintenance tasks: Back Up Database (Full) & Maintenance Cleanup Task. Click on Next to continue.


vii. For this plan, we need to clean up the old backup files first before creating the new backup. Select “Back Up Database (Full)” and click on the “Move Down” button. Click Next to continue.


viii. Define the Maintenance Cleanup Task. For this task, we will define the following setting as shown below (File type need to be deleted, Location of the folder where the files are stored, the age of the files to be deleted), click on Next to continue.


ix. In the Back Up Database (Full) task , configure the following high-lighted options (Type of databases, Backup Locations, Backup file extension). Click on Next to continue.


x. Enter the location where the Job execution report will be stored. Click Next to continue.


xi. Click on Finish to complete the wizard.


xii. Once you click on Finish, the maintenance job will be created.


The Maintenance plan for “Backup User Databases” has been created successfully. Once the plan is created, you can view the plan below the “Maintenance Plan” in SSMS.


Similarly, we will create a Maintenance plan to create a backup of all System Databases. After the creation of the plan, we will find the same under the Maintenance Plan in SQL Server Management Studio.


Hope, this will help to create Maintenance plan in SQL Server 2005 ….


About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Automation in SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s