Shulou(Shulou.com)06/01 Report--

When Alwayson adds a database, if the synchronization preference is full, then a full backup of copyonly and a log backup are performed on the primary copy and a restore operation is performed on the secondary copy, which gives me a new understanding of copyonly. Although copyonly was often used to perform full backups before

But there are some misunderstandings about copyonly before. Next, the operation of copyonly is explained in detail.

First, backup testing

CREATE DATABASE city;GOCREATE TABLE city.dbo.test (id INT);-perform a full backup BACKUP DATABASE [city] TO DISK = nicked:\ backup\ city_full_20170613.bak' WITH NOFORMAT, NOINIT, NAME = Noble City-full database backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GOMY-insert a record INSERT INTO city.dbo.test VALUES (1) -- perform log backup 1BACKUP LOG [city] TO DISK = backup:\ backup\ city_log1_20170613.trn' WITH NOFORMAT, NOINIT, NAME = Nlog backup 1 backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GOINSERT INTO city.dbo.test VALUES (2) GO-- performs a full copyonly backup BACKUP DATABASE [city] TO DISK = nicked:\ backup\ city_full_copyonly_20170613.bak' WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = Numbercity-full copyonly database backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GOINSERT INTO city.dbo.test VALUES (3) -- perform a differential backup BACKUP DATABASE [city] TO DISK = backup\ city_diff_20170613.bak' WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = Nabilistic-differential database backup, SKIP, NOREWIND, NOUNLOAD, STATS = 10GOINSERT INTO city.dbo.test VALUES (4) GO-- performs log backup 2BACKUP LOG [city] TO DISK = city_log2_20170613.trn' WITH NOFORMAT:\ backup\ backup, NOINIT, NAME = Nlog backup 2 backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO

Second, query backup

The log sequence number of the earliest log record in the SELECT bs.database_name,bs.name AS BackupName,bs.first_lsn,-- backup set is bs.last_lsn,-- the log sequence number of the next log record in the backup set, bs.database_backup_lsn, the log sequence number of the most recent full backup of the database, and the log sequence number of the beginning of the redo log. Bs.is_copy_only. CASE bs.type WHEN'D' THEN 'FullBack' WHEN' L' THEN 'LogBack' WHEN' I 'THEN' DiffBack' ELSE bs.type END AS BackupType,bs.backup_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'

In the above picture, three more important knowledge points are identified in the frame of three colors:

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

From the first_lsn and last_lsn of the two log backups marked by the red box on the far left, you can see that the lsn of the entire two log backups is consecutive from '45000000016800179', and the lsn of the two log backups covers the lsn of all backups. That is, the copyonly full backup and differential backup in the middle will not truncate the log (of course, if there is a full backup in the middle and will not truncate the log either, you can try it)

two。 Only replicating a full backup cannot be used as a baseline backup for a differential backup

As you can see from the middle truncated box "database_backup_lsn" column, all subsequent backups are based on the first full backup as the base backup.

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

The last box "checkpoint_lsn" shows that except for log backups, the other three backups will trigger checkpoint. You can also determine whether the is_modify field has been modified by querying buffer to see whether the log field has been modified.

4. Only replicating a full backup can be used as a base backup for log backups

This is not reflected in the screenshot above, but you can see that the lsn of log backup 2 covers the lsn of the third copy-only backup, and only replicating a full backup can be understood as a snapshot of the database at a point in time, while log backup is a log operation that records all changes and can be used to perform redo. So if you copy only the full backup for the third time + the fifth log backup, you can restore all the data.

Third only copy full backup + 5th log backup it = (1st full backup + 4th differential backup + 5th log backup) = (1st full backup + 2nd log backup + 5th log backup)

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

N, MOVE N N, MOVE N N, NORECOVERY, NOUNLOAD, STATS N, NOUNLOAD, STATS

III. Meaning of checkpoint

Because the data is stored in hash on disk, if you modify the disk every time, it will cause a lot of IO, so the checkpoint refresh mechanism is introduced. According to some trigger conditions, checkpoint writes dirty pages in buffer to disk (also known as persistence operation). For example, full backup, only copy full backup, differential backup, log modification to a certain percentage, restart service, and so on, will trigger checkpoint. Of course, checkpoint is not a unique feature of sqlserver. Chckpoint mechanism exists in other relational databases such as mysql. In mysql, there are background threads that execute checkpoint operations per second, but it seems that sqlserver will not. Many knowledge points involved in checkpoint are only briefly introduced here!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.