How to create a Maintenance Plan in SQL server ?

Following are the steps to create the maintenance plan in SQL server.

Step 1

Go To Management -- > Maintenance Plan --> Right Click -->Select New Maintenance Plan as shown below.

Step 2

Provide the Name of the maintenance plan as given below.

Step 3

The toolbox  will appear automatically and from the toolbox select the Backup Database Task as shown below.

Step 4

After selecting the Task, just drag and drop it as shown below.

Step 5

The following backup database task window will appear once you double clicked the back up task.

Step 6

You can choose the required database and you can ignore the database if the database is offline by choosing the below checkbox as shown below.

Step 7

You can choose the path for the backup file to store and choose the back compression as well.

Step 8

You can generate the T-SQL script using the following T-SQL option as shown below.

Step 9

You can schedule the job by clicking the following button as shown below.

Step 10

You get to see the Job schedule properties and here you can give the job name for easy identification and select the schedule type as well.

Enabled option to be checked always.

Step 11

You can give the occurrence of the job such as Daily,Weekly and Monthly as shown below.

Choose the occurrence time and duration as well.

Step 12

Once you done with your changes click OK button.

Step 13

Save the maintenance job as shown below.

Step 14

You get to see the created plan under the management option and also the job under the jobs folder as shown below.

.

Step 15

You can start the job by selecting the Start Job at Step as shown below.

Finally we have created the Maintenance Plan successfully.