SQLServer 理解copyonly备份操作

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
日志服务 SLS,月写入数据量 50GB 1个月
简介:

概述  

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

 

 

一、备份测试 

复制代码

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

复制代码

 

二、查询备份

复制代码

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'

复制代码

 

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

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:\backup\city_full_copyonly_20170613.bak' WITH  FILE = 1,  
MOVE N'city' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\city_copyolny.mdf',  
MOVE N'city_log' TO N'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

复制代码

 

三、checkpoint意义

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

 

 

概述  

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


本文转自 bxst 51CTO博客,原文链接:http://blog.51cto.com/13013670/1939650


相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
6月前
|
SQL 数据库
Microsoft SQL Server 2014如何来备份数据库
Microsoft SQL Server 2014如何来备份数据库
589 3
|
6月前
|
SQL 数据库
SQL Server 如何进行备份?
【8月更文挑战第20天】SQL Server 如何进行备份?
138 3
|
6月前
|
SQL 存储 数据库
SQL Server 中的备份类型详解
【8月更文挑战第31天】
143 0
|
6月前
|
SQL 数据库
SQL Server 还原 备份集中的数据库备份与现有的 'XXX' 数据库不同
SQL Server 还原 备份集中的数据库备份与现有的 'XXX' 数据库不同
419 0
|
7月前
|
SQL 关系型数据库 MySQL
「Python入门」python操作MySQL和SqlServer
**摘要:** 了解如何使用Python的pymysql模块与MySQL数据库交互。首先,通过`pip install pymysql`安装模块。pymysql提供与MySQL的连接功能,例如创建数据库连接、执行SQL查询。在设置好MySQL环境后,使用`pymysql.connect()`建立连接,并通过游标执行SQL(如用户登录验证)。注意防止SQL注入,使用参数化查询。增删改操作需调用`conn.commit()`来保存更改。pymssql模块类似,但导入和连接对象创建略有不同。
85 0
「Python入门」python操作MySQL和SqlServer
|
9月前
|
关系型数据库 MySQL 数据库
实时计算 Flink版操作报错合集之sqlserver mysql都用的胖包,sqlserver的成功了,mysql报这个错如何解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
372 6
|
9月前
|
SQL Java 网络安全
实时计算 Flink版操作报错合集之SQLserver表没有主键,同步的时候报错如何解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
289 1
|
9月前
|
SQL 安全 数据库
SQL Server 备份和还原
SQL Server 备份和还原
|
9月前
|
SQL 调度 数据库
【Database】Sqlserver如何定时备份数据库和定时清除
【Database】Sqlserver如何定时备份数据库和定时清除
391 2
|
9月前
|
SQL Kubernetes 数据库
实时计算 Flink版操作报错合集之查询sqlserver ,全量阶段出现报错如何解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
116 0