Brad’s Sure Guide to SQL Server Maintenance Plans- P50: 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 you re ready to start using it. Add the Maintenance Plan Tasks At this point we have three subplans each with its own design surface. Our next task is to drag and drop the appropriate Maintenance Plan tasks to the design surface of the appropriate subplan. Let s consider each subplan in turn. Daily Full Backup Subplan According to the goals of our overall plan this subplan should contain One Back Up Database task to perform the full backup of AdventureWorks. One Notify Operator task to notify the operator should the full backup fail. Highlight the Daily Full Backup subplan and then drag each of these two tasks from the Toolbox and drop them on the design surface as shown in Figure . 246 Chapter 19 Create and Modify Maintenance Plans Using the Designer Figure The Daily Full Backup subplan now has the necessary Maintenance Plan Tasks. Note of course that although we ve now added the appropriate tasks they are not yet configured. We will do this later after adding the required tasks to the other two subplans. Hourly Log Backup Subplan According to our goals this subplan should contain One Back Up Database task - to perform hourly backups of the transaction log for the AdventureWorks database. One Notify Operator task - to notify the DBA should the transaction log backup fail. 247 Chapter 19 Create and Modify Maintenance Plans Using the Designer The resulting design surface should look as shown in Figure . Figure Notice that the Hourly Log Backup subplan now looks like the Daily Full Backup subplan. In their unconfigured states the Daily Full Backup and the Hourly Log Backup subplans look identical. Later when we configure them we will specify one to do a full backup and one to do a transaction log backup. Weekly Maintenance Subplan Finally we need to add Maintenance Plan tasks to our most complex subplan the Weekly Maintenance subplan. Based on our database maintenance plan goals we .
