SQLServer 理解copyonly備份操作

Alwayson在添加數(shù)據(jù)庫(kù)的過(guò)程中如果同步首選項(xiàng)選擇的是“完整”，那么就會(huì)在主副本上執(zhí)行copyonly的完整備份和日志備份在輔助副本上執(zhí)行還原操作，也正是這個(gè)操作讓我對(duì)copyonly有了新的理解。雖然以前也經(jīng)常使用copyonly執(zhí)行完整備份，
但是之前對(duì)copyonly的理解存在一點(diǎn)誤區(qū)。接下來(lái)詳細(xì)說(shuō)明copyonly的操作。

一、備份測(cè)試 

CREATE DATABASE city;GOCREATE TABLE city.dbo.test(id INT);---執(zhí)行完整備份BACKUP DATABASE [city] TO  DISK = N'D:\backup\city_full_20170613.bak' WITH NOFORMAT, NOINIT,  NAME = N'city-完整 數(shù)據(jù)庫(kù) 備份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10GO--插入1條記錄INSERT INTO city.dbo.test VALUES(1);--執(zhí)行日志備份1BACKUP LOG [city] TO  DISK = N'D:\backup\city_log1_20170613.trn' WITH NOFORMAT, NOINIT,  NAME = N'city-日志備份1 備份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10GOINSERT INTO city.dbo.test VALUES(2);GO--執(zhí)行完整copy only備份BACKUP DATABASE [city] TO  DISK = N'D:\backup\city_full_copyonly_20170613.bak' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'city-完整copyonly 數(shù)據(jù)庫(kù) 備份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10GOINSERT INTO city.dbo.test VALUES(3);--執(zhí)行差異備份BACKUP DATABASE [city] TO  DISK = N'D:\backup\city_diff_20170613.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'city-差異 數(shù)據(jù)庫(kù) 備份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10GOINSERT INTO city.dbo.test VALUES(4);GO--執(zhí)行日志備份2BACKUP LOG [city] TO  DISK = N'D:\backup\city_log2_20170613.trn' WITH NOFORMAT, NOINIT,  NAME = N'city-日志備份2 備份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10GO

二、查詢(xún)備份

SELECT bs.database_name,
bs.name AS BackupName,
bs.first_lsn,--備份集中最早的一條日志記錄的日志序列號(hào)bs.last_lsn, --備份集下一條日志記錄的日志序列號(hào)bs.database_backup_lsn, --最近的數(shù)據(jù)庫(kù)完整備份的日志序列號(hào)bs.checkpoint_lsn,  --重做日志開(kāi)始的日志序列號(hào)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'

上圖中用三種顏色的框框出了三個(gè)比較重要的知識(shí)點(diǎn)：

1.日志備份以外的備份不會(huì)截?cái)嗳罩?/strong>

從最左邊的紅框標(biāo)志的兩次日志備份的first_lsn和last_lsn可以看到整個(gè)兩次日志備份的lsn是連續(xù)的從‘45000000016800179’-‘45000000038400001’，兩次日志備份的lsn涵蓋了所有備份的lsn。也就是中間的copyonly完整備份和差異備份不會(huì)截?cái)嗳罩荆?span >當(dāng)然如果中間還存在完整備份同樣不會(huì)截?cái)嗳罩?，大家可以去試?/span>）

2.僅復(fù)制完整備份不能作為差異備份的基準(zhǔn)備份

從中間的截框“database_backup_lsn”列可以看到，所有的后面的備份都基于第一次完整備份作為基準(zhǔn)備份。

3.完整、差異、僅復(fù)制完整備份會(huì)觸發(fā)checkpoint

最后一個(gè)截框“checkpoint_lsn”可以看到除了日志備份，其它的三種備份都會(huì)觸發(fā)checkpoint,大家也通過(guò)查詢(xún)buffer查看is_modify字段是否被修改來(lái)判斷。

4.僅復(fù)制完整備份可以作為日志備份的基準(zhǔn)備份

這個(gè)在上面的截圖中沒(méi)有體現(xiàn)出來(lái)，但是可以看到日志備份2的lsn是涵蓋了第三次僅復(fù)制備份的lsn,僅復(fù)制完整備份其實(shí)可以理解成數(shù)據(jù)庫(kù)在一個(gè)時(shí)間點(diǎn)的快照，而日志備份是記錄所有更改的日志操作，可以用來(lái)執(zhí)行redo。所以如果將第3次僅復(fù)制完整備份+第5次日志備份是可以還原所有的數(shù)據(jù)。

第3次僅復(fù)制完整備份+第5次日志備份它=（第1次完整備份+第4次差異備份+第5次日志備份）=（第1次完整備份+第2次日志備份+第5次日志備份）

執(zhí)行第3次僅復(fù)制完整備份+第5次日志備份

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

三、checkpoint意義

由于數(shù)據(jù)在磁盤(pán)是散列存儲(chǔ)，如果每次修改都去修改磁盤(pán)勢(shì)必會(huì)造成很多的IO，所以引入了checkpoint刷新機(jī)制，checkpoint根據(jù)某些觸發(fā)條件將buffer中的臟頁(yè)寫(xiě)入磁盤(pán)（也稱(chēng)作持久化操作）。比如完整備份、僅復(fù)制完整備份、差異備份、當(dāng)日志的修改到達(dá)一定的比例、重啟服務(wù)等都會(huì)觸發(fā)checkpoint，當(dāng)然checkpoint并不是sqlserver獨(dú)有的功能，在其他的關(guān)系型數(shù)據(jù)庫(kù)比如mysql都存在chckpoint機(jī)制；mysql中還存在每秒后臺(tái)線程執(zhí)行checkpoint操作，但是貌似sqlserver不會(huì)，checkpoint涉及的知識(shí)點(diǎn)很多這里只是稍微介紹！