What is a maintenance plan and How to create a maintenance plan using SQL Server management studio?

Problem: What is a maintenance plan and How to create a maintenance plan using SQL Server management studio?

Solution: Create a Maintenance Plan

Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly backed up, and free of inconsistencies. The Maintenance Plan Wizard also creates core maintenance plans, but creating plans manually gives you much more flexibility.

Maintenance plan can be used for the following options on a database.

·         Checking Database Integrity.

·         Perform Index Maintenance.

·         Update database Statistics.

·         Perform database backups.

·         Compressing files.

A maintenance plan can have sub plans and can have different schedules based on the sub plan

Example 1:

In this example, I would like to show how to create a maintenance plan of a specific database or all databases full database backup, differential database backup and transactional database backup.

1    1.   Open the maintenance plan wizard by clicking on the maintenance plans folder on SSMS.

2.     Create a maintenance plan and name it with a better naming convention.

3.     Select the maintenance task you would like to perform.

4.     Select the order of the task to perform.

5.     Configure Full database backup selection of databases Schedule and backup location.

      A.  Selection of databases.

S    

     A.  Selection of databases backup for destination.

     C.  Selection of schedules to perform the recurring backup.

6.     Configure Differential database backup selection of databases Schedule and backup location.

7.     Configure Transactional database backup selection of databases Schedule and backup location.

8.     Logging and Alerts.

9.     Finish Screen.

10.  Viewing the created maintenance plan and SQL Server Agent jobs to see everything side-by-side.

To look for the progress and history of the maintenance you may go through the SQL Server Agent to check for errors:

you might get the below message if you are not having AD group permissions to the selected folder to backup as a destination.
Message

The job failed.  Unable to determine if the owner (TYLER\mahesh.sambu) of job DB MaintenancePlan.Subplan_3 has server access (reason: Could not obtain information about Windows NT group/user 'TYLER\mahesh.sambu', error code 0x54b. [SQLSTATE 42000] (Error 15404)).

Problem is with owner of the job. If owner is sysadmin SQL server will run job with SQL server agent account but if owner does not belongs to sysadmin SQL Server will impersonate the job owner login to run the job and since job owner login is missing some privilege the job is failing. 

If you are having issues in running the maintenance plan due to AD group permissions the 
change the owner of the job through the SQL Server Agent and select the job OWNER to set the properties of the job OWNER to sa like below:

 In the future articles lets see how we can see the metadata of this plans.