Create an automatic backup maintenance plan

Since database backup is so important, as a database administrator, you have to spend a lot of time backing up the database. When data in a database is updated frequently, it is essential to back up data several times a day. If the database administrator needs to back up the database manually each time, it will be an unbearable task.

In SQL Server 2005, you can use maintenance plans to automatically back up databases, reducing the workload of database administrators. The usage is as follows:

(1) start SQL Server Management studio and select database instance Management> maintenance plan in the object Resource Manager window.

(2) Right-click maintenance plan and select the maintenance plan wizard option from the shortcut menu. The maintenance plan wizard dialog box shown in 18.17 is displayed, click Next.

Figure 18.17 maintenance plan wizard dialog box

(3) The "select target server" dialog box shown in 18.18 is displayed. In the "name" text box, enter the name of the maintenance plan. In the "Description" text box, enter the description of the maintenance plan; in the server text box, enter the name of the server you want to use. Finally, select the correct ID card and click Next.

Figure 18.18 select target server dialog box

(4) The select maintenance task dialog box 18.19 is displayed. In this dialog box, you can select multiple maintenance tasks: check database integrity, shrink the database, regenerate or organize indexes, update statistics, clear historical records, execute SQL Server proxy jobs, and back up databases. In this example, select the backup database (complete) Check box. The settings for other maintenance tasks are similar.

Figure 18.19 select maintenance task dialog box

(5) Click Next. The select maintenance task sequence dialog box shown in 18.20 is displayed. If multiple maintenance tasks exist, you can click the move up and move down buttons to set the order of maintenance tasks. After the settings are complete, click Next.

Figure 18.20 select maintenance task sequence dialog box

(6) The define task dialog box shown in 18.21 is displayed. In the database drop-down list box, you can select the name of the database to be backed up; in the backup components area, you can select backup database or backup database files; in the [target] area, you can add Backup files and devices, and set whether to append backup data to the backup files. Click Next after setting.

Figure 18.21 define task dialog box

(7) In the displayed 18.22 dialog box, click Change.

Figure 18.22 select plan Attributes dialog box

(8) The "create job plan" dialog box shown in 18.23 is displayed. In this dialog box, you can set the time and frequency of backing up the database. After the setting is complete, click the "OK" button to return to the dialog box 18-22, click Next.

Figure 18.23 create job plan dialog box

(9) The "Select Report option" dialog box shown in 18.24 is displayed. In this dialog box, you can choose to manage the maintenance plan report, which can be written to a file, you can also email the database administrator. Click Next after setting.

Figure 18.24 Select Report option dialog box

(10) The "complete wizard" dialog box shown in 18.25 is displayed. Click "finish" to create the maintenance plan.

Figure 18.25 "complete wizard" dialog box

(11) after creating the maintenance plan, check whether the SQL Server proxy is started. In the object Resource Manager window, right-click SQL Server proxy ], in the shortcut menu that appears, select start.