SQL 2005 Database
Management Plans
Overview
STI recommends that users create database maintenance plans for Microsoft SQL 2005 to maintain the integrity of the system’s database. Database maintenance plans are comprised of automated jobs which may be scheduled to run unattended daily, weekly or monthly. This document provides instructions on creating and using a database maintenance plan.
Creating Database Maintenance Plan
A database maintenance plan is simply a set of tasks that will be automatically performed on your SQL Server databases. This set of tasks will lessen the burden on the administrator by automating essential maintenance tasks. These tasks, which include data integrity checks, backup, and database optimization, will keep the database running at top efficiency.
Creating a maintenance plan differs a little from SQL 2000 to SQL 2005. Users who are going to use new Maintenance Plan features are strongly advised to install the SQL Server 2005 Service Pack 2 first. In order to create a new maintenance plan in Management Studio, connect to the target server using Windows Authentication and perform the steps that follow.
• Open SQL Server Management Studio.
• Select New Maintenance Plan and enter a descriptive name for the plan.
• A Maintenance Plan design panel will appear on the right, and a toolbox with available Maintenance Plan Tasks will be displayed on the left.
• Click on the Connection button to verify that the current connection uses Windows
Authentication (recommended by Microsoft). Currently the connected Windows user will become the owner of the job created by this maintenance plan.
• The first step in creating a database or transaction log backup is to drag and drop Back up database task from the toolbox to the design panel. Then double-click on that item to set the following necessary properties:
o
Click on the drop-down field to bring up the database selection window.o For Backup type, choose Full.
o Under Destination Parameters, select to back up to Disk. Make sure that the Create a backup file for every database option is selected and also ensure that the Create a sub-directory for each database box is checked.
o You may use the default destination folder or specify your own.
o For Backup file extension, make sure that the value listed is bak, without a leading dot denoting the file extension.
o Check the Verify backup integrity box.
Scheduling the Database Maintenance
Plan
• To create a schedule for this plan, click on the ellipsis button, located to the right of the Schedule field.
• Set the necessary parameters in the Job Schedule Properties window. Schedule Name will be defaulted to <maintenance_plan_name>-Schedule and may be modified if necessary.
• Click OK to save this schedule.
Note: When the user clicks on the Logging button, the Reporting and Logging window will appear. This screen is similar to the SQL 2000 Reporting tab. The default folder for the backup may be modified. The user may also choose between the Create a new file option, which will create a new log file named <maintenance_plan_name>
_yyyymmddhhmm.txt for every completed job; or the Append to file option (in this case, the user must name the log file). An option is also available to Send an e-mail to the operator.
• In order to delete obsolete backup files, an additional Maintenance Cleanup Task must be inserted and configured. Drag and drop this task into the design panel and double-click on it to set the properties:
o Choose the Backup files option.
o File location: Select Search folder and delete files based on an extension. Provide the same full path to the backup folder that was used on the Back up database task.
• The file extension should be the same as that used in Back up database task: bak for the database backup files or trn for the transaction log backup files (both should be entered without the leading dot used to indicate a file extension).
• If a separate directory was created for each database in Back up database task, the user may also check the Include first-level subfolders box.
• Under File age:, check Delete files based on the age of the file at task run time and set the Delete files older than the following option.
• Click on File | Save As to save the plan. As a result, a new job, with the same name as the maintenance plan name, has been created. Both the plan and the job will be listed in the Object Explorer panel.
• To test these plans/jobs, the user may right-click on the plan icon in Object Explorer and select Execute from the drop-down menu, or right-click directly on the job and select Start Job at Step, as applicable.
Checking the Results
The plan/job results will be stored in the msdb database and in the file specified in the plan Logging property. If the Log extended information box was checked in the Reporting and Logging window during maintenance plan creation, the detailed information will be stored in both the log file and the
msdb.dbo.sysmaintplan_logdetail table. If this box was not checked and the job status is Succeeded, there will be no details provided in the log file or in the msdb.dbo.sysmaintplan_logdetail table. However, if any of the tasks failed, the details will be available in this location.
