This article is meant to work hand in hand with the steps explained in Project Insight Backup and Restore Recommendations for SQL Server 2014. A database maintenance plan is created within the appropriate database server selection from SQL Server Management Studio in the “Management -> Maintenance Plans” selection. Right-click to create a “Maintenance Plan Wizard” as follows:
A maintenance plan setup wizard will guide you through the process of naming the maintenance plan and determining schedules for the tasks within the plan. If you choose separate schedules for each task as we have done in this example, you will either need to set a schedule for each task or run the maintenance plan on-demand (manually). Setting a single schedule would be the option to choose if you wanted all of the tasks within the plan to run at a specific interval without task exceptions, such as everyday at 2 AM all tasks will proceed as configured in the maintenance plan.
Select the maintenance tasks to be performed based on your needs. In this example, we have chosen to do full database backups along with some cleanup and index maintenance.
Select the order in which you would like the maintenance tasks to be performed.
Define the Check Integrity Task and select the Project Insight database.
Define Back Up Database (Full) Task and select the Project Insight database.
Define the Reorganize Index Task, select the Project Insight database and for Object select Tables and view.
Define Update Statistics Task by selecting the Project Insight database and Tables and views for Objects.
Define Maintenance Cleanup Task, if you wish to keep files up to 4 weeks it is the default, but a week should be sufficient.
Verify your settings and complete the maintenance plan.
Important note: The above example does not have a schedule for each of the tasks so they would only be performed on-demand. You will want to choose a schedule for each task or have the entire plan on a single schedule. The maintenance plan is not a substitute for data backup to removable storage and/or redundant systems. The maintenance plan simply makes a backup file which can be restored. The files created as part of the maintenance plan must be utilized by your existing backup procedures so that the data is available for disaster recovery.
Remember that SQL Server data must be created as recoverable data and then backed up. Missing this maintenance plan step and making copies of files in the database instead, either by copying or through the use of imaging software, will result in files which may not be recoverable at the crucial moment of need during a disaster recovery event.