Creating a SQL Server Database Maintenance Plan

Use the SQL Server maintenance plan wizard

 Database Maintenance Plans allow you to automate many database administration tasks in Microsoft SQL Server. You can create maintenance plans using the SQL Server maintenance plan wizard without any knowledge of Transact-SQL.

Instructions in this article apply to  SQL Server 2019 (15.x).

How to Use the SQL Server Maintenance Plan Wizard

You can perform the following tasks within a database maintenance plan:

  • Shrinking a database
  • Backing up a database
  • Performing an operator notification
  • Updating database statistics
  • Verifying the integrity of a database
  • Cleaning up leftover maintenance files
  • Executing a SQL Server Agent job
  • Executing a Transact-SQL statement
  • Rebuilding an index
  • Reorganizing an index
  • Cleaning up database histories
  1. Open Microsoft SQL Server Management Studio (SSMS) and expand the Management folder. Right-click on the Maintenance Plans folder and select Maintenance Plan Wizard from the pop-up menu. You will see the wizard's opening screen. Select Next to continue.

    SQL Server maintenance plan wizard
  2. Provide a name and description for your database maintenance plan. You should provide information here that will be helpful to another administrator (or yourself!) who is trying to figure out the purpose of the plan months or years from now. Select either Separate schedules for each task or Single schedule for the entire plan or no schedule to specify a recurring schedule.

    The scheduling options in the Maintenance Plan Wizard
  3. You'll probably want to use the default option here Single schedule for the entire plan or no schedule. Select the Change button to alter the default schedule and choose the date and time the plan will execute. Select Next when you are finished.

    You do have the option of creating different schedules for different tasks, but we recommend creating different plans for different schedules to help keep things straight.

  4. Select the task(s) that you wish to include in your database maintenance plan. When you finish, select Next to continue.

  5. Change the order of tasks in your maintenance plan if desired using the Move Up and Move Down buttons.

    The Move Up and Move Down buttons
  6. Configure the details of each task. The options presented to you will vary based upon the tasks you have chosen. This image shows an example of the screen used to configure a backup task. When finished, select Next to continue.

  7. Finally, you can have SQL Server create a report each time the plan executes containing detailed results. Choose to have this report sent to a user via e-mail or saved to a text file on the server.