One of the most comprehensive and helpful wizards in SQL Server is the Database Maintenance Plan Wizard. By creating a wizard that steps you through the core maintenance tasks, Microsoft has enabled both novice and experienced administrators to quickly establish a valid maintenance plan. Many administrators discount wizards as simplistic and "beneath" them, and in many cases experienced administrators feel they can perform tasks quicker without them. If you fall into this category, I encourage you to give the Database Maintenance Plan Wizard a chance. The wizard leverages the features of the SQLMAINT utility to provide additional functionality such as creating backup files with unique names based on a timestamp. As the functions you define in the wizard are saved as SQL Server jobs, you still maintain control, as the jobs can be edited after they are created.
To access the Database Maintenance Plan Wizard, from the Enterprise Manager toolbar, select Tools, and then Database Maintenance Planner; it can also be accessed from the Magic Wand icon on the toolbar under Management. After you're past the welcome screen, you will be stepped through various screens allowing you to customize your maintenance plan.
The first screen allows you to select which databases to back up. The option buttons allow you to select all databases, all system databases, or all user databases, or to build a custom selection of databases. New for SQL Server 2000 is the check box to ship the logs to another server. If this is selected, you will be presented with a new set of screens in the log section of the wizard where you can choose a network share from which to ship the logs, and a remote server and database to act as the standby database. Log shipping is used to keep a standby server up to date. This check box is disabled if the database is in simple recovery mode. Figure 17.1 shows the Select Databases screen.
The next screen is used to optimize data access and storage. The first option, if selected, specifies that indexes should be dropped and rebuilt, either with the original amount of free space reserved, or a new amount specified as a percentage. The second option allows you to update the statistics used by the query optimizer in building a query plan. A sample percentage tells SQL Server how much of the data to sample. A higher percentage gives more reliable statistics, but takes longer to run. Note that this option is not available if you have chosen to rebuild the indexes, as new stats will be generated when the indexes are created. The next options control file size. You can specify a size at which the database will attempt to remove any free space, and how much free space should be retained in the database to allow for future growth. The schedule window indicates when the operation will run, and the Change button allows you to customize the schedule. Figure 17.2 shows the Update Data Optimization Information screen.
The Database Integrity screen allows scheduling of the database consistency check DBCC CHECKDB. This checks the data pages for inconsistencies. You are given the option of including the indexes, which is more thorough but time consuming, and also to attempt to repair any minor problems. Note that if problems are found, SQL Server must put the object in Single User Mode before a repair attempt will be made. The repair will not proceed if users are connected to the database. The integrity checks can be made before each backup or on an independent schedule. If you choose to run the check before each backup, and the check fails, the backup doesn't run. Figure 17.3 illustrates the Database Integrity Check screen.
Moving on to the next screen, you specify whether to include backups as part of the maintenance plan, whether a verification should be done on the completed backup, whether backups should be backed up to disk or tape, and when you would like to schedule the backups. Figure 17.4 shows the Specify the Database Backup Plan screen.
If, in the previous screen, you selected disk as the backup media, the next screen will allow you to customize where the backup files should be located. The first option allows you to choose between the default backup location and a directory that you specify. Next, you can select to create a separate subdirectory in which to store the backups for each database. This will really help keep things organized if you have several databases, or if you keep several backups for each database. This brings you to the next selection where you specify how long to keep each backup. This can be specified in minutes, hours, days, weeks, or months. With this selected, SQL Server will automatically delete backups older than the specified time. This is really handy to maintain a revolving backup schedule, or to clean up disk files if you back up to disk using SQL Server and then back those backup files to disk with your network backup utility. The last option is to specify the file extension for the backup files. Figure 17.5 shows the Specify Backup Disk Directory screen.
The next screen is the same as the database backup screen, except that here you are specifying whether to include the transaction log in the backup plan. Figure 17.6 shows the Specify the Transaction Log Backup Plan screen.
Next, you come to the Specify the Transaction Log Backup Disk Directory screen, where you are presented with the same options for location and cleanup as you were for the database. See Figure 17.7 for an example of this screen.
Now you can configure how you would like reports of the maintenance plan's activities to be generated. From here, you can choose a directory to which to write the reports, specify how long they should be retained, and even e-mail a report to an operator. This reporting is a great way to keep track of the success or failure of the maintenance plans operations. I especially like the e-mail feature, which enables me to keep track of what is happening even when I am not in the office. Figure 17.8 shows the Reports to Generate screen.
The Maintenance Plan History screen has you configure where to store the maintenance plan history, either locally or on a remote server, and specify how many rows of history should be kept before it starts to overwrite. Figure 17.9 shows this screen.
The last screen lets you review your plan before clicking Finish to create the plan. Figure 17.10 shows the final screen of the wizard.
After a maintenance plan has been created, it can be accessed and revised at any time from the Database Maintenance Plan folder located in the Management folder of Enterprise Manager. Figure 17.11 shows the Properties dialog box for the Maintenance Plan.