Database Maintenance Plan

Database maintenance keeps the database running at optimal state. Creating a Database maintenance plan allows SQL Server to effectively automate the maintenance of databases, save time for administrators, and prevent delays in database maintenance. The process of creating a database maintenance plan is as follows:

(1) In Enterprise Manager, select the database in which you want to create a maintenance plan, right-click, and select the Maintenance Plan menu item from the All Tasks submenu from the shortcut menu, and then appear as shown in Figure 6-14 The welcome interface shown.

(2) Click the Next button to appear in the Select Database dialog box shown in Figure 6-15. You can select the database you want to maintain as needed.

(3) Click the Next button to appear the Update Data Optimization Information dialog box as shown in Figure 6-16. You can choose whether you want to restructure the database's data page or index page as needed. Click the Change button to appear in the Edit Loop Work Plan dialog box as shown in Figure 6-13.

(4) In Figure 6-16, click the Next button, and the Database Integrity Check dialog box appears as shown in Figure 6-17. You can choose whether to perform integrity testing on the database to find out if there are any database errors caused by software or hardware errors.


(5) Click the Next button to show the specified database backup Plan dialog box as shown in Figure 6-18. You can choose whether to back up the database to a tape or hard disk. You typically choose to back up your database to your hard disk.

(6) Click the Next button to see the Directory dialog box for the specified database backup file as shown in Figure 6-19. You can choose to store the database backup files to the specified hard disk directory, and you can create a subdirectory for each database.

(7) Click the Next button to appear the specified transaction log File Backup Plan dialog box as shown in Figure 6-20. You can choose whether to add transaction log backups to the database maintenance plan.

(8) Click "Next" button to appear as shown in Figure 6-21 to the specified transaction log file Backup storage Directory dialog box. You can choose whether to store transaction log backups in the relevant options.


(9) Click the Next button to appear as shown in Figure 6-22 of the Generate Maintenance Plan Report dialog box to select the report of the storage directory options

(10) Click the Next button to appear as shown in Figure 6-23 for the specified maintenance plan execution History Location dialog box. You can optionally store it on a local server or a remote server.

(11) Click the Next button to see the final interface shown in Figure 6-24, specifying the Maintenance Plan Name dialog box. After you enter a name, click the Finish button to complete the setup of the maintenance plan.
After you have set up your maintenance plan, you can view and modify it in Enterprise Manager. You can see the schedule that you set up by selecting Database maintenance plans under the Management subdirectory under the server folder in Enterprise Manager. As shown in Figure 6-25.

In the Figure 6-25 window, select the name of the database maintenance plan you want to modify, right-click, and select the Properties option from the shortcut menu, and the Database Maintenance Plan Editing dialog shown in Figure 6-26 appears. You can edit almost all of the information that you set up in the Plan Generation Wizard.