SQL Server Backup Brief

SQL Server supports many different types of backups, but the most common are full, differential, and transaction log backups. In the final analysis of which backup to use also refer to the recovery model of the database, such as the simple recovery model does not support transaction log backups.

Full backup

Back up all the data in the specified database, the cost of backup and recovery is higher when the data is large, and the process is to lock the database first, block all transactions, immediately write a flag 1 in the transaction log, and quickly release the database lock, which is fast.

All pages containing data in the data file are then extracted to the backup device. Pages that do not contain data are skipped.

Then, lock the database again, block all transactions, write a flag 2 in the transaction log, and quickly release the database lock.

Finally, the transaction between the flags 1 and 2 is extracted and the data is written to the backup device.

As shown in the following:

650) this.width=650; "Width=" 421 "height=" 98 "title=" image "style=" padding-top:0px;padding-right:0px;padding-left:0 Px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;background-image:none; "alt=" image "src=" Http://s3.51cto.com/wyfs02/M01/6E/F2/wKiom1WMlyfxuzm1AAB1zFrXpyo882.jpg "border=" 0 "/>

In this figure, D represents a page with data, L is a log file, if you are using a full backup, start by marking 1, then back up all the data pages to the device, then Mark 2, and then add the data generated by the log files in the middle of marker 1 and tag 2 to the backup device.

Differential backup

Differential backup is a good understanding, it is relative to the full backup, the backup is the full backup after the change of content, but only full backup, with the last differential backup has no relationship. The size of the differential backup depends on the amount of data that has changed since the differential base was established. Typically, the older the differential base, the greater the new differential backup. A specific differential backup captures the state of the changed extents when the backup is created. If you create a series of differential backups, the frequently updated extents may contain different data in each discrepancy. When the size of a differential backup increases, restoring a differential backup significantly increases the time it takes to restore the database. Therefore, it is recommended that a new full backup be performed at set intervals to establish a new differential base for the data. For example, you can perform a full backup of the entire database once a week (that is, a full database backup), and then perform a series of regular differential database backups within that week.

We all know that the data page in the database is 8KB in size, and 8 connected data pages are called a zone. Then there is a mapping table called the differential bitmap that maps to all the data areas. When we make a benchmark, that is, a full backup, the position in the corresponding mapping table is zeroed out, and if there is a change in the data, the corresponding position becomes 1. As shown in the following:

650) this.width=650; "Width=" 363 "height=" 248 "title=" image "style=" Padding-top:0px;padding-right:0px;padding-left : 0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;background-image:none; "alt=" image "src = "Http://s3.51cto.com/wyfs02/M02/6E/F2/wKiom1WMlyeRo4vGAACBCdqMRXk949.jpg" border= "0"/>

The figure shows 24 data areas, six of which have changed. A differential backup contains only these six data areas. The differential backup operation depends on the bitmap page, which contains one bit for each zone.

Transaction log Backups

A transaction log that records all transaction operations in the database and the modifications made to the database by each transaction. However, the ability of a database to perform transaction log backups depends on the recovery model of the database and, if it is the full recovery model or the bulk-logged recovery model, you can make a transaction log backup of the database.

In the case of the full recovery model, when a transaction log backup is performed, all virtual log files (VLF) are scanned for new log records since the last transaction log backup. and add these log records to the backup file.

The bulk-logged recovery model only minimally records large-capacity operations (although other transactions are fully logged) compared to the full recovery model (full logging of all transactions). The bulk-logged recovery model protects high-capacity operations from media failures, provides optimal performance, and consumes minimal log space. This means that the bulk-logged recovery model prevents the capture of changes made to each transaction, only the transactions and data changed by the bulk operation, and cannot be restored to the point in time in that log backup, but only the entire log backup is restored.

The principle of transaction log backup under bulk-logged backup recovery also requires a bitmap page, which is a bit similar to a differential backup.

To track a data page, the log backup operation relies on bulk changes to the bitmap page, and the bitmap page contains one bit for each zone. After a log backup, the location of the corresponding bitmap page in the data page area is zeroed. Then, for each zone that was updated by the bulk-logged operation since the last log backup, each bit is set to 1 in the bitmap. The data area is copied to the log, followed by the log data. Shows how log backups are constructed.

650) this.width=650; "Width=" 407 "height=" 290 "title=" image "style=" Padding-top:0px;padding-right:0px;padding-left : 0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;background-image:none; "alt=" image "src = "Http://s3.51cto.com/wyfs02/M00/6E/F2/wKiom1WMlyeCDMsIAACmBqURz2s345.jpg" border= "0"/>

These are the three most common types of backups that SQL Server uses.

Copy-only backup options

In SQL Server 2005 and later versions, one more copy-only backup option, when can I use this option? In Microsoft's words:

Copy-only backup is a SQL Server backup that is independent of the regular SQL Server backup sequence. Typically, making a backup changes the database and affects how the backup is restored thereafter. However, it is sometimes useful to back up a particular purpose without affecting the overall database backup and restore process. A copy-only backup is used for this purpose.

A copy-only backup is a normal sequence that is independent of the SQL Server backup, that is, if we are now using a copy-only backup to make a full backup of the database, it is not necessary to generate a new baseline that does not affect differential backups, the baseline that the differential backup considers, or the last full backup If a copy-only backup is selected during a full backup, the transaction log is not truncated, that is, the original log chain is not affected, whether this is a full or log backup. Therefore, now on an AlwaysOn secondary node, you can use the copy-only backup option to perform full and transaction log backups of the database without affecting the original log chain.

650) this.width=650; "Width=" 588 "height=" 612 "title=" image "style=" Padding-top:0px;padding-right:0px;padding-left : 0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;background-image:none; "alt=" image "src = "Http://s3.51cto.com/wyfs02/M01/6E/F2/wKiom1WMlyejyBAhAAJn6aCn0_0770.jpg" border= "0"/>

Copy-only backups are available for full and log backups, but not for differential backups. For more information in this article, refer to: https://msdn.microsoft.com/zh-cn/library/ms187048 (v=sql.105). aspx

This article is from the "Duffy" blog, make sure to keep this source http://dufei.blog.51cto.com/382644/1665788

SQL Server Backup Brief