1.7 SQL Server 2000 database maintenanceIn the actual application of the database, to ensure the security of user data and prevent accidental data loss in the database, you need to back up the data in the database frequently, you can use backup for recovery. SQL Server 2000 provides database backup and restoration components. We will introduce them separately.
1.7.1 back up a databaseBackup is to back up the SQL Server database or transaction log. The database backup records the status of all data in the database during the backup operation, so that the database can be restored in time when it is damaged. First, let's take a look at how to use the Enterprise Manager to create a database backup device, which is an essential step to back up the database. The following describes how to create a disk backup device. (1) Open the Enterprise Manager and log on to the specified database server. (2) Expand the "manage" folder, right-click the "backup" node, and select the "Create backup device" command from the shortcut menu. the dialog box shown in 4-22 is displayed. (3) In the "name" text box, enter the name of the disk backup device to be created. (4) Select the "file name" option and enter the file name used by the disk backup device. The default backup file name and path are provided in the text box after "file name. You can also click the button to display the dialog box shown in Figure 4-23. In this dialog box, select the Local Computer and file used by the disk backup device. Figure 4-22 backup device properties Figure 4-23 backup device location dialog box (5) Click OK to complete the settings. After the backup device is created, you can use the Enterprise Manager to back up the database. The procedure is as follows. (1) Start the Enterprise Manager, log on to the specified database server, open the "Database" folder, right-click the database icon to be backed up, in the shortcut menu that appears, select "all tasks"> "backup database" to open the "SQL Server backup" dialog box, as shown in Figure 4-24. (2) In the "backup" text box on the "backup" tab of the "SQL Server backup" dialog box, enter the backup name, and enter the backup description in the "Description" text box. Then, under the "backup" option group, select the Backup Type (full or different). Under the "purpose" option group, click the "add" button to open the "select backup destination" dialog box, select a backup file or device, as shown in Figure 4-25. Figure 4-24 SQL Server backup dialog box Figure 4-25 select backup purpose (3) under the "Override" option group, you can select the "APPEND to media" option, you can append a backup to any existing device on the backup device, or click the "Overwrite existing media" button to overwrite any existing backup on the device. (4) If you need to perform regular backup operations, select the "Schedule" check box and click the button. The "Edit scheduling" dialog box is displayed, as shown in Figure 4-26, you can set the backup time in this dialog box. (5) You can select the "options" tab for further settings, as shown in Figure 4-27. Figure 4-26 "Edit scheduling" dialog box Figure 4-27 SQL Server backup dialog box in the "options" option group, use "to verify that the backup will read the entire backup and check the integrity of the media ......" The following check box can be set as follows. ● Check the "verify backup after completion" checkbox to check the backup during the backup operation. ● "Pop Up tape after backup" check box: Pop Up tape after backup is complete. This option applies only to tape backup settings. ● Check the media set name and backup set expiration time check box: Check the Backup Settings to prevent unexpected backup Overwrite Events. In the media set editing box, enter the name of the device used for the backup operation. If you are using the backup device media for the first time or want to change the ID of the existing backup device media, select the "initialize and identify media" check box under the "media set tag" option group, to enter the media Set Name and description. (6) After completing all the settings, click "OK" to start backup. In addition, you can use the Create Database Backup Wizard to back up the database. The procedure is as follows: (1) Open the Enterprise Manager and log on to the specified database server. (2) In the "Tools" menu, select the "Wizard" command, the dialog box shown in 4-28 appears. (3) Expand the "manage" node in the dialog box. (4) double-click the backup wizard option. The Wizard box shown in 4-29 is displayed. Figure 4-28 select wizard dialog box Figure 4-29 create a database backup wizard (5) follow the wizard prompts to complete database backup operations.
1.7.2 Restore databaseAfter the database is backed up, once the system crashes or an incorrect database operation is performed, the database can be recovered from the backup file. Database recovery refers to the process of loading the database backup to the system. The system automatically performs security checks, reconstructs the database structure, and completes the database content during database recovery. Follow these steps to restore the database. (1) Start the Enterprise Manager, log on to the specified database server, open the database folder, right-click the database icon to be backed up, select "all tasks"> "Restore database" from the shortcut menu, and the "Restore database" dialog box is displayed, as shown in Figure 4-30. Figure 4-30 "Restore database" dialog box (2) Select the database to be restored from the "Restore to Database" drop-down list, and select the corresponding Database Backup Type in the "Restore" recovery group, select the "from device" button. Then, click Select device. The dialog box shown in 4-31 is displayed. Figure 4-31 select "Restore device" in the "restore from" option group and select "disk" or "tape" to specify the backup device used. If no backup device is displayed, click Add to add an existing backup device or create a new backup device. (3) Select the "options" tab, as shown in Figure 4-32. You can set other options. Figure 4-32 "options" tab (4) After the settings are complete, click "OK" to complete the operation.
