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:

  • Shrink a database.
  • Back up a database.
  • Perform an operator notification.
  • Update database statistics.
  • Verify the integrity of a database.
  • Clean up leftover maintenance files.
  • Execute a SQL Server Agent job.
  • Execute a Transact-SQL statement.
  • Rebuild an index.
  • Reorganize an index.
  • Clean up database histories.
  1. Open Microsoft SQL Server Management Studio (SSMS) and expand the Management folder. Right-click the Maintenance Plans folder and select Maintenance Plan Wizard. You will see the wizard's opening screen. Select Next to continue.

  2. Provide a name and description for your database maintenance plan. Provide information that will help another administrator figure out the purpose of the plan. 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. Select Change to alter the default schedule and choose the date and time the plan will execute. Select Next when you're finished.

    You can create different schedules for different tasks. It's recommended that you create different plans for different schedules to keep things straight.

  4. Select the tasks 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 vary based on the tasks you chose. This image shows an example of the screen used to configure a backup task. When finished, select Next to continue.

  7. Have SQL Server create a report each time the plan executes containing detailed results. Choose to have this report sent to a user through email or saved to a text file on the server.

Format
mla apa chicago
Your Citation
Chapple, Mike. "Creating a SQL Server Database Maintenance Plan." ThoughtCo, Dec. 6, 2021, thoughtco.com/creating-sql-server-database-maintenance-plan-1019879. Chapple, Mike. (2021, December 6). Creating a SQL Server Database Maintenance Plan. Retrieved from https://www.thoughtco.com/creating-sql-server-database-maintenance-plan-1019879 Chapple, Mike. "Creating a SQL Server Database Maintenance Plan." ThoughtCo. https://www.thoughtco.com/creating-sql-server-database-maintenance-plan-1019879 (accessed March 19, 2024).