SQLServer 理解copyonly备份操作

标签：MSSQL/日志截断

概述  

Alwayson在添加数据库的过程中如果同步首选项选择的是“完整”，那么就会在主副本上执行copyonly的完整备份和日志备份在辅助副本上执行还原操作，也正是这个操作让我对copyonly有了新的理解。虽然以前也经常使用copyonly执行完整备份，
但是之前对copyonly的理解存在一点误区。接下来详细说明copyonly的操作。

一、备份测试 

CREATE DATABASE city;
GO
CREATE TABLE city.dbo.test(id INT);

---执行完整备份
BACKUP DATABASE [city] TO  DISK = N'D:ackupcity_full_20170613.bak' WITH NOFORMAT, NOINIT,  NAME = N'city-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

--插入1条记录
INSERT INTO city.dbo.test VALUES(1);

--执行日志备份1
BACKUP LOG [city] TO  DISK = N'D:ackupcity_log1_20170613.trn' WITH NOFORMAT, NOINIT,  NAME = N'city-日志备份1 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

INSERT INTO city.dbo.test VALUES(2);
GO

--执行完整copy only备份
BACKUP DATABASE [city] TO  DISK = N'D:ackupcity_full_copyonly_20170613.bak' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'city-完整copyonly 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

INSERT INTO city.dbo.test VALUES(3);

--执行差异备份
BACKUP DATABASE [city] TO  DISK = N'D:ackupcity_diff_20170613.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'city-差异 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

INSERT INTO city.dbo.test VALUES(4);

GO
--执行日志备份2
BACKUP LOG [city] TO  DISK = N'D:ackupcity_log2_20170613.trn' WITH NOFORMAT, NOINIT,  NAME = N'city-日志备份2 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

二、查询备份

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_model
FROM msdb.dbo.backupset bs 
--INNER JOIN msdb.dbo.backupfile bf ON bs.backup_set_id=bf.backup_set_id
WHERE bs.database_name='city'

 

上图中用三种颜色的框框出了四个比较重要的知识点：

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次日志备份

USE [master]
RESTORE DATABASE [city_copyolny] FROM  DISK = N'D:ackupcity_full_copyonly_20170613.bak' WITH  FILE = 1,  
MOVE N'city' TO N'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAcity_copyolny.mdf',  
MOVE N'city_log' TO N'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAcity_copyolny_log.ldf',  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [city_copyolny] FROM  DISK = N'D:ackupcity_log2_20170613.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 5

GO

三、checkpoint意义

由于数据在磁盘是散列存储，如果每次修改都去修改磁盘势必会造成很多的IO，所以引入了checkpoint刷新机制，checkpoint根据某些触发条件将buffer中的脏页写入磁盘（也称作持久化操作）。比如完整备份、仅复制完整备份、差异备份、当日志的修改到达一定的比例、重启服务等都会触发checkpoint，当然checkpoint并不是sqlserver独有的功能，在其他的关系型数据库比如mysql都存在chckpoint机制；mysql中还存在每秒后台线程执行checkpoint操作，但是貌似sqlserver不会，checkpoint涉及的知识点很多这里只是稍微介绍！

总结  

其实上面的备份测试中在中间在加入一次完整备份就更加完美了，但是如果大家理解备份的原理也是一样可以理解的。

备注：

    作者：pursuer.chen

    博客：http://www.cnblogs.com/chenmh

本站点所有随笔都是原创，欢迎大家转载；但转载时必须注明文章来源，且在文章开头明显处给明链接。

《欢迎交流讨论》