33. Restore and Reply Database
Reduction method
Enforcement database restore
Planning serious damage to reply
Summary of this chapter
In Chapter 32, we have learned the importance of system backup and how to perform backups. This chapter will continue to continue the instructions for the protection and establishment of the database mentioned in the previous chapter. We will learn how to restore the database, how to reply to the system, and the operation mode of the database reply. As you can see, the type of backup execution affects the reply. In addition to learning about restore and reply libraries, record file transfer is also introduced. Record file transfer is a new feature in Microsoft SQL Server 2000 that allows you to create a backup copy on another server with the original server transaction record.
Description
Some DBAs refer to the process of restore and replying to the repository called Recovering The Database. However, these programs are large. Chapter 32 illustrates the difference in reply from the backup restore database and the SQL Server reply. In any case, the status of the database back before the system failure is the main target of the backup, restore, and reply operation.
The reduction method is as previously mentioned, and the type of backup affects the nature of the restore operation. In this chapter we will learn how to perform restore from full backup, differential backup, and transaction record backup. Restore from full backup
Restore from full backup is a fairly simple and straightforward process: just restore backup files with SQL Server Enterprise Manager or Transact-SQL (TSQL) command. This chapter will have the instructions for the use of these two methods later. If after the full backup, you intend to restore from a differential backup, just determine that there are recent transaction record backups, such as this chapter, from the Transaction Record Backup Restore> Chapter, and specify the Norecovery option when performing restore.
Description
The restore operation has an important feature, which is the recovery option. It commands SQL Server to reply the database with a connection transaction record after restore completion. If you want to use a differential backup file or transaction documentation, you can remember to determine that the Norecovery option has been specified.
Restore from differential backup
To restore from a differential backup, you must restore from a full backup, then restore all differential backup files after the last full backup. Remember, differential backups are used to back up information that has been changed after the last full backup or differential backup. Determine that the Norecovery option you are using unless you are restoring the last backup file, and restore the last backup file can be used with the Recovery option. If restore is performed from the transaction record file outside of the trading record file, you must simultaneously back up the current record file and apply all changes, which will be mentioned in the next section.
Restoring the reply operation from the transaction record file will return the status of the database to the point before the failure, first restore the information file, and then restore changes to the database from the startup backup. You can store all trading record files for all failures to restore these changes. To ensure that no latest transactions are lost during the restore these record files, the current record file must be saved first. If you forget to save the current record file, you will lose most of the latest changes in the record file due to the restore operation overwriting the transaction record.
To use the transaction record file to restore the database to the state before the failure, please follow the following steps (these are based on the technology you have learned in Chapter 32):
Back up the transaction record files currently used using the NO_TRUNCATE option. Restore the most recent backup. Restore all differential backups, restore the database to the final backup status. Restore all transaction record files from the last difference backup to reset all transactions that have occurred since the last backup. Restore the transaction record backup established in step 1, so that the database returns to the state before the failure.
Restore database in Bulk_logged reply mode
If you are executing a database in Bulk_logged reply mode, you must make a recording operation when you need to restore. These operations include SELECT ... INTO, BULK COPY, BCP, and some Create INDEX operations, and text operations that have been talked out in the previous chapter. If you think that the content talked here is too trouble, you can perform a database in Bulk_logged reply mode. Restore Restore Use Enterprise Manager or T-SQL command to perform restore operations - the result of these two modes is the same. Unlike backup operations, SQL Server does not provide a restore operational wizard. Use Enterprise Manager to perform Restore To use Enterprise Manage R to perform a restore action, follow these steps:
In ENTERPRISE Manager, press the right button on the database name to be restored, select all work on the quick function table, then select Restore Database, enter the restore database dialogue, as shown in Figure 33-1.
Figure 33-1 "A General" page sign in the "Restore Database" dialogue
At the top of the general tab, it is to restore a library drop-down list, which specifies the database type that this backup will be restored. Figure 33-1 shows the selected Example database. Here is not required to use the same name to restore the database - actually, you should use another database name. For example, suppose a user unexpectedly deletes a data sheet, want to restore a complete database, then restore all people's data to an earlier information. However, it can actually restore the data to renamed databases, remove the deleted data sheet, and insert this information table into the database still working. Then specify the type of restore operation: a database, a file group or a file or source device. The database option specifies the restore database. Archives Group or Archive Options You can specify a set of file groups or files. The source device option specifies the reduced device, and the content of the device will determine the type of restore. Figure 33-1 shows the selected database option. The parameter area allows you to set whether you want to display a backup of other repository (restore from another database), you should still restore which backup (if multiple available backups) is set, there should be a restore time Point. The restore time point can restore the data to a specific time. For example, if you accidentally delete a data sheet at 12:01, you can use the restore time point to restore the database of 12:00, just before the deletion occurs. Because there are all available backup lists, you can choose the backup you want to use. If there is no forced restore the latest backup, all backeys of the database are available. You can select a set of backups in the restore database dialogue, then select the Properties button to view the content. Backup Set Properties Window is shown in Figure 33-2.
Figure 33-2 Backup Set Property Window
Select OK, return the general tab of the restore database dialogue, and select a file group or file to display a different screen, as shown in Figure 33-3. Figure 33-3 Displaying all archives and archive group backups in the Example database. To view the properties of these archives and archive group backups, select the file or profile group and select Properties.
Figure 33-3 After selecting the archive group or file option, restore the general tab of the database dialogue block
Now select a source device, as shown in Figure 33-4. This option is required when electing a reduced special backup device. This set of backup must be manually selected, then specify whether SQL Server should perform full restore, difference restore, transaction record restore or archive and archive group restore. You can also let SQL Server read backup information and store them in the MSDB database with other backup history information. If you want to execute a database restore, these information about backup can be used. Select the option page check for the restore database dialogue, as shown in Figure 33-5. At this page, three check squares will be seen. After backup restoration, exiting the inserted tape Nuclear Net One by one ensures that the tape will not be overwritten in the tape drive. Before you choose to restore the backup, you will first prompt me to provide chances to change the execution of the backup. And choose to enforce the existing database to restore blocks to overwrite the existing database with the restore database. In this tab, you can restore the database under a new data file, which is useful for retention the original database. Figure 33-4 After selecting a source device option, restore the general page sign of the database dialogue block.
Figure 33-5 Restore database dialogue option page
Other options in the tab can specify the status of the database should be kept after the reply is completed, as follows:
Keep the database operation, no additional new transaction records this option does not allow further differences or transaction record restoration. It is essentially a Recovery mark in the restoration. If you choose this option, you cannot restore the transaction record file backup. Keep the database does not work, but you can also restore additional new transactions. This option sets the Norecovery tag in the restore. This tag is set, and further differential backup restore and transaction record file backup restore is available. During the restore, the database is unaffected, which means that the user cannot access the database before completing the entire restore. Keep the database as the state of reading, and you can restore additional new transaction records. This option is also set in the restore, which can perform a differential backup restore and transaction record restore. And the previous option is that the user can read the database only when performing a restore operation.
After completing the setting option, select OK to turn on the restore operation. The process of restore operations can be understood by a message, as shown in Figure 33-6. When the restore operation is completed, a state block will be seen, and the notification is restored to success or fail.
Figure 33-6 "Restore Progress" message
Note The SQL Server backup device supports the output of multiple different backup operations. Vice versa: Single backup can be dispersed into multiple devices. The content of multiple backup device groups forms a backup called backup set. Therefore, a backup device (tape, disk file, etc.) can support multiple different database backup sets or backup sets of multiple same databases.
Use T-SQL to perform restore
Restore T-SQL commands and backup commands are similar (chapter 32). Like the Backup command, it may be quite difficult when used for the first time, but some DBA still likes to place their management programs into the SQL command file to facilitate the execution of these features. 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 a variety of options provided by this command. There are two forms of the restore command to display 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 should be determined according to the type of implementation of the restore operation. Because these commands share most of the options, we will later discuss all options for these two restore types (database and record files) in a list. RESTORE statement When the full database is restored, the RESTORE statement is as follows: RESTORE DATABASE DATABASE_NAME
[From backup_device]
[With options]
This statement only requires the file name and backup of the database. The grammatical grammar when performing the file and archive group restore 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, the file name, or the location of the archive group name and backup. The grammatical grammar when performing the transaction record is as follows:
RESTORE log database_name
[From backup_device]
[With options]
Just like other commands, Database_name is the name of the database that is restored to be executed. The Backup_Device parameter can be the name of the logical backup device name or entity device. If you want to specify an entity device, it must define its device type - that is, the device name must be added to DISK =, TAPE = or PIPE =. You can specify one or more devices, separated by commas in the middle.
Note If the Form clause is not provided, the restore will not be performed, and the reply will still be executed (unless the Norecovery option is executed). This technology can be used to set the database to reply mode without restoring other additional information. For example, multiple differential restore operations can be performed, then execute RESTORE statements, do not require the form clause to set the database to reply mode, you can start the reply process.
Option Table 33-1 lists the options available to the restore command. As you can see, these options provide a lot of elastic space (if the options listed only for transaction records).
Table 33-1 RESTORE command options
Option Description RESTRICTED_USER Sets the security of the new restore repository, only DB_OWNER, DBCREATER, and SYSADMIN role members can access the database. File = file_number If there are multiple backup sets in the storage medium, it can be used to identify the backup set used. For example, the value is set to 2, then the second backup set on the tape is used. Password = password specifies the password that stores the backup. Medianame = media_name Specifies the name of the media. MediaPassword = password specifies the password of the store. Move 'logical_file_name' to 'os_file_name' Change the location of the restore file, for example: move 'northwind' to 'd: /data/northwind.mdf'. You can use this option if the old disk is not available, and you need to restore it on a new disk. Norecovery | Recovery | StandBy = undo_filenorecovery Specifies that the transaction will not be returned or re-performed after restore. This option is required if restored other backups (differential backups, or transaction record files) will be used. Revovery is a preset option, which specifies to perform a reply operation and returns the change that is not submitted. Standby specifies that the restore (UNDO) file is established in the case of restore needs. Keep_Replication Specifies the copy setting to be saved when the database restore is restored. NOUNLOAD | UNLOADNOUNLOAD specifies that the storage medium is not unloaded after the restore is complete (for example, overridden backup tapes and pop up). Unload is the preset option, specifies that the storage medium is uninstalled after the restore is completed. Replace pointed out that even the data file already exists, SQL Server will still restore these files. The existing file will be deleted and overwritten. If you do not specify Replace, SQL Server will check if Database_name already exists. If there is already, the restore operation will be terminated. This security feature helps avoid unintentionally overwriting the restore database. Restart Specifies that SQL Server restarts the restore operation after the restore operation is interrupted. Stats [= percentage] Displays the message after the reduction operation completes the specified percentage. This option will be useful if you need to monitor the operation. Partial specifies the execution part restore. Stopat = date_time (log restore "Specifies the status status of the database reply to the DATA_TIME specified time (only the record file is restored). Stopatmark = 'mark' Specifies the restore operation to the tag. Stopbeforemark = 'mark' Specifies the restore operation before the tag. Named Transactions is a new feature in SQL Server 2000. They are built using the command beac transaction ... with Mark's mark_name option, which is available in the RESTORE command to use the StopatMark and StopBeForemark feature.
Real world Use Restore to have several examples of using the Restore T-SQL command. This statement reduces the information file of the Example database:
RESTORE DATABASE EXAMPLE
From backup_dev_1, backup_dev_2
WITH
Norecovery,
Stats = 5
Go
This statement reduces 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 result of the restore, the output will display the percentage of the operation. How many pages will be notified, how much time is reduced, and the reduced speed (MB / sec) will be reduced. You can now reply to the following command repositories:
RESTORE LOG EXAMPLE
With recovery
Go
You will again see the statistics for restore operations.
The reply of planning serious damage is only a simple clustering server (discussed in Chapter 12) or using RAID (Chapter 5) to improve the normal working time of the system. You must do a good reply before you seriously damage. It is very important to familiarize how to perform a valid database when necessary, but it is still ready to start reconstructing the system from one if necessary. Preparation includes file management and planning. In addition, use the functions restored in the new SQL Server 2000 to provide sufficient reply. This feature allows transaction record files to move from primary systems to backup systems. As a DBA, a plan for a maximum working time should be planned. This plan should include the following components: Record the current configuration file establishment fault tolerance environment Preparing instant reply to record the database Reply Plan file Most steps involving planning and archive management. Usually replied is not recorded, and only planned developers can perform this plan, once this particular member is not able to appear, there will be severe damage.
Description Confirm the serious damage to reply plan files, and assigning candidates when they are seriously damaged, you cannot perform the planning plan when you are in the event.
