MSSQL-最佳实践-SQL Server三种常见备份

摘要

本期月报是SQL Server数据库备份技术系列文章的开篇,介绍三种常见的SQL Server备份方法的工作方式、使用T-SQL语句和使用SSMS IDE创建备份集三个层面,介绍SQL Server的三种常见备份的工作原理和使用方法。三种常见的备份包括:
数据库完全备份(Full Backup)
数据库日志备份(Transaction Log Backup)
数据库差异备份(Differential Backup)

备份的重要性

在开始分享之前,我们首先来看看数据库备份的重要性。进入DT时代,数据的价值越发体现,数据已经成为每个公司赖以生存的生命线,数据的重要性不言而喻,而公司绝大多数核心数据都存放在数据库里。数据库本身的灾难恢复(DR)能力是数据安全的最后一道防线,也是数据库从业者对数据安全底线的坚守。数据库中数据潜在的安全风险包括:硬件故障、恶意入侵、用户误操作、数据库损坏和自然灾害导致的数据损失等。在关系型数据库SQL Server中,数据库备份是灾难恢复的能力有力保证。

Full Backup

Full Backup(完全备份)是SQL Server所有备份类型中,最为简单、最基础的数据库备份方法,它提供了某个数据库在备份时间点的完整拷贝。但是,它仅支持还原到数据库备份成功结束的时间点,即不支持任意时间点还原操作。

Full Backup工作方式

以上是Full Backup是什么的解释,那么接下来,我们通过一张图和案例来解释Full Backup的工作原理。

这是一张某数据库的数据产生以及数据库备份在时间轴上的分布图,从左往右,我们可以分析如下:
7 P.m.:产生了数据#1
10 P.m.:数据库完全备份,备份文件中包含了#1
2 a.m.:产生了数据#2,目前数据包含#1,#2
6 a.m.:产生了数据#3,目前数据包含#1,#2,#3
10 a.m.:数据库完全备份,备份文件中包含#1,#2,#3
1 p.m.:产生了数据#4,目前数据包含#1,#2,#3,#4
5 p.m.:产生了数据#5,目前数据包含#1,#2,#3,#4,#5
8 p.m.:产生了数据#6,目前数据包含#1,#2,#3,#4,#5,#6
10 p.m.:数据库完全备份,备份文件中包含了数据#1,#2,#3,#4,#5,#6
从这张图和相应的解释分析来看,数据库完全备份工作原理应该是非常简单的,它就是数据库在备份时间点对所有数据的一个完整拷贝。当然在现实的生产环境中,事务的操作远比这个复杂,因此,在这个图里面有两个非常重要的点没有展示出来,那就是:
备份操作可能会导致I/O变慢:由于数据库备份是一个I/O密集型操作,所以在数据库备份过程中,可能会导致数据库的I/O操作变慢。
全备份过程中,数据库的事务日志不能够被截断:对于具有大事务频繁操作的数据库,可能会导致事务日志空间一直不停频繁增长,直到占满所有的磁盘剩余空间,这个场景在阿里云RDS SQL产品中有很多的客户都遇到过。其中之一解决方法就需要依赖于我们后面要谈到的事务日志备份技术。

T-SQL创建Full Backup

使用T-SQL语句来完成数据库的完全备份,使用BACKUP DATABASE语句即可,如下,对AdventureWorks2008R2数据库进行一个完全备份:

USE master
GO

BACKUP DATABASE [AdventureWorks2008R2]
TO DISK = 'C:\Temp\AdventureWorks2008R2_20171112_FULL.bak' WITH COMPRESSION, INIT, STATS = 5;
GO

SSMS IDE创建Full Backup

除了使用T-SQL语句创建数据库的完全备份外,我们还可以使用SSMS IDE界面操作来完成,方法:
右键点击想要备份的数据库 => Tasks => Backup => 选择FULL Backup Type => 选择Disk 做为备份文件存储 => 点击Add 添加备份文件 => 选择你需要存储备份文件的目录 => 输入备份文件名,如下图两张图展示。

Back up Database设置界面

Transaction Log Backup

SQL Server数据库完全备份是数据库的完整拷贝,所以备份文件空间占用相对较大,加之可能会在备份过程中导致事务日志一直不断增长。为了解决这个问题,事务日志备份可以很好的解决这个问题,因为:事务日志备份记录了数据库从上一次日志备份到当前时间内的所有事务提交的数据变更,它可以配合数据库完全备份和差异备份(可选)来实现时间点的还原。当日志备份操作成功以后,事务日志文件会被截断,事务日志空间将会被重复循环利用,以此来解决完全备份过程中事务日志文件一致不停增长的问题,因此我们最好能够周期性对数据库进行事务日志备份,以此来控制事务日志文件的大小。但是这里需要有一个前提是数据库必须是FULL恢复模式,SIMPLE恢复模式的数据库不支持事务日志的备份,当然就无法实现时间点的还原。请使用下面的语句将数据库修改为FULL恢复模式,比如针对AdventureWorks2008R2数据库:

USE [master]
GO
ALTER DATABASE [AdventureWorks2008R2] SET RECOVERY FULL WITH NO_WAIT
GO

Transaction Log Backup工作方式

事务日志备份与数据完全备份工作方式截然不同,它不是数据库的一个完整拷贝,而是至上一次日志备份到当前时间内所有提交的事务数据变更。用一张图来解释事务日志备份的工作方式:

00:01:事务#1,#2,#3开始,未提交
00:02:事务#1,#2,#3成功提交;#4,#5,#6事务开始,未提交;这时备份事务日志;事务日志备份文件中仅包含已提交的#1,#2,#3的事务(图中的LSN 1-4,不包含#4)
00:04:由于在00:02做了事务日志备份,所以#1,#2,#3所占用的空间被回收;#4,#5,#6事务提交完成
00:05:事务#7已经提交成功;#8,#9,#10开始,但未提交;事务日志备份文件中包含#4,#5,#6,#7的事务(图中的LSN4-8,不包含#8)。
从这张图我们看到,每个事务日志备份文件中包含的是已经完成的事务变更,两次事务日志备份中存放的是完全不同的变更数据。而每一次事务日志备份成功以后,事务日志空间可以被成功回收,重复利用,达到了解决数据库完全备份过程中事务日志一致不断增长的问题。

T-SQL创建事务日志备份

使用T-SQL语句来创建事务日志的备份方法如下:

USE Master
GO

BACKUP LOG [AdventureWorks2008R2]
TO DISK = N'C:\temp\AdventureWorks2008R2_log_201711122201.trn' with compression,stats=1;
GO
BACKUP LOG [AdventureWorks2008R2]
TO DISK = N'C:\temp\AdventureWorks2008R2_log_201711122202.trn' with compression,stats=1;
GO
BACKUP LOG [AdventureWorks2008R2]
TO DISK = N'C:\temp\AdventureWorks2008R2_log_201711122203.trn' with compression,stats=1;
GO

SSMS IDE创建事务日志备份

使用SSMS IDE创建事务日志备份的方法:
右键点击想要创建事务日志备份的数据库 => Tasks => Backup => 选择Transaction Log Backup Type => 选择Disk 做为备份文件存储 => 点击Add 添加备份文件 => 选择你需要存储备份文件的目录 => 输入备份文件名,如下图展示:

事务日志备份链

由于数据库完全备份是时间点数据的完整拷贝,每个数据库完整备份相互独立,而多个事务日志备份是通过事务日志链条连接在一起,事务日志链起点于完全备份,SQL Server中的每一个事务日志备份文件都拥有自己的FirstLSN和LastLSN,FirstLSN用于指向前一个事务日志备份文件的LastLSN;而LastLSN指向下一个日志的FirstLSN,以此来建立这种链接关系。这种链接关系决定了事务日志备份文件还原的先后顺序。当然,如果其中任何一个事务日志备份文件丢失或者破坏,都会导致无法恢复整个事务日志链,仅可能恢复到你拥有的事务日志链条的最后一个。事务日志备份链条的关系如下图所示:

我们使用前面“T-SQL创建事务日志备份”创建的事务日志链,使用RESTORE HEADERONLY方法来查看事务日志链的关系:

USE Master
GO
RESTORE HEADERONLY FROM DISK = N'C:\temp\AdventureWorks2008R2_log_201711122201.trn';
RESTORE HEADERONLY FROM DISK = N'C:\temp\AdventureWorks2008R2_log_201711122202.trn';
RESTORE HEADERONLY FROM DISK = N'C:\temp\AdventureWorks2008R2_log_201711122203.trn';

查询结果如下:

从这个结果展示来看,事务日志备份文件AdventureWorks2008R2_log_201711122201的LastLSN指向了的AdventureWorks2008R2_log_201711122202的FirstLSN,而AdventureWorks2008R2_log_201711122202的LastLSN又指向了AdventureWorks2008R2_log_201711122203的FirstLSN,以此来建立了事务日志备份链条关系。假如AdventureWorks2008R2_log_201711122202的事务日志备份文件丢失或者损坏的话,数据库只能还原到AdventureWorks2008R2_log_201711122201所包含的所有事务行为。
这里有一个问题是:为了防止数据库事务日志一直不断的增长,而我们又不想每次都对数据库做完全备份,那么我们就必须对数据库事务日志做周期性的日志备份,比如:5分钟甚至更短,以此来降低数据丢失的风险,以此推算每天会产生24 * 12 = 288个事务日志备份,这样势必会导致事务日志恢复链条过长,拉长恢复时间,增大了数据库还原时间(RTO)。这个问题如何解决就是我们下面章节要分享到的差异备份技术。

Differential Backup

事务日志备份会导致数据库还原链条过长的问题,而差异备份就是来解决事务日志备份的这个问题的。差异备份是备份至上一次数据库全量备份以来的所有变更的数据页,所以差异备份相对于数据库完全备份而言往往数据空间占用会小很多。因此,备份的效率更高,还原的速度更快,可以大大提升我们灾难恢复的能力。

Differential Backup工作方式

我们还是从一张图来了解数据库差异备份的工作方式:

7 a.m.:数据包含#1
10 a.m.:数据库完全备份,备份文件中包含#1
1 p.m.:数据包含#1,#2,#3,#4
2 p.m.:数据库差异备份,备份文件中包含#2,#3,#4(上一次全备到目前的变更数据)
4 p.m.:数据包含#1,#2,...,#6
6 p.m.:数据库差异备份,备份文件中包含#2,#3,#4,#5,#6
8 p.m.:数据包含#1,#2,...,#8
10 p.m.:数据库完全备份,备份文件中包含#1,#2,...,#8
11 p.m.:产生新的数据#9,#10;数据包含#1,#2,...,#10
2 a.m.:数据库差异备份,备份文件中包含#9,#10
从这个差异备份的工作方式图,我们可以很清楚的看出差异备份的工作原理:它是备份继上一次完全备份以来的所有数据变更,所以它大大减少了备份日之链条的长度和缩小备份集的大小。

T-SQL创建差异备份

使用T-SQL语句创建差异备份的方法如下:

USE master
GO
BACKUP DATABASE [AdventureWorks2008R2]
TO DISK = 'C:\Temp\AdventureWorks2008R2_20171112_diff.bak' WITH DIFFERENTIAL
GO

SSMS创建差异备份

使用SSMS IDE创建差异备份的方法:
右键点击想要创建事务日志备份的数据库 => Tasks => Backup => 选择Differential Backup Type => 选择Disk 做为备份文件存储 => 点击Add 添加备份文件 => 选择你需要存储备份文件的目录 => 输入备份文件名,如下图展示:

最后总结

本期月报分享了SQL Server三种常见的备份技术的工作方式和备份方法。数据库完全备份是数据库备份时间的一个完整拷贝;事务日志备份是上一次日志备份到当前时间的事务日志变更,它解决了数据库完全备份过程中事务日志一直增长的问题;差异备份上一次完全备份到当前时间的数据变更,它解决了事务日志备份链过长的问题。
将SQL Server这三种备份方式的工作方式,优缺点总结如下表格:

从这个表格,我们知道每种备份有其各自的优缺点,那么我们如何来制定我们的备份和还原策略以达到快速灾难恢复的能力呢?这个话题,我们将在下一期月报中进行分享。

参考

Full Backup工作方式图参考
Transaction Log Backup工作方式图参考
Differential Backup工作方式图参考

时间: 2024-10-28 01:04:34

MSSQL-最佳实践-SQL Server三种常见备份的相关文章

MSSQL · 最佳实践 · SQL Server三种常见备份

摘要 本期月报是SQL Server数据库备份技术系列文章的开篇,介绍三种常见的SQL Server备份方法的工作方式.使用T-SQL语句和使用SSMS IDE创建备份集三个层面,介绍SQL Server的三种常见备份的工作原理和使用方法.三种常见的备份包括: 数据库完全备份(Full Backup) 数据库日志备份(Transaction Log Backup) 数据库差异备份(Differential Backup) 备份的重要性 在开始分享之前,我们首先来看看数据库备份的重要性.进入

SQLServer · 最佳实践 · SQL Server优化案例分享

title: SQLServer · 最佳实践 · SQL Server优化案例分享 author: 石沫 前端时间的视频分享,这里有同学文档总结下来: 请参考:https://yq.aliyun.com/articles/60119

SQLServer · 最佳实践 · SQL Server 2012 使用OFFSET分页遇到的问题

title: SQLServer · 最佳实践 · SQL Server 2012 使用OFFSET分页遇到的问题 author: 石沫 1. 背景 最近有一个客户遇到一个奇怪的问题,以前使用ROW_NUMBER来分页结果是正确的,但是替换为SQL SERVER 2012的OFFSET...FETCH NEXT来分页出现了问题,因此,这里简单分析一下原因,更深层次的原因还没有确切的结论,但可以提供解决办法. 在升级数据库后并且应用新功能时,这个问题可能会困扰一些同学. 2. 现象 为了复现在这个

SQL Server系统数据库备份最佳实践

原文:SQL Server系统数据库备份最佳实践 首先了解主要的系统数据库: master 包含登录信息和其他数据库的核心信息 msdb 存储作业.操作员.警报.备份还原历史.数据库邮件信息等等. model 所有新数据库的模型,如果希望新数据库都有某些对象,可以在这里创建. tempdb sql server重启时重建,所以不需要备份 除了以上四种,其实还有一个数据库:Resource 从2005就引入的,一个只读.隐藏的数据库,包含所有在sql server中的系统对象.由于SQL SERV

《SQL Server企业级平台管理实践》读书笔记——关于SQL Server数据库的备份方式

原文:<SQL Server企业级平台管理实践>读书笔记--关于SQL Server数据库的备份方式 数据备份一直被认为数据库的生命,也就是一个DBA所要掌握的主要技能之一,本篇就是介绍SQL Server备份原则,SQL Server数据库分为数据文件和日志文件.为了使得数据库能够恢复一致点,备份不仅需要拷贝数据数据文件里的内容,还要拷贝日志文件里的内容.那么根据每次备份的目标不同,我们可以将备份分为数据备份和日志备份. 数据备份的范围可以是完整的数据库.部分数据库.一组文件或文件组.所以根

快速实现SQL Server数据库恢复备份_MsSql

本文为大家分享了SQL Server数据库恢复备份的两种方法,供大家参考,具体内容如下 第一种方法:通常我们采用恢复备份的方式,选择目标数据库,选择源设备进行恢复. 截图如下: 第二种方法:这种方式有时候不太方便,而脚本方式将更方便,使用脚本方式如下. /* 备份数据DB 到.bak文件.然后利用此bak文件恢复一个新的数据库DBTest. */ USE master BACKUP DATABASE DB TO DISK = 'e:\DBTest.bak' RESTORE FILELISTONL

SQL Server 数据库使用备份还原造成的孤立用户和对象名‘xxx’无效的错误的解决办法

server|备份|错误|对象|解决|数据|数据库 介绍SQL Server 数据库使用备份还原造成的孤立用户和对象名'xxx'无效的错误的解决办法          在使用数据库的过程中,经常会遇到数据库迁移或者数据迁移的问题,或者有突然的数据库损坏,这时需要从数据库的备份中直接恢复.但是,此时会出现问题,这里说明几种常见问题的解决方法.一.孤立用户的问题比如,以前的数据库的很多表是用户test建立的,但是当我们恢复数据库后,test用户此时就成了孤立用户,没有与之对应的登陆用户名,哪怕你建立

SQL Server 2008、SQL Server 2008R2 自动备份数据库

让SQL Server 2008自动备份数据库,需要建立一个SQL Server作业,并启动SQL Server代理,使该作业定期运行. 具体来说,可以按以下步骤进行: 一.打开SQL Server Management Studio,在对象资源管理器中,确认SQL Server代理已启动,若没有,右击SQL Server代理节点,点击"启动". 二.展开SQL Server 代理节点,在其下的作业文件夹上右击,选择"新建作业". 三.在弹出的"新建作业&

对 sql server 数据库的备份进行加密

原文:对 sql server 数据库的备份进行加密 嗯,最近在研究数据库备份相关的东西,考虑到应该为数据库备份加个密,就准备从网上搜索一下看看有什么好办法,没想到还挺乱... 首先,我从网上搜到的,对数据库备份加密的方法,主要有三种:   1.在使用 BACKUP 语句时,添加 PASSWORD 参数[此方法适用于 sql server 2012 以前的版本(不包含 2012)] 不过呢,其实这个 PASSWORD 参数的加密,并不是我们想象中的对数据完整的加密. 根据 MSDN 中的介绍:h