SQL Server understands Copyonly backup operations

AlwaysOn in the process of adding a database, if the Sync preferences select "Full", then the full and log backups of copyonly are performed on the primary replica to perform the restore operation on the secondary replica, and it is this operation that gives me a new understanding of copyonly. Although you used to perform a full backup with Copyonly,
But the previous understanding of copyonly has some misunderstanding. The next step is to elaborate on the copyonly.

First, the backup test

create database city; Gocreate table city.dbo.test (id int);---Perform a full backup backup database [city] to   disk = n ' D:\backup\city_full_20170613.bak '  WITH NOFORMAT, NOINIT,   Name = n ' city-full   database   backup ',  skip, norewind, nounload,  stats =  10go--insert 1 Records insert into city.dbo.test values (1);--Perform log backup 1backup log [city]  to  disk = n ' D:\backup\city_log1_20170613.trn '  with noformat, noinit,   name = n ' city-log backup 1  backup ',  skip, norewind, nounload,  stats  = 10goinsert into city.dbo.test values (2); go--perform a full copy only backup backup database [city] to  disk = n ' D:\backup\city_ Full_copyonly_20170613.bak '  with  copy_only, noformat, noinit,  name =  n ' city-full copyonly  database  Backup ', skip, norewind, nounload,  stats = 10goinsert into  City.dbo.test values (3);--performing a differential backup backup database [city] to  disk = n ' D : \backup\city_diff_20170613.bak '  WITH  DIFFERENTIAL , NOFORMAT, NOINIT,   name = n ' city-diff   database   backup ',  skip, norewind, nounload,  stats  = 10goinsert into city.dbo.test values (4); go--Perform log backup 2backup log [city] to  disk = n ' D:\backup\city_log2_20170613.trn '  with noformat, noinit,  name = n ' city-log backup 2  backup ', SKIP,  Norewind, nounload,  stats = 10go

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Border:none; "/>

Second, query backup

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Border:none; "/>

SELECT Bs.database_name,bs.name as Backupname,bs.first_lsn,--the log sequence number of the oldest log record in the backup set BS.LAST_LSN,-- The log sequence number of the next log record for the backup set is BS.DATABASE_BACKUP_LSN, the log sequence number of the most recent database full backup BS.CHECKPOINT_LSN, the log sequence number that the redo log began with Bs.is_copy_only,case Bs.type when ' D ' and ' fullback ' when ' L ' then ' logback ' if ' I ' then ' diffback ' ELSE bs.type END as Backuptype,bs.backu P_start_date,bs.backup_finish_date,bs.backup_size,bs.recovery_modelfrom msdb.dbo.backupset BS--inner JOIN Msdb.dbo.backupfile BF on Bs.backup_set_id=bf.backup_set_idwhere bs.database_name= ' city '

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Border:none; "/>

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/135426/201706/135426-20170614171834165-646777098. PNG "style=" border:0px; "/>

There are three more important points of knowledge in the three-color box:

1. Backups other than log backups do not truncate the log

The FIRST_LSN and last_lsn of two log backups from the leftmost red box flag can see that the LSN of the entire two-log backup is continuous from ' 45000000016800179 '-' 45000000038400001 ', the LSN of two log backups covers the LSN of all backups. That is, the middle of the copyonly full and differential backups do not truncate the log ( of course, if there is a full backup in the middle also does not truncate the log, you can try)

2. Copy-only full backups cannot be a baseline backup of differential backups

You can see from the middle section "DATABASE_BACKUP_LSN" column that all subsequent backups are based on the first full backup as a baseline backup.

3. Full, differential, copy-only full backups trigger checkpoint

The last truncated box "CHECKPOINT_LSN" can be seen in addition to log backup, the other three kinds of backup will trigger checkpoint, we also query buffer to see if the Is_modify field is modified to determine.

4. Copy-only full backups can be used as a baseline backup of log Backups

This is not reflected in the above, but you can see that the LSN of log backup 2 is a third copy of the LSN of the backup only, the copy of the full backup can actually be understood as a database at a point in time snapshot, and log backup is to record all changes log operations, can be used to execute redo. So if you will only copy the full backup + 5th log backup for the 3rd time, all the data can be restored.

3rd time Copy only full backup + 5th log backup it = (1th full backup + 4th differential backup + 5th log backup) = (1th times full backup + 2nd log backup + 5th log backup)

Perform the 3rd time copy only full backup + 5th log backup

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Border:none; "/>

n, move n N, move n N, NORECOVERY, Nounload, STATS N, Nounload, STATS

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Border:none; "/>

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/135426/201706/135426-20170614174942665-1504063255. PNG "style=" border:0px; "/>

Three, checkpoint meaning

Since the data is hashed in the disk, if each modification of the disk will inevitably cause a lot of Io, the checkpoint refresh mechanism is introduced, and the dirty pages in buffer are written to disk (also known as persistent operations) based on certain trigger conditions. such as full backups, copy-only full backups, differential backups, log changes to a certain percentage, restart services, etc. will trigger checkpoint, of course, checkpoint is not a unique feature of SQL Server, In other relational databases such as MySQL exist chckpoint mechanism, MySQL also exists per second background thread execution checkpoint operation, but seemingly SQL Server does not, checkpoint involved in a lot of knowledge points here just a little introduction!

SQL Server understands Copyonly backup operations