SQL Server provides a convenient and friendly way to automate important tasks such as optimizations, integrity checks, full backups, and transaction-log backups. Using the Database Maintenance Plan wizard you can create one or more plans for each of your databases, or a plan to be used by one or more of your databases. To initiate the wizard, select the Database Maintenance Planner command from the Tools menu in the Enterprise Manager. You can also select the wizard from the Taskpad view, by right clicking on a server in the Management folder, or by right clicking on a database name and from the All Tasks command choosing Maintenance Plan.
After you name your plan you'll get a prompt to select the databases it applies to. The Update Data Optimization step allows you to reorganize your data and index pages, with options such as removing unused space from those files. Another step in the wizard provides a Database Integrity Check scan, using the DBCC CHECKDB command. Options for integrity checks provide for automatic repairs, suppressing a backup if an error is found for a check prior to backup, and deciding whether or not to check your indexes. Keep in mind that integrity checks are time consuming and should be done during hours of low activity.
The wizard offers you two types of backups, one full backup and another that backs up the transaction log. Full backup doesn't offer much in the way of options; you can choose to verify your backup, and you can also specify where the backup will be written, whether to tape or disk, and using which directory. An option also lets you automatically delete older backups. The transaction-log backup offers identical options. Part of the wizard's action is to write a log file called the Maintenance History. This history is optional, but recommended.
Once they are created, you can view maintenance plans in the left pane of the Enterprise Manager as part of the definition of a particular database. Click on the database's name, then on the Jobs icon. If you double-click on a plan, you open a multi-tabbed dialog box that lets you edit your plan.
While the Database Maintenance Plan wizard won't replace more specialized and refined enterprise tools such as SQL Server backup programs, this wizard does offer an administrator a quick and painless way to make sure that essential functions will be managed without hands on effort.
Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.
