A maintenance plan can be used in SQL Server 2005 to automatically back up the database, reducing the workload of the database administrator. Here's how to use it:
(1) Start SQL Server Management Studio and select Manage-maintenance plan option in the Object Explorer window.
2) Right click on "Maintenance Plan" and select "Maintenance Plan Wizard" option in the popup shortcut menu.
, the Maintenance Plan Wizard dialog box pops up, click the Next button
3) Popup "Select Target Server" dialog box, in the "Name" text box you can enter the name of the maintenance plan, in the "description" text box you can enter the description of the maintenance plan, "in the Server" text box you can enter the name of the server to use, and finally select the correct ID information, click the "Next" button.
(4) The Select Maintenance Task dialog box appears, where the dialog
You can choose from a variety of maintenance tasks: Check database integrity, shrink databases, regenerate or organize indexes, update statistics, clear history, execute SQL
Server Agent jobs, backup databases, and so on. In this example, select the Backup database (full) check box, and the settings for other maintenance tasks are similar.
(5) Click the Next button to bring up the Select Maintenance Task Order dialog box, if you have more than one maintenance task, you can set the order of maintenance tasks in the move up and move down two buttons here, and then click the Next button when you are finished.
(6) The "Define Task" dialog box appears, under "Database"
The drop-down list box allows you to select the name of the database you want to back up, to backup the database or to back up the database file in the backup components area, to add backup files and backup devices to the destination area, to set whether to append backup data to the backup file, etc. Click the Next button when you are finished setting up.
(7) The Select Schedule Properties dialog box pops up and the Change button is clicked.
8) pop up the New Job Schedule dialog box, where you can set the time and frequency of the backup database, click OK after setting up the dialog box as shown below, and click the Next button.
(9) Pop-up 18.24 shows the Select Report Options dialog box, where you can select if you are managing maintenance plan reports: You can write them to a file, or you can send the database administrator by e-mail. Click the Next button when you are finished setting up.
10) The Complete wizard dialog box pops up and the Finish button is clicked to complete the maintenance plan creation operation.
11) After you create the maintenance plan, confirm that SQL Server Agent is started: In the Object Explorer window, right-click the SQL
Server Agent, select the Start option in the Popup shortcut menu.
How SQL SERVER 2005 establishes a maintenance plan for automated backups