SQL Server database backup and restore awareness and summary (ii) _mssql

Through the SQL Server database backup and restore knowledge and summary (i), I believe you have a more in-depth understanding of data backup and restore, in the above I do not have to do the transaction log analysis, this recommendation Song article, the transaction log did a more detailed explanation: http:// Www.jb51.net/article/31038.htm. This article will focus on some of the key options for backup and restore for data backup and restore explained above.

Database backup Options

When backing up a database, there are several backup options that need to be understood, covering media, transaction logs, and so on. When it comes to covering media, you have to understand the concept before you can talk about it.

Media set: An ordered collection of backup media (tape or disk files) that has been written to one or more backup operations using a fixed type and number of backup devices. The number of backup devices used by the Media Gallery determines the number of media clusters in the media set. For example, if a media set uses two non-mirrored backup devices, the media set contains two media clusters. Typically, after a media set is created, subsequent backup operations append their backup sets to the media set in turn.

Media family: A backup created on a single, non-mirrored device or a set of mirrored devices in a media set.

Backup set: A successful backup operation adds a backup set to the media set.

After you have a general idea of the above concepts, go back to backup options:

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

If you want to back up the database to disk, after you select the disk (you can select one or more disk drives, or you can select a tape drive device, for example, disk drives), if you leave the default backup 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 the media header. The media header is created on the first use of a tape (or disk) to perform a backup operation, which will contain the name of the media, a unique identification number for the media set, a unique identification number for the media cluster, and so on. The backup set is then added to the media set. For example, for the first full backup in the previous article, SQL Server will automatically create a media set, add the backup set to the media set, and then back up the transaction log file, and the transaction log file backup set will be appended to the existing backup set. Final backup files All we see is a backup file Mytest.bak, but when you select this file at restore time, the list of backup sets to restore displays the backup set that was backed up, such as a full backup set, the first transaction log backup set, the second transaction log backup set, and so on.

Back up to an existing media set-overwrite all existing backup sets

This option overwrites all backup sets on the media and retains the media headers, if any. If there is no media header, a header is created. When overwriting a backup, all existing media headers are retained, and a new backup is created as the first backup in the backup device.

Do not overwrite backup media if any of the following conditions are present:

An existing backup on the media has not expired. (if SKIP is specified, the expiration is not checked.) ）

The expiration date specifies the date on which the backup expires and can be overwritten by another backup. You can specify an expiration date when you create a backup. By default, the expiration date is determined by the media retention option that is set by using sp_configure.

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

The media name is a descriptive name for easy identification of media.

To sum up, their biggest difference is that one is to append the backup set to the current backup set, one to overwrite the existing backup set, that is, the information previously saved in the backup set will not be reread.

Figure 1: When backing up, select Append to existing backup set, which is displayed in the backup set to be restored at the time of the restore, with each appended backup set.

Figure 2: When backing up, select Overwrite all existing backup sets, and the backup set to be restored on restore displays the covered backup set, and the previously appended backup set is overwritten.

Check the media set name and backup set expiration time

Figure 3: The backup set name and backup set expiration entered during the backup, the generic backup set defaults to the name and, if necessary, can be modified to a meaningful name, and the expiration time generally remains the default never process.

With the above figure, "Check the media set name and backup set expiration Time" in the understanding of how to enter, this I did not experiment, not too much necessary one by one experiments.

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

Select to clear the previous backup set and back up the database with a new media set.

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

Deletes the old header (if present) to effectively remove previous content from the backup media.

Formatting a tape device deletes all previous content from the currently mounted tape. formatting a disk affects only the files that you specify 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. will verify that the backup set is complete and that all volumes are readable.

Check the checksum before writing to the media . The checksum will be validated before the backup media is written, and if selected, the workload may be increased and the backup throughput of the backup operation reduced.

continue when an error occurs. If an error occurs while backing up the database, the backup work will continue.

transaction Log (this item is activated only if the backup type is selected as the transaction log at backup time)

truncate the transaction log . Selecting this item backs up the transaction log and truncates it to free more log space, at which point the database is online.

back up the tail of the log and put the database in a restored state . Selecting this will back up the tail of the log and put the database in a restored state, which creates a tail-log backup that backs up logs that have not been backed up. This option is useful when you fail over to a secondary database or to prevent your work from being lost before the restore operation. After you select this item, the database cannot be used until the database is fully restored.

tape drive . Select this option when you choose to back up the database with a tape drive device.

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

Database Restore Options

Overwrite existing database check box: Selecting this item overwrites all existing databases and related files, including other databases or files that already exist with the same name.

Keep Replication settings check box: When this item is selected, the replication settings are preserved when the published database is restored to a server other than the server that created the database. However, this item is only available if you have selected the roll back uncommitted transactions so that the database is in the available State radio button.

Restrict access to restored databases check box: make the restored database available only to members of db_owner,dbcreator , or sysadmin .

Recovery status:

RESTORE with RECOVERY, which enables the database to be in a state of use by rolling back uncommitted transactions. Additional transaction logs cannot be restored. This entry allows the database to enter a functioning state after the restore and automatically recovers transactions that have not yet been completed, which can be selected if this restore is the last step in the restore process. This is the default option.

RESTORE with NORECOVERY does not perform any operations on the database and does not roll back uncommitted transactions. You can restore additional transaction logs. This item will still not work correctly after the restore, and will not recover outstanding transaction operations, but can continue to restore transaction log backups or differential backups, allowing the database to revert to the nearest current state.

RESTORE with STANDBY to keep the database in read-only mode. Undo uncommitted transactions, but save the undo action in an alternate file so that the restore effect can be reversed. when this item is selected, the alternate file selection directory is activated. This item restores the unfinished transaction after the restore and makes the database read-only, so that you can continue to restore the transaction log backups, and you must specify a restore file to hold the recovered transaction content.

So far, the database backup restore options are basically finished, there are omitted to introduce the options through the name can be directly understood. As for database backup restore scripts the MSDN documentation has syntax and examples, and you can also export standard scripts after you've set up the UI.

Above for my knowledge and summary of SQL Server database backup and restore, if there are missing details or explanation errors, but also hope that the master corrected, thank you!