Tip

Using the database maintenance plan wizard

Barrie Sosinsky, Contributor

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

    Requires Free Membership to View

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.


This was first published in March 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.