Ms SQL Server 2000 administrator manual series-33. Restore and reply to databases

33. Restore and reply to the database
Restoration Method
Execute database Restoration
Plan serious damaged responses
Summary
In Chapter 32nd, we learned the importance of system backup and how to perform backup. This chapter continues the description of database protection and establishment in the previous chapter. We will learn how to restore the database, how to reply to the system, and how the database replies. As you can see, the backup execution type affects the reply process. In addition to learning about restoring and replying to databases, we will also introduce log shipping ). Transfer Record Files is a new feature in Microsoft SQL Server 2000 that allows you to create backup copies on another server with original server transaction records.
________________________________________
Description
Some DBAs mentioned that the process of restoring and replying to a database is called recovering the database. However, these programs are quite different. Chapter 2 illustrates the differences between restoring a database from backup and restoring the database from SQL Server. Under any circumstances, bringing the database back to the status before the system fails is the primary goal of backup, restore, and reply operations.
________________________________________
Restoration Method
　
As mentioned above, the Backup Type affects the nature of the Restoration Operation. In this section, we will learn how to restore from full backup, differential backup, and transaction record backup.
Restore from full backup
　
Restoring a full backup is a simple and direct process: you only need to use SQL Server Enterprise Manager or transact-SQL (tsql) commands to restore the backup file. This chapter describes how to use these two methods later. If you plan to recover from differential backup after the full backup, you only need to confirm that there is a recent transaction record backup, as described in <recovering from transaction record backup> next to this chapter, you can specify the norecovery option during restoration.
________________________________________
Description
The recovery operation has an important feature, that is, the rediscovery option. It command SQL Server to use online transaction records to reply to the database after restoration. If you want to use differential backup files or transaction history files, make sure you have specified the norecovery option.
________________________________________
Restore from differential backup
　
To restore from a differential backup, you must first restore from the full backup, and then restore all differential backup files after the last full backup. Remember, differential backup is used to back up the information changed after the last full backup or differential backup. Are you sure you are using the norecovery option, unless you are restoring the last backup file, you can use the recovery option to restore the last backup file. If the transaction record file is restored in addition to differential backup, the current record file must be backed up and all changed record files must be applied at the same time, as mentioned in the next section.
Restore from transaction record file backup
　
Performing the reply operation will return the database status to the point before it becomes invalid. First, restore the data file and then restore the changes to the database since the backup starts. You can store backups of all transaction history files that have expired to restore these changes.
To ensure that no latest transactions are lost during the restoration of these record files, you must first save the current record files. If you forget to save the current record file, most of the latest changes recorded in the record file will be lost because the Restoration Operation overwrites the transaction record.
To use a transaction record file to restore the database to the State before it expires, follow these steps (these are based on the technology learned in Chapter 32nd ):
1. Use the no_truncate option to back up the currently used transaction record files.
2. Restore the latest full backup.
3. Restore all differential backups and restore the database to the final backup status.
4. Restore all transaction history file backups since the last differential backup to reset all transactions that have occurred since the last backup.
5. Restore the backup of the transaction record file created in step 1 to bring the database back to the state before the failure.
Restore the database in bulk_logged reply Mode
　
If you are executing a database in bulk_logged reply mode, you must perform another record operation when restoring the database. These operations include select... into, bulk copy, BCP, and some create index operations, as well as text operations that have been discussed in the previous chapter. If you think the content mentioned here is too troublesome, do not execute the database in bulk_logged reply mode.
Execute database Restoration
　
Use Enterprise Manager or T-SQL commands to perform a restore operation -- the results are the same in both ways. Unlike the backup operation, SQL server does not provide the Restoration Operation wizard.
Restore using Enterprise Manager
　
To use enterprise manage R for restoration, follow these steps:
1. In Enterprise Manager, click the right button on the name of the database to be restored, select all jobs from the shortcut menu, and then select Restore database to go To the Restore database dialog box, as shown in 33-1.
　
 
Figure 33-1 "general" tab in the "Restore database" dialog box
2. At the top of the General tab is to restore to a database drop-down list, which can specify the type of database to be restored for this backup. Figure 33-1 shows that the example database is selected.
Here we do not need to use the same name to restore the database-in fact, we should use another database name. For example, assume that a user accidentally deletes a data table and wants to restore the complete database, the data of all users is restored to earlier data. However, you can actually restore the data to the renamed database, retrieve the deleted data table, and insert the data table into the database that is still working.
3. specify the type of Restoration Operation: database, archive group, or archive or source device. The database option can be used to restore a database. The archive group or archive option specifies the restored archive group or archive. The Source Device option specifies the restored device. The device content determines the type of the restored device. Figure 33-1 shows that the database option is selected.
4. the parameter area allows you to set whether to display the backup of other databases (from the backup of another database) and which backup should be restored first (if multiple available backups are set ), whether the restoration time point should be executed. Data can be restored at a specific time point. For example, if a data table is accidentally deleted at, you can use the restoration time to restore the database at, just before the deletion. Because all available backup lists are available, you can select the backup to be used. If the latest backup is not forcibly restored, all the backups of the database are available.
In the "Restore database" dialog box, select a group of backups and click "properties" to view the content. The backup Set Properties window is 33-2.

　
 
Figure 33-2 backup Set Properties window
5. Select "OK" and return to the General tab of the "Restore database" dialog box. Select an archive group or archive to display a different picture, as shown in 3-3. Figure 33-3 shows all archives and archive group backups in the example database. To view the attributes of these archives and archive group backups, select an archive or archive group and select attributes.
　
 
Figure 33-3 General Tab Of the Restore database dialog box after selecting the archive group or archive Option
6. Now select the source device, as shown in 3-4. This option is required when a special backup device for restoration is selected. You must manually select this set of backups, and then specify whether SQL Server should perform full restoration, differential restoration, transaction record restoration, or archive and archive group restoration. SQL Server can also read the backup information and store the information together with other backup history information in the MSDB database. If you want to restore the database, the backup information can be used.
7. select the option tab of the "Restore database" dialog box, as shown in Figure 33-5. Three check boxes are displayed at the top of this tab. After the backup is restored, you can exit the inserted tapes check box one by one to ensure that the tapes are not overwritten on the tape drive. Before selecting "Restore backup", you will be prompted with the option to change and execute the backup. Select the force restore check box to overwrite the existing database with the restored database. On this tab, you can restore the database under a new data file name, which is useful for retaining the original database.
　
 
Figure 33-4 General Tab Of the Restore database dialog box after selecting the source device Option

　
 
Figure 33-5 option tab of the "Restore database" dialog box
Other options in the tab can specify the status of the database after the reply is completed, as shown below:
O maintain database operation. The option of not restoring additional new transaction records does not allow further differential recovery or transaction record restoration. In essence, the recovery mark is set in restoration. If this option is selected, the transaction record file backup cannot be restored.
　
O makes the database not operational, but can also restore the additional new transaction records. This option sets the norecovery flag in restoration. With this tag set, further differential Backup Recovery and transaction record file backup recovery can be applied. During the restoration process, the database is in an operational state, which means that the user cannot access the database before the restoration is complete.
　
O maintain the database as read-only, and you can restore additional new transaction records. This option also sets the norecovery flag in restoration to execute differential Backup Recovery and transaction record restoration. Unlike the previous option, users can read-only access the database during restoration.
　
8. After setting the options, select OK to enable the restoration operation. You can view the restoration process in a message box, as shown in 3-6. After the restoration operation is complete, a status box is displayed, notifying you that the restoration is successful or fails.
　
 
Figure 33-6 "Restore progress" message box
________________________________________
Description
The SQL Server backup device supports multiple outputs for different backup operations. And vice versa: A single backup can be distributed across multiple devices. The contents of multiple backup device groups form a backup set ). Therefore, a backup device (tape, disk files, etc.) can support multiple backup sets of different databases or multiple backup sets of the same database.
________________________________________
Restore with T-SQL
　
Restore T-SQL commands are similar to backup commands (discussed in chapter 32nd ). Like the BACKUP command, it may be quite difficult to use it for the first time, but some DBAs prefer to place their management programs in the SQL command file to facilitate repeated execution of these functions. Like the BACKUP command, the restore command provides more additional options than Enterprise Manager.
In this section, we will discuss the syntax of the restore command and the various options provided by this command. The restore command can be displayed as follows:
• Restore database restores a complete database, archive, or archive group.
　
• Restore log restores transaction record files.
　
As you can see, the commands used depend on the type of the restore operation. Because these commands share most of the options, we will discuss all the options for these two restore types (databases and record files) in a list later.
Restore statement
　
The restore statement syntax is as follows:
Restore database database_name
[From backup_device]
[With options]
This statement only requires the database file name and the backup location.
The declarative syntax for restoring an archive and archive group is as follows:
Restore database database_name
[File = file_name]
[Filegroup = filegroup_name]
[From backup_device]
[With options]
This statement only requires the database name, file name, file group name, and backup location.
Statement syntax when transaction records are restored:
Restore log database_name
[From backup_device]
[With options]
Just like other commands, database_name is the name of the database to be executed. The backup_device parameter can be the name of the logical backup device or physical device. To specify a physical device, you must specify its device type-that is, disk =, tape =, or pipe = must be added before the device name. One or more devices can be specified, separated by commas.
________________________________________
Description
If the form clause is not provided, restoration cannot be performed, and a reply is still executed (unless the norecovery option is executed ). This technology can be used to set the database to the reply mode without restoring additional data. For example, you can execute multiple differential restoration operations and then execute the restore statement. You can start the reply process without the form clause setting the database to the reply mode.
________________________________________
Option
　
Table 33-1 lists the available restore commands. As you can see, these options provide a lot of flexibility when performing a restore operation (if the listed options are only useful for transaction record restoration, they will be marked as restrictive ).
Table 33-1 restore Command Options
Option description
Restricted_user sets the security of the newly restored database. Only members of the db_owner, dbcreater, and SysAdmin roles can access the database.
File = file_number if there are multiple backup sets in the stored media, it can be used to identify the used backup set. For example, if this value is set to 2, the second backup set on the tape is used.
Password = PASSWORD specifies the password used to store the backup.
Medianame = media_name specifies the media name.
Mediapassword = PASSWORD specifies the password for storing media.
Move 'logical _ file_name'
To 'OS _ file_name' to change the position of the file to be restored, for example, move 'northwind 'to 'd:/data/northwind. MDF '. If the old disk cannot be used and you need to restore it to a new disk, you can use this option.
Norecovery | recovery |
Standby = undo_file norecovery specifies that after restoration, the transaction cannot be returned or re-processed. This option is required if you want to restore other backups (differential backup or transaction record file backup. Revovery is the default option, which specifies to perform the reply operation and return each unsubmitted change. Standby specifies that an undo file is created when restoration is required.
Keep_replication specifies that the replication settings should be saved when the database is restored.
Nounload | unload nounload specifies that the stored media will not be detached after restoration (for example, overwriting the backup tape and popping up ). Unload is the default option, indicating that the storage media will be detached after the restoration is complete.
Replace indicates that SQL server will restore the data files even if they already exist. The existing files will be deleted and overwritten. If replace is not specified, SQL Server checks whether database_name already exists. If it already exists, the restoration operation is terminated. This security feature helps avoid unintentional overwriting of working databases.
Restart specifies that the SQL server restarts the restoration operation after the restoration operation is interrupted.
Stats [= percentage] displays a message after the Restoration Operation completes the specified percentage. This option is useful if you need to monitor the operation process.
Partial specifies to execute partial restoration.
Stopat = date_time
(Log restore only) specifies the data status at the specified time point when the database returns to data_time (only valid for record file restoration ).
Stopatmark = 'mark' indicates that the restoration operation is performed at the mark.
Stopbeforemark = 'mark' specifies that the restore operation is performed before the flag.
________________________________________
Description
Named transactions is a new feature in SQL Server 2000. They are created using the mark_name option of the begin transaction... with Mark command, which features the stopatmark and stopbeforemark functions in the restore command.
________________________________________
________________________________________
Use restore in the real world
Here are a few examples of using the restore T-SQL command.
This statement restores the data file of the example database:
Restore database example
From backup_dev_1, backup_dev_2
With
Norecovery,
Stats = 5
Go
This statement restores the transaction record file of the example database:
Restore log example
From backup_dev_3, backup_dev_4
With
Norecovery,
Stats = 5,
Unload
Go
Like the restoration result, the output displays the percentage of Operations completed. How many pages are restored, how much time is taken to restore, and how fast the restore is performed (MB/sec ).
Now you can use the following command to reply to the database:
Restore log example
With recovery
Go
You will see the restoration statistics again.
________________________________________
Plan serious damaged responses
　
It is not enough to simply cluster servers (as discussed in chapter 12th) or use RAID (as discussed in chapter 5th) to improve the normal operating time of the system. You must make a response plan before serious damage occurs. It is important to be familiar with how to back up and restore an effective database when necessary, but you must be prepared to re-build the system from nothing when necessary. Preparation includes file management and planning. In addition, the new SQL Server 2000 restoration function should be used to provide sufficient response capabilities. This function allows transaction record files to be moved from the primary system to the backup system.
As a DBA, you should plan a plan with the maximum working time. This plan should include the following components:
• Record the current configuration file
　
• Build a fault tolerance environment
　
• Prepare real-time replies
　
• Record database recovery plan files
　
Most of the steps involve planning and archive management. Generally, the reply plan is not recorded, and only the developer of the plan can execute the plan. Once a specific member fails to appear, serious damage will occur.
________________________________________
Description
Confirm the records of critical damage recovery plan files and assign alternate personnel to execute the plan when you are unable to be present in the event of severe damage.
________________________________________
Record current configuration file
　
If the current configuration creation step is not well documented as a file, problems may occur when the system needs to be re-constructed or new hardware is added to the system. Record the current configuration file to rebuild, reinstall, and reset the system more quickly. Confirm that the following information is included:
• Hardware configurations include hardware types and quantities, RAID configurations, and other options.
　
• Installed software products include complete information about all software installed on the server.
　
Establish fault tolerance environment
　
As shown in chapter 5th, the use of fault-tolerant raid disk areas can greatly reduce the possibility of system failure. A disk is a mechanical component and is worn out like an object. Using a fault-tolerant disk area can reduce the trouble of re-installing the operating system, SQL Server, and rebuilding the database from backup, the reconstruction process is a time-consuming and labor-consuming process (mainly in terms of income loss during labor and system failures ).
If the fault-tolerant disk is damaged, replace it as soon as possible. There will always be disk damages, and some fault tolerance methods can continue to work if only one disk is damaged. You can prepare a disk for backup at any time.
Prepare real-time reply
　
Prepare to reply to the database as necessary. Backup should be stored elsewhere to prevent disasters such as fire or flood, but you should keep the latest backup at hand, because you may not be able to quickly get the backup from other places. Backup staging area is the most popular method. It can save the latest backup on the workstation and improve the efficiency of backup and restoration. The backup phase area stores the latest database and transaction record file backups on a disk on the network. When restoration is required, you do not need to use tape or back up data from storage far away from the workstation-restoration can be performed immediately. This technology is useful if you need to restore immediately. However, because you must use hardware resources to store backups, it has a high hardware requirement. You must consider the budget and demand to determine whether or not you need it.
Record database recovery plan files
　
Because you may not be on site when the database must reply, you should carefully record the database reply plan and share the information with other members, in this way, they can reply to the system as necessary. If it takes a long time since the last database restoration, the database recovery plan file will also be a valid memo manual. In any event, the manual copy of the database response plan can make the restoration process more stable.
Transfer Record Files
　
The transfer of record files feature in SQL Server 2000 allows you to apply the latest transaction record files to the backup system to create a backup system and update it at any time. The standby system maintains the parameter reply mode and keeps applying the transaction record file to it. The system can perform read-only queries even in the reply mode. Therefore, you can use the standby system to remove report tasks.
In the primary server failure event, the backup server can be easily and quickly connected to the new server. This feature has been used by many users in SQL Server 7 and is also supported in Microsoft. Some useful features are also added. These features include the ability to start and maintain the standby system through the database maintenance plan wizard and the SQL Server Agent.
Summary
　
The discussion on database backup, restore, and reply operations ends now. In these two chapters, you have learned how to effectively back up and restore the system, and how SQL server replies work, you also see how to perform these operations using Enterprise Manager and T-SQL commands. This chapter focuses on the reply operation and preparation of the serious damage response plan. Remember, system security is one of the main responsibilities of DBA.