SQLServer 理解copyonly備份操作

標籤：sql

Alwayson在添加資料庫的過程中如果同步喜好設定選擇的是“完整”，那麼就會在主副本上執行copyonly的完整備份和記錄備份在輔助副本上執行還原作業，也正是這個操作讓我對copyonly有了新的理解。雖然以前也經常使用copyonly執行完整備份，
但是之前對copyonly的理解存在一點誤區。接下來詳細說明copyonly的操作。

 

 

一、備份測試 

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="border:none;" />

CREATE DATABASE city;GOCREATE TABLE city.dbo.test(id INT);---執行完整備份BACKUP DATABASE [city] TO  DISK = N‘D:\backup\city_full_20170613.bak‘ WITH NOFORMAT, NOINIT,  NAME = N‘city-完整 資料庫 備份‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10GO--插入1條記錄INSERT INTO city.dbo.test VALUES(1);--執行記錄備份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--執行完整copy only備份BACKUP DATABASE [city] TO  DISK = N‘D:\backup\city_full_copyonly_20170613.bak‘ WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N‘city-完整copyonly 資料庫 備份‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10GOINSERT INTO city.dbo.test VALUES(3);--執行差異備份BACKUP DATABASE [city] TO  DISK = N‘D:\backup\city_diff_20170613.bak‘ WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N‘city-差異 資料庫 備份‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10GOINSERT INTO city.dbo.test VALUES(4);GO--執行記錄備份2BACKUP LOG [city] TO  DISK = N‘D:\backup\city_log2_20170613.trn‘ WITH NOFORMAT, NOINIT,  NAME = N‘city-記錄備份2 備份‘, SKIP, NOREWIND, NOUNLOAD,  STATS = 10GO

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="border:none;" />

 

二、查詢備份

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="border:none;" />

SELECT bs.database_name,bs.name AS BackupName,bs.first_lsn,--備份組中最早的一條日誌記錄的記錄序號bs.last_lsn, --備份組下一條日誌記錄的記錄序號bs.database_backup_lsn, --最近的資料庫完整備份的記錄序號bs.checkpoint_lsn,  --重做日誌開始的記錄序號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‘

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="border:none;" />

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

中用三種顏色的框框出了三個比較重要的知識點：

1.記錄備份以外的備份不會截斷日誌

從最左邊的紅框標誌的兩次記錄備份的first_lsn和last_lsn可以看到整個兩次記錄備份的lsn是連續的從‘45000000016800179’-‘45000000038400001’，兩次記錄備份的lsn涵蓋了所有備份的lsn。也就是中間的copyonly完整備份和差異備份不會截斷日誌（當然如果中間還存在完整備份同樣不會截斷日誌，大家可以去試試）

2.僅複製完整備份不能作為差異備份的基底備份

從中間的截框“database_backup_lsn”列可以看到，所有的後面的備份都基於第一次完整備份作為基底備份。

3.完整、差異、僅複製完整備份會觸發checkpoint

最後一個截框“checkpoint_lsn”可以看到除了記錄備份，其它的三種備份都會觸發checkpoint,大家也通過查詢buffer查看is_modify欄位是否被修改來判斷。

4.僅複製完整備份可以作為記錄備份的基底備份

這個在上面的中沒有體現出來，但是可以看到記錄備份2的lsn是涵蓋了第三次僅複本備份的lsn,僅複製完整備份其實可以理解成資料庫在一個時間點的快照，而記錄備份是記錄所有更改的日誌操作，可以用來執行redo。所以如果將第3次僅複製完整備份+第5次記錄備份是可以還原所有的資料。

第3次僅複製完整備份+第5次記錄備份它=（第1次完整備份+第4次差異備份+第5次記錄備份）=（第1次完整備份+第2次記錄備份+第5次記錄備份）

 

執行第3次僅複製完整備份+第5次記錄備份

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" 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="複製代碼" style="border:none;" />

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

 

三、checkpoint意義

由於資料在磁碟是散列儲存，如果每次修改都去修改磁碟勢必會造成很多的IO，所以引入了checkpoint重新整理機制，checkpoint根據某些觸發條件將buffer中的髒頁寫入磁碟（也稱作持久化操作）。比如完整備份、僅複製完整備份、差異備份、當日誌的修改到達一定的比例、重啟服務等都會觸發checkpoint，當然checkpoint並不是sqlserver專屬的功能，在其他的關係型資料庫比如mysql都存在chckpoint機制；mysql中還存在每秒後台線程執行checkpoint操作，但是貌似sqlserver不會，checkpoint涉及的知識點很多這裡只是稍微介紹！

SQLServer 理解copyonly備份操作