Brad’s Sure Guide to SQL Server Maintenance Plans- P49: SQL Server has a reputation as being a simple database application to install, configure, and maintain. This is a little misleading. SQL Server is a powerful relational database that can handle the needs of the largest organizations and, as such, its proper maintenance almost certainly requires the attention of an experienced DBA. | Chapter 19 Create and Modify Maintenance Plans Using the Designer It has been quite a long journey through the Designer to get to the point where we are ready to design create test and schedule a full Maintenance Plan. In this chapter we ll walk through the full process of creating from scratch and deploying a Maintenance Plan that could be used to perform the following essential database maintenance tasks back up database data and log files run regular database integrity checks perform index rebuilds delete old data from the msdb database remove old backup and report files that are no longer required to be stored on the local server notify the designated operator should one of the tasks fail. While the example does perform many of the essential maintenance tasks it s not intended as a template for how to create Maintenance Plans nor does it cover all the necessary maintenance tasks. The exact nature of a Maintenance Plan will always depend on the exact nature of your business and administration needs. You must establish exactly what your Maintenance Plans need to achieve and then implement them appropriately using the available options that best meet your needs. Establishing Your Maintenance Goals Before you can create a Maintenance Plan for your SQL Server instances you have to first establish what you want to accomplish. Based on established goals the DBA must create Maintenance Plans that include the appropriate tasks appropriately configured and scheduled to meet these goals. 241 Chapter 19 Create and Modify Maintenance Plans Using the Designer To some extent these goals will be established at an organizational level. For example tolerance towards potential data loss should be established at a business level and on a system-by-system basis. This in turn will dictate the DBA s maintenance policy in regard to the nature and frequency of database backups in his or her Maintenance Plans. Elsewhere the nature of the plans will be guided by the DBA s knowledge of a
