Microsoft SQL 2005 Maintenance Wizard Part 3
April 6, 2007
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.
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 doesnt 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 its 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.
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)
The Backup Job has now been created, but it hasnt actually run, or been executed. To run the job, Right Click the Maintenance Plans folder from inside the SQL Server Management Studios 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.
Although Restores are not part of the Maintenance Plan Wizard, well examine how to restore the file the Wizard just created.
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.