MSSQL Server Maintenance Plan

When and How to do server maintenance

Greenstone recommends that customers backup their MSSQL databases on a regularly scheduled basis. The SQL Server Maintenance Plan found inside SQL Server Management Studio allows the customer to configure their own Maintenance Plan to meet their business requirements.
 
The content below provides a recommendation for performing regularly scheduled backups. This recommendation should not be considered a requirement. This template should be modify based on business requirements and used as a guideline to assist your I.T. team in deploying a plan that meets your company's needs. The details of a SQL Server Maintenance Plan may vary from customer to customer. There are many unique situations that should be considered when creating a SQL Server Maintenance Plan. If your business has a Database Administrator, it is best to discuss Database Backup schedules and Recovery capabilities to ensure all relevant data is backed up on a regular basis and can be recovered in a timely manner if necessary.

Maintenance Plans allow the administrator to create backups, perform database integrity checks, database statistics updates and other tasks. These tasks are part of a Subplan which can be scheduled to run at specified time intervals.

Best Practices
http://sqlmag.com/database-backup-and-recovery/sql-server-backup-best-practices

• Perform Full Backups Daily

• Perform Frequent Transaction Log Backups

• Regularly Back Up System Databases

• Back Up the Host OS Daily

• Practice Recovery Operations – It is important to perform an actual Database Recovery operation to verify the backup and restore processes are working properly.

There are 2 methods for creating a Maintenance Plan via Microsoft SQL Server Management Studio -> Management

1. *Manually create the Maintenance Plan (preferred)

2. Maintenance Plan Wizard

 
The template and screenshots below will assume you are creating the Maintenance Plan manually.

• Consider these tasks as part of your Daily Maintenance plan

  1. Backup database full

  2. Rebuild indexes

  3. [optional] Update Statistics (This action could increase time to complete this sub-plan however it can improve performance. This step is not required because the database engine will automatically perform this step when necessary)

  4. Check Database Integrity

• Consider these tasks as part of your Hourly Maintenance plan

  1. Backup transaction logs

*Note: if there is a database failure you will need to restore the Database and the associated Transaction Logs. The database backup will contain all the data from your last full backup and the Transaction logs will contain all the transactions that occurred since your last backup. These 2 entities are required for a full recovery.

Creating a Daily Maintenance Plan

The screenshots and content below are provided as a guide for creating a Daily Maintenance Plan. These steps are not all inclusive. Steps can be added or removed based on the business need.

1. Creating a Daily Maintenance Plan

2. Right click on Maintenance Plans and choose Create New Plan

   1. Enter the name of the plan in the Name field

   2. [optional] enter a description

3. Add tasks to Subplan_1 (also you can rename Subplan_1 to something more meaningful)

   1. Click on the line of Subplan_1

   2. Click on the toolbox(to the left of the Object Explorer) and drag desired tasks to the Designer Area as illustrated below

   3. Open

      

4. Configure the Back Up Database task in the Subplan – right-click on the Back Up Database task and Edit

   1. Open

      

   2. Select the databases to be backed up. Accelerator and AGRIS databases should be selected. If there are multiple Accelerator and AGRIS databases, these can be selected as well.

   3. Open

      
   4. Open

      
   5. Open

      

5. Configure the Rebuild Index Task – right-click on the Rebuild Index Task and Edit

   1. Select the AGRIS and Accelerator databases

   2. Open

      

6. Configure the Update Statistics Task – right-click on the Update Statistics Task and Edit

   1. Choose AGRIS and Accelerator databases

   2. Open

      

7. Configure the Check Database Integrity Task – right-click on Check Database Integrity and Edit

   1. Open

      

8. Create a Subplan for backing up the Transaction Log

   1. Click the Add Subplan button to create a new subplan

   2. Drag the Back Up Database Task onto the Design area as indicated below

   3. Open

      

   4. Configure the Back Up Database Task for the Transaction Log – right-click on Back Up Database Task and Edit and choose the AGRIS and Accelerator databases

   5. Open

      
   6. Open

      
   7. Open

      

9. Configuring scheduled jobs to run on recurring intervals

   1. When the subplans above were created, a Job was automatically created that can be scheduled to run automatically

   2. Open

      

   3. Double click on Each Job to Edit the Schedule

   4. Open

      
   5. Open

      

   6. Configure the Transaction Log back subplan_2

   7. Open

      

10. Complete the Maintenance plan

    1. At this point the Maintenance plan is complete and the backups are scheduled to run automatically

    2. The Job Activity Monitor can be used to monitor the status of each job

    3. The Plan History can be viewed by right clicking on the Plan and choosing View History