Creating a SQL Database Maintenance Plan

This topic  describes how to create a new database maintenance plan using the Microsoft SQL Server's Database Maintenance Wizard.

In general, you need to create a maintenance plan for each set of tasks that you want to perform as a group. For example, you need one maintenance plan for hourly transaction log backups, a second plan for the nightly transaction log backups, and a third plan for each recurring full database backup for the APTIFY database. (Note that you may also want to create a separate maintenance plan for backing up the system databases.) Here is an overview of the maintenance plans that Aptify recommends you create to handle database tasks:

• Maintenance plans to automatically perform a full backup of the APTIFY and system databases on the schedule described in Backup Strategy for the APTIFY Database.
• You can create separate backup plans for the APTIFY databases and the system databases or you can backup all of these databases in one plan.
• Maintenance plan to backup the APTIFY database's transaction log hourly (using the append to existing media option).
• Maintenance plan to backup the APTIFY database's transaction log nightly (using the INIT or overwrite existing media option).
• Maintenance plan(s) to perform general maintenance tasks on a regular basis (such as every week), including:
  • Check Database Integrity
  • Shrink Database

  • Reorganize Index

    The frequency of these tasks depends on the requirements of your organization.

Within Microsoft SQL Server Management Studio, you can create maintenance plans manually or you can use the Maintenance Plan Wizard. When you create a new maintenance plan, SQL Server automatically creates a corresponding Job for the SQL Server Agent.

Follow these steps to the use the wizard to create a new maintenance plan:

1. Open Microsoft SQL Server Management Studio.
   
   • If prompted, log in to your server using a system administrator account.
      
2. Add your database server to the Object Explorer using the Connect menu option, if necessary.
   
   
   • If prompted, log in to your server using a system administrator account.
3. Expand your server's management tree.
4. Right-click the Maintenance Plans node and select Maintenance Plan Wizard from the pop-up menu. 
   
   
   
   • The Database Maintenance Plan Wizard begins with an introductory screen, explaining how the wizard helps create tasks to:
     
     • run database integrity checks
     • update database statistics
     • perform index maintenance
     • perform database back ups
        
5. Click Next to continue.
6. Enter a Name and Description for your maintenance task.  
   
    
7. Specify the schedule options.
   
   • Identify if you want to define a single schedule for the entire plan or a separate schedules for each task in the plan.
   • Click the Change button to open the New Job Schedule dialog in order to specify when this plan should execute automatically.  
     
     
8. Check one or more maintenance tasks to execute in this plan.
   
   • The following figure illustrates a plan that will only have one task (Back Up Database (Full)). 
     
     
9. Click Next to continue.
10. If the plan will execute multiple tasks, specify the order in which you want to run these tasks (or accept the default order). Click Next to continue.
    
    • The following figure illustrates a plan with three tasks. If your plan has multiple tasks, you can specify the order in which the tasks are executed. 
      
      
11. Configure the options for each of the tasks you selected. When finished, click Next to continue to the next screen. (Each task in the plan has its own options screen.)
    
    • Specify the database or databases on which the task will be performed.
    • Specify task-specific options (if in doubt, use the default setting).
    • For back up tasks, configure the following options:
      
      • Select the APTIFY database. You can also specify the system databases (master, model, and msdb) to back up all four databases at the same time in one task.
      • Specify the location for backup files. As mentioned earlier in this appendix, the partition occupied by the operating system or any partitions containing a pagefile should not be used as a backup location. If you specify a network location, the SQL Server Agent that will execute this task must have read/write access to that network path.
      • Select the Verify Backup Integrity option.
         
    • The following figure illustrates the Options screen for the Back Up Database (Full) task. 
      
      
12. Specify reporting options (such as the location for the storage of the reports generated by the maintenance plan and a list of email recipients for the report), and click Next.
13. Click Finish to complete the wizard and create the maintenance plan.  
    
     
14. Click Close when finished.
    
    • The SQL Server Agent will execute the specified tasks at the scheduled date and time.

Note that after you have created a maintenance plan using the wizard, you can edit the plan as needed by selecting Modify from the plan's right-click menu under the Maintenance Plan heading in the SQL Server Management Studio's Object Explorer.

To manually execute a maintenance plan, select the Execute option.