Backup/restore Principles for SQL Server differential backups

Original: Backup/restore Principles for SQL Server differential backups

Backup/restore Principles for SQL Server differential backups

Remember one point: Differential backups are based on the difference from the last full backup, not the difference based on the last difference

Backup process:

1-After a full backup there have been no modifications to the database, if any, the last LSN of the logging database if no changes have been made to the database after the full backup, then the differential backup is meaningless 2- Read the diff page content based on the differential bitmap when doing a differential backup note: The diff bitmap records the pages that have changed in the database since the last full backup, so that you do not have to sweep the entire library page when making a differential backup, just read the differential Page 3-Before the backup, the database will do a checkpoint. Record the LSN of the last checkpoint in the backup file.

4-Record the last LSN in the backup file 5-this time the bootpage of the database holds the checkpoint LSN of the last full backup of the database

Several LSN values are stored in the backup file.

FirstLSN
LastLSN
Checkpointlsn
DatabaseBackupLSN
Differentialbaselsn

Bootpage only differentialbaselsn have value.

In general, the differential backup file will contain

(1) The following LSN value

FirstLSN
LastLSN
Checkpointlsn
DatabaseBackupLSN
Differentialbaselsn

(2) Activity log

(3) diff page

Restore process:

Differentialbaselsn effect: To restore a differential backup first to restore a full backup, using the NORECOVERY option, and then restore the differential backup, when the differential backup is restored, the database based on the value of DIFFERENTIALBASELSN in the differential backup file

The database Bootpage the value of the saved differentialbaselsn, and if not, the differential backup cannot be restored, and the other is the base LSN that is restored as a differential backup. If the CHECKPOINTLSN in the differential backup file is less than DIFFERENTIALBASELSN

Then there's no need to restore

1-Read the page from the differential backup file and read the LSN of the header, comparing it to the CHECKPOINTLSN in the differential backup file, if it is greater than checkpointlsn and less than LASTLSN, Then take out the active log in the differential backup file to change the page redo and undo if less than CHECKPOINTLSN, then this page does not need to restore, because the page version of the database is more than the difference in the backup file of the page in the new activity log records also saved the page number, It is convenient for redo and undo of the page
2-write the redo and undo pages back to the database


Differential backup disadvantage differential backups are based on the difference from the last full backup, so for a busy database, several differential backups have been made over a period of time, regardless of how many times Checkpoint,sql server still places these differential pages based on the full backup into the differential backup file. According to reason, a page after a checkpoint, if there is no further changes, then it is not necessary to put inside the differential backup file (if it is based on the difference between the last difference) because the difference bitmap can not be based on differences, this will cause, Sometimes the differential backup file is larger than the full backup file

--ScriptSELECT db_id('SSS')DBCCFileheader ( -)BACKUP DATABASE [SSS]  to DISK='C:\sss_full.bak' RESTOREFilelistonly from DISK ='C:\sss_full.bak' RESTOREHeaderonly from DISK ='C:\sss_full.bak' 1585000000012200042 --BACKUPLSN1686000000056400078 --ckpt LSN1686000000060800001 --Last LSNCREATE TABLETessssss (IDINT)GoINSERTTessssssSELECT 1 UNION  All SELECT 2BACKUP DATABASE [SSS]  to DISK='C:\sss_diff.bak'  withdifferentialRESTOREHeaderonly from DISK ='C:\sss_diff.bak'1686000000056400078 --BACKUPLSN1686000000056400078 --diff LSN1686000000065400151 --ckpt LSN1686000000071800001 --Last LSNINSERTTessssssSELECT 3 UNION  All SELECT 4BACKUP DATABASE [SSS]  to DISK='C:\sss_diff2.bak'  withdifferentialRESTOREHeaderonly from DISK ='C:\sss_diff2.bak'1686000000056400078 --BACKUPLSN1686000000056400078 --diff LSN1686000000071900004 --ckpt LSN1686000000072300001 --Last LSN--FirstLSN--LastLSN--CHECKPOINTLSN--DATABASEBACKUPLSN--DIFFERENTIALBASELSN

MySQL's xtrabackup backup tool is pretty much the same, reading the page, backing up and restoring it based on the LSN of the CHECKPOINTLSN and the page, so LSN plays an important role in the backup and restore of the database. For the last LSN, refer to:

Do you really understand the log chain of SQL Server?
Http://www.cnblogs.com/lyhabc/p/3460272.html

If there is a wrong place, welcome everyone to shoot brick O (∩_∩) o

Backup/restore Principles for SQL Server differential backups