SQL Server understands Copyonly backup operations

Tags: mssql/log truncation

Overview

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 DATABASECity ;GOCREATE TABLECity.dbo.test (IDINT);---Perform a full backupBACKUP DATABASE [ City]  to  DISK =N'D:\backup\city_full_20170613.bak'  withNoformat, Noinit, NAME=N'city-full Database backup', SKIP, Norewind, Nounload, STATS= TenGO--inserting 1 RecordsINSERT  intoCity.dbo.testVALUES(1);--performing a log backup 1BACKUP LOG [ City]  to  DISK =N'D:\backup\city_log1_20170613.trn'  withNoformat, Noinit, NAME=N'city-Log backup 1 backup', SKIP, Norewind, Nounload, STATS= TenGOINSERT  intoCity.dbo.testVALUES(2);GO--perform a full copy only backupBACKUP DATABASE [ City]  to  DISK =N'D:\backup\city_full_copyonly_20170613.bak'  withCopy_only, Noformat, Noinit, NAME=N'city-full Copyonly database backup', SKIP, Norewind, Nounload, STATS= TenGOINSERT  intoCity.dbo.testVALUES(3);--performing a differential backupBACKUP DATABASE [ City]  to  DISK =N'D:\backup\city_diff_20170613.bak'  withDifferential, Noformat, Noinit, NAME=N'city-differential Database backup', SKIP, Norewind, Nounload, STATS= TenGOINSERT  intoCity.dbo.testVALUES(4);GO--performing a log backup 2BACKUP LOG [ City]  to  DISK =N'D:\backup\city_log2_20170613.trn'  withNoformat, Noinit, NAME=N'city-Log backup 2 backup', SKIP, Norewind, Nounload, STATS= TenGO

Second, query backup

SELECTBs.database_name,bs.name asBackupname,bs.first_lsn,--log sequence number of the oldest log record in a backup setBS.LAST_LSN,--log sequence number of the next log record for the backup setBS.DATABASE_BACKUP_LSN,--log sequence number of the most recent database full backupBS.CHECKPOINT_LSN,--log sequence number for redo log startBs.is_copy_only, CaseBs.type when 'D'  Then 'fullback'  when 'L'  Then 'Logback'  when 'I'  Then 'Diffback' ELSEBs.typeEND  asBackuptype,bs.backup_start_date,bs.backup_finish_date,bs.backup_size,bs.recovery_model fromMsdb.dbo.backupset BS--INNER JOIN msdb.dbo.backupfile BF on bs.backup_set_id=bf.backup_set_idWHEREBs.database_name=' City'

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

 Use [Master]RESTORE DATABASE [City_copyolny]  from  DISK =N'D:\backup\city_full_copyonly_20170613.bak'  with  FILE = 1,
MOVE N' City'  toN'C:\Program Files\Microsoft SQL Server\mssql12. Mssqlserver\mssql\data\city_copyolny.mdf',
MOVE N'City_log'  toN'C:\Program Files\Microsoft SQL Server\mssql12. Mssqlserver\mssql\data\city_copyolny_log.ldf', NORECOVERY, Nounload, STATS= 5RESTORE LOG [City_copyolny]  from  DISK =N'D:\backup\city_log2_20170613.trn'  with  FILE = 1, Nounload, STATS= 5GO

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!

Overview

In fact, the above backup test in the middle of a full backup is more perfect, but if you understand the principle of backup is also understandable.

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly give the link. Welcome to the exchange of discussions

SQL Server understands Copyonly backup operations