Microsoft SQL 2005 Maintenance Wizard Part 3

April 6, 2007

Introduction

This article focuses on the SQL Server 2005 Maintenance Plan Wizard for creating Database Backup operations. The Maintenance Plan Wizard is a graphical interface for creating a variety of database housekeeping tasks. In addition to Backup operations, maintenance items such as reorganizing data and index files, updating statistics and performing consistency checks can be performed. These tasks should be done on a regular basis to insure SQL performance and data integrity are optimized. All of these tasks can be executed using TSQL commands, however the Maintenance Plan Wizard makes selecting these tasks and their options easy. In addition, the Wizard will assemble all of your selected tasks into a reusable and customizable package.

Part 1 of this series introduced the Maintenance Plan Wizard and demonstrated how to use the Wizard for creating Data Check Integrity Tasks, Shrink Database Tasks, Reorganize Index Tasks, and the Rebuild Index Tasks.

Part 2 continued with explanation of Agents XPs, then demonstrated the Update Statistics Task as well the Clean Up History Task. Lastly, the Designer was introduced. The Designer can be used for both creating maintenance tasks from scratch and for modifying packages created by the Wizard.

Below is the complete list of tasks the SQL 2005 Maintenance Plan Wizard is capable of creating.

• Check Database Integrity

• Shrink Database

• Reorganize Index

• Rebuild Index

• Update Statistics

• Clean Up History

• Execute SQL Server Agent Job

• Back Up Database Full

• Back Up Database Differential

• Back Up Database Transaction Log

Backup Up Database Full

There are several different types of backup methods. The Full Backup copies all the data in the database to a file that can be used to completely restore the database in one step. The backup file size will be smaller than the actual database because no white space is copied. A Full Backup can be used as a convenient method for moving a database from test to production. If the database exists on the production machine, it will be overwritten. If it doesn’t exist, it will be created. Below are the steps for creating a Full Backup using the Maintenance Plan Wizard.

To use the Maintenance Plan Wizard, Open the Microsoft SQL Server Management Studio. The Object Explorer should be visible. If it’s not, from the top menu, select View, then Object Explorer. Expand the Management folder, and then right click Maintenance Plan. Select the Maintenance Plan Wizard.

• When the Maintenance Plan splash screen appears, click Next.

• The Select a Target Server screen should follow. Give the plan a name and select the server. The options for running the plan with either a Windows login or a SQL login are also presented. After making your selections, click Next.

• The Select Maintenance Tasks screen should appear. Select Back Up Database (Full) and then click Next.

• The Order screen that’s presented is used to specify task execution order when there is more than one type of task being performed in the same plan. Click Next.

• The Define Back Up Database screen should now be displayed. The first drop down box lets you specify a database or databases to backup. The options for backing up System and User databases are also presented. This example will back up the Adventure Works database. To create a file backup, click Disk, then “Back up database across one or more files”. Click the Add button and enter a file path and name, such as “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\myFullBackup.bak”, then click OK. Change the “If backup file exists” option to Overwrite. The default option of Append will add this backup to the end of the file name specified above if it exists. The next option, “Create a backup file for every database,” creates a separate physical backup file for each database if selected. Directly below is an option to put each separate file into its own directory. Check the Verify backup integrity box and click Next.

• The “Select Plan Properties” allows the job to be scheduled as a reoccurring task. For this example, no Schedule will be set, making it an On Demand job. Click Next.

• The “Select Report Options” screen should appear next. Keep the default check of Write a report to a text file. The log can be helpful in diagnosing problems if they occur. Click Next.

• A synopsis of the job should now appear. Click Finish to start the backup.

• The job should execute and a Progress screen like the one shown below should appear.

If there were errors with the job, they would appear on this screen next to a red circle. Clicking the Report button gives options to view or save the log. In this example, the log reiterated the Progress screen messages as shown below.

Maintenance Plan Wizard Progress
- Creating maintenance plan "FullBackup" (Success)
- Adding tasks to the maintenance plan (Success)
- Adding scheduling options (Success)
- Adding reporting options (Success)
- Saving maintenance plan "FullBackup" (Success)

Execute

The Backup Job has now been created, but it hasn’t actually run, or been executed. To run the job, Right Click the Maintenance Plans folder from inside the SQL Server Management Studio’s Object Explorer and select Refresh. The newly created job should appear. Right Click its name and select Execute. A status indicator should appear showing a successful backup.

Restore

Although Restores are not part of the Maintenance Plan Wizard, we’ll examine how to restore the file the Wizard just created.

• From the SQL Server management Studio, right click the Database folder then select Restore Database.

• In the “To database” drop down box, select the target database. For this example, select Adventure Works.

• Our backup was saved as a file, so click the “From Device” radio button, and then click the browse button at the right (the button with three dots on it).

• Keep the default media option of File, and click the Add button. Select the newly created backup file, then click OK, and OK again.

• The “Select the backup set to restore” window should now be populated, check the Restore box.

• At the upper left, click the Options folder. Because our destination database, Adventure Works, already exists, check the Overwrite the existing database box, and then click OK.

• A status dialog will confirm a successful restore.

Conclusion

The SQL Server Maintenance Plan Wizard can be used to create a variety of maintenance tasks. Its simple full featured interface makes it a handy option for creating job packages.

» See All Articles by Columnist Don Schlichting