MssqlServer database backup and restoration (2)

This document explains how to back up and restore data, and describes some key options for backup and restoration.

This document explains how to back up and restore data, and describes some key options for backup and restoration.

Database Backup options

When backing up a database, You Need To Know several backup options, such as overwriting media and transaction logs. When talking about the coverage media, you must first understand this concept, otherwise you will not be able to talk about it.

● Media set: an ordered set of backup media (tape or disk files). One or more backup operations are performed on a fixed type and quantity of backup devices. The number of backup devices used by the media set determines the number of media clusters in the media set. For example, if a media set uses two non-image backup devices, the media set contains two media clusters. After a media set is created, subsequent backup operations append its backup set to the media set in sequence.

● Media family: A backup created on a single non-image device or a group of Image devices in a media set.

● Backup set: A backup set is added to the media set for successful backup operations.

After learning about the above concepts, return to the backup options:

◆ Back up to existing media set-append to existing backup set

If you want to back up the database to a disk, select a disk (you can select one or more disk drives or a tape drive device. Here, take the disk drive as an example ), if you keep the default [back up to existing media set-append to existing backup set], SQL Server automatically creates a media set (each volume of the backup media (Disk File or tape) contains a media header, the Media Header is created when a backup operation is performed on a tape (or disk) for the first time, it includes the media name, unique ID of the media set, unique ID of the media cluster, and so on), and then adds the backup set to the media set. For example, in the first full backup in the previous article, SQL Server automatically creates a media set, adds the backup set to the media set, and then backs up the transaction log file, this transaction log file backup set will be appended to the existing backup set. What we can see in the final backup file is a backup file MyTest. bak, but after this file is selected during restoration, the list of backup sets to be restored displays the backup sets that have been backed up successively, such as the complete backup set, the first transaction log backup set, and the second transaction log backup set.

◆Back up to existing media set-overwrite all existing backup Sets

This option overwrites all backup sets on the media and retains the Media Header (if any ). If no Media Header exists, a header is created. Overwrite the backup, retain all existing media headers, and create a new backup as the first backup on the backup device.

If any of the following conditions exists, the backup media is not overwritten:

● The existing backup on the media has not expired. (If SKIP is specified, no expiration check is performed .)

The expiration date specifies the backup expiration date and can be overwritten by another backup. You can specify an expiration date when creating a backup. By default, the expiration date is usedSp_configureThe media retention option is determined.

● The media name (if any) does not match the name on the backup media.

● The media name is a descriptive name used to easily identify the media.

To sum up, the biggest difference between them is that one is to append a backup set to the current backup set, and the other is to overwrite the existing backup set, that is, the information previously saved in the backup set cannot be read again.

: Select "APPEND to existing backup set" When backing up. The backup set to be restored is displayed in the backup set to be restored.

: During backup, select overwrite all existing backup sets. During restoration, the backup sets to be restored are overwritten, and the previously appended backup sets are overwritten.

◆Check media set name and backup set expiration time

: The name of the Backup set and the expiration time of the Backup set. Generally, the backup set has a name by default. You can change it to a meaningful name if necessary. The expiration time is always the default value.

Now, the [Check media set name and backup set expiration time] will show you how to input it. I have not tried this experiment, and there is no need to experiment one by one.

◆ Back up to the new media set and clear all existing backup Sets

Select this option to clear the previous backup set and use the new media set to back up the database.

To create a new media set, you must format the backup media (one or more tape or disk files ). The formatting process changes the backup media as follows:

● Delete the old header (if any) to effectively Delete the previous content in the backup media.

The formatting tape device deletes all previous contents of the currently loaded tape. Formatting a disk only affects the files you specified for the backup operation.

● Write a new media header to the backup media (tape or disk file) in each backup device.

◆ Reliability

● Verify the backup after completion.It verifies whether the backup set is complete and whether all the volumes are readable.

●Check the checksum before writing to media. The Checksum will be verified before the backup media is written. If this option is selected, it may increase the workload and reduce the backup throughput of the backup operation.

●Continue when an error occurs. If an error occurs during database backup, the backup will continue.

◆Transaction Log(This option is activated only when the Backup Type is set to transaction log during Backup)

● Truncate transaction logs. If this option is selected, transaction logs are backed up and truncated to release more log space. The database is online.

●Back up the tail of the log and restore the database. If this option is selected, the tail of the log is backed up and the database is restored. This option is used to back up logs that have not been backed up. This option is useful when a fault is transferred to a secondary database or when it is done to prevent loss of work before the restoration operation. After this option is selected, the database cannot be used until the database is completely restored.

◆Tape drive. Select this option when backing up a database from a tape drive device.

Backup-related options. MSDN has a very detailed document, which is recommended here. After reading this document, I will have a deep understanding of backup storage.. Http://msdn.microsoft.com/zh-cn/vcsharp/ms178062.aspx

Database restoration options

◆ Overwrite existing database check box: select this option to overwrite all existing databases and related files, including existing databases or files with the same name.

◆ [Retain replication settings] Check box: If this option is selected, the replication settings will be retained when the published database is restored to a server other than the one that created the database. However, this option can be used only after you select the roll back uncommitted transaction to make the database available.

◆ [Restrict access to restored databases] Check box: db_owner,Dbcreator, OrSysadmin. "> Make the restored database available onlyDb_owner,DbcreatorOrSysadmin.

Db_owner,Dbcreator, OrSysadmin. ">Recovery status:

Db_owner,Dbcreator, OrSysadmin. "> ◆Restore with recovery: Roll Back uncommitted transactions to make the database available. Other Transaction logs cannot be restored.This option enables the database to enter a normal state after restoration, and automatically restores unfinished transactions. If this restoration is the last step of restoration, you can select this option. This item is the default option.

Db_owner,Dbcreator, OrSysadmin. "> ◆Restore with norecovery does not perform any operations on the database and does not roll back uncommitted transactions. Other Transaction logs can be restored.After restoration, the database still cannot be used normally and the unfinished transaction operations are not restored. However, you can continue to restore the transaction log backup or differential backup, restore the database to the nearest current state.

Db_owner,Dbcreator, OrSysadmin. "> ◆Restore with standby to make the database in read-only mode. Undo uncommitted transactions, but save the Undo operation in the backup file to reverse the recovery effect.When this option is selected, the Directory of the backup file will be activated. This operation restores the uncompleted transactions after restoration and keeps the database in read-only status. To continue restoring the transaction log backup, you must also specify a restore file to store the recovered transaction content.

Db_owner,Dbcreator, OrSysadmin. "> By now, the database backup and restoration options have been basically described. You can directly understand the missing options by name. As for the database backup and restoration script, the MSDN document has syntax and examples. You can also set the UI to export the standard script.

The above is my understanding and summary of SQL Server database backup and restoration. If any missing details or explanations are incorrect