Brad’s Sure Guide to SQL Server Maintenance Plans- P5: 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 i Why is Database Maintenance Important some tasks more difficult than they need to be. For example the maintenance task that is designed to delete older backup files can only delete one file type at a time such as BAK or TRN and not both at the same time. Because of this you may have to create multiple Maintenance Plans just to perform simple tasks such as this. No scripting to other instances. Maintenance Plans created with the Wizard cannot be scripted and moved to other SQL Server instances although multi-server Maintenance Plans can be created. Bugs in some earlier versions of the Wizard. If you use SQL Server 2005 Service Pack 2 or higher or SQL Server 2008 then you should have no problems. Some experienced DBAs will tell you that real DBAs don t use the Maintenance Plan Wizard and instead always write their database maintenance plans from scratch using T-SQL or PowerShell scripts. In reality this is not true. Many real DBAs use the Maintenance Plan Wizard when it is appropriate. Much of this book will be devoted to letting you know when using the Maintenance Plan Wizard is appropriate and when it is not. Maintenance Plan Designer If you search for the Maintenance Plan Designer in Books Online you won t find anything referred to by this exact name. This is because I had to provide a name for a feature of SQL Server that does not appear to have a consistently-used official name. Sometimes it is referred to as New Maintenance Plan or the Maintenance Plan Design Tab and other times as the Maintenance Plan Designer Surface. Essentially the Maintenance Plan Designer is a drag-and-drop GUI interface found in SSMS based on the SQL Server Integration Services SSIS Designer Surface which allows DBAs to manually design and create Maintenance Plans from scratch or to modify Maintenance Plans originally created using the Maintenance Plan Wizard. The Maintenance Plan Designer offers more features than the Wizard and this coupled with the element of manual control
