Choose the tasks that will be part of your plan
Database Maintenance Plans allow you to automate many database administration tasks in Microsoft SQL Server. You can create maintenance plans using an easy wizard-based process without any knowledge of Transact-SQL.
You may perform the following tasks within a database maintenance plan:
Starting the Database Maintenance Plan Wizard
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, as shown above. Click Next to continue.
Name the Database Maintenance Plan
In the next screen that appears, 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.
Schedule your Database Maintenance Plan
You'll probably want to use the default option here "Single schedule for the entire plan or no schedule". You do have the option of creating different schedules for different tasks, but I prefer to create different plans for different schedules to help keep things straight.
Click the Change button to alter the default schedule and choose the date and time the plan will execute. Click the Next button when you are finished.
You'll see the window shown above. Select the task(s) that you wish to include in your database maintenance plan. When you're finished, click the Next button to continue.
The next window, shown above, allows you to change the order of tasks in your maintenance plan by using the Move Up and Move Down buttons.
Next, you'll have the opportunity to configure the details of each task. The options presented to you will vary based upon the tasks you have chosen. The image above shows an example of the screen used to configure a backup task. When you're finished, click the Next button to continue.
Finally, you have the ability to have SQL Server create a report each time the plan executes containing detailed results. You may choose to have this report sent to a user via e-mail or saved to a text file on the server.
Database Maintenance Plans allow you to automate many database administration tasks in Microsoft SQL Server. You can create maintenance plans using an easy wizard-based process without any knowledge of Transact-SQL.
You may 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
Starting the Database Maintenance Plan Wizard
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, as shown above. Click Next to continue.
Name the Database Maintenance Plan
In the next screen that appears, 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.
Schedule your Database Maintenance Plan
You'll probably want to use the default option here "Single schedule for the entire plan or no schedule". You do have the option of creating different schedules for different tasks, but I prefer to create different plans for different schedules to help keep things straight.
Click the Change button to alter the default schedule and choose the date and time the plan will execute. Click the Next button when you are finished.
You'll see the window shown above. Select the task(s) that you wish to include in your database maintenance plan. When you're finished, click the Next button to continue.
The next window, shown above, allows you to change the order of tasks in your maintenance plan by using the Move Up and Move Down buttons.
Next, you'll have the opportunity to configure the details of each task. The options presented to you will vary based upon the tasks you have chosen. The image above shows an example of the screen used to configure a backup task. When you're finished, click the Next button to continue.
Finally, you have the ability to have SQL Server create a report each time the plan executes containing detailed results. You may choose to have this report sent to a user via e-mail or saved to a text file on the server.