开发者社区> 科技小能手> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQLServer 理解copyonly备份操作

简介:
+关注继续查看

概述  

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


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
oracle,mysql,SqlServer三种数据库的分页查询
MySql: MySQL数据库实现分页比较简单,提供了 LIMIT函数。一般只需要直接写到sql语句后面就行了。LIMIT子 句可以用来限制由SELECT语句返回过来的数据数量,它有一个或两个参数,如果给出两个参数, 第一个参数指定返回的第一行在所有数据中的位置,从0开始(注意不是1),第二个参数指定最多返回行数。
1082 0
向SqlServer数据库插入数据
原文:向SqlServer数据库插入数据 Insert Values Insert Select Insert Exec Select Into Bulk Insert Insert Values是最常用的一种插入数据的方式,基本语法如下,表名之后可以指定目标列的名称,这种显式的指定目...
830 0
基于4.5Framework web程序、SQLSERVER数据库打包
原文:基于4.5Framework web程序、SQLSERVER数据库打包   估计很多朋友和我一样,对于C/S程序打包很熟悉,但对于B/S程序打包一头雾水。。。   最近公司要求我们把项目和数据库(SQLSERVER)一起打包,然后安装在CD光盘上,打算拿光盘去客户那边实现一键安装。
1078 0
SQLServer 2005 数据库定阅复制实现双机热备(主要是sharepoint 内容数据库)
原文:SQLServer 2005 数据库定阅复制实现双机热备(主要是sharepoint 内容数据库) 场景   公司最近的sharepoint的数据库服务器老是出问题,并且在一旦出现问题,就导致无法正常工作。
1509 0
sqlserver 数据库 SQL循环语句
关键字: sqlserver 数据库 SQL循环语句 declare @i int set @i=1 while @i $50         BREAK      ELSE         CONTINUE END PRINT 'Too much for the market to bear' B. 在带有游标的过程中使用 WHILE 以下的 WHILE 结构是名为 count_all_rows 过程中的一部分。
843 0
sql server数据库定时自动备份
     1、进入企业管理器中->管理->sql server代理->作业;  2、新建作业,作业名称随便取,例如:data备份,所有者选择sa,当然你也可以选择其他用户,前提是该用户有执行作业的权限;  3、点击步骤标签,进入步骤面板。
1109 0
通过事务日志恢复SqlServer数据库到一个特定的时间点
Code--创建测试数据库CREATE DATABASE DbGO--对数据库进行备份BACKUP DATABASE Db TO DISK='c:\db.bak' WITH FORMATGO--创建测试表CREATE TABLE Db.
728 0
在非SqlServer数据库上实现MemberShip和Role功能(自定义MemberShipProvider和RoleProvider)
默认情况下,.Net网站上的MemberShip和Role功能只能用于SqlServer,如果要在其它数据库,比如Access,Oracle上使用该功能该怎么办呢? 其实MS早就为我们考虑到了,用户只要从MemberShipProvider和RoleProvider派生自己的Provider类,并...
833 0
文章
问答
文章排行榜
最热
最新
相关电子书
更多
SQL Server 2017
立即下载
PostgresChina2018_董红禹_SQL_Server迁移PG经验分享
立即下载
SQL Sever迁移PG经验
立即下载