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.

 image

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

image

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.

image

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.

image

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

image

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.

image

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.

image

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.

image

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.

image

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

image

xi. Click on Finish to complete the wizard.

image

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

image

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.

image

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.

image

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

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s