图片 12

sql server 备份与恢复系列一 必备知识

二. 数据库恢复模式下的备份类型

    上面说了备份涉及的几种类型,这里就得说数据库恢复模式对备份类型的支持及特点。sql
server有三种数据库恢复模式设置包括:简单恢复模式,完整恢复模式,大容量恢复模式。

图片 1

  2.1  简单恢复模式
    在简单恢复模式下,不能做日志备份,只支持最简单的备份和还原方式,容易管理,数据库最后一次备份之后做的数据修改将全部丢失。为了降低风险,可以引入差异备份。差异备份的开销一般都比完整备份低,可以经常运行。如果数据库比较庞大或者不允许长时间的数据丢失,那这种简单恢复模式就不适合。在总结下:

    优点:

    (1)日志文件占用物理空间少日志增长慢。

    (2)对SQL执行性能优,能最小化日志。

    缺点:

    (1)不支持日志备份.

    (2)无法实现零丢失,恢复时间点至上一次备份时。

    (3)切换到其它恢复模式时,日志链中断。

  2.2 大容量恢复模式

    又叫大批量恢复模式,可以使用日志备份,它能够对某些大批量操作提供最佳的性能和最小的日志使用空间,这些大批量包括bulk
insert, bcp,create index, select into, writetext ,
updatetext。也就是说这些操作有数据丢失风险,相对完全恢复模式,这些操作都是完全记录的。总结下:

     优点:

         (1)日志文件占用物理空间少(日志增长慢)。

         (2)对SQL执行性能优(最小化日志)。

         (3)支持切换到完整模式不中断日志链。

    缺点:

         (1)还原大批量操作,数据有丢失风险如bulk insert, select
into等。

  2.3 完整恢复模式

    也可以叫完全恢复模式,在此模式下,所有操作都会被完整记录下来,如insert每新增的一行,delete每删除的一行,还包括大批理操作如bulk
insert等,都会记录到事务日志中。 包括create
index操作也会被完全记录,在日志恢复时不必要重建索引,恢复会很快。使用日志备份,可以定义一种很频繁的频率,5份钟甚至更短时间来做备份,以防止出现故障数据丢失。但是备份数量越多,恢复时需要严格按备份产生的顺序依次恢复,中间不能有任何备份缺失。

    优点:

    (1)
使用了日志备份可以实现零丢失(如果能进行尾日志备份,能还原到任意时间点)。

    (2)支持切换到大容量模式不中断日志链。

    缺点:

    (1)日志文件空间占用大必须定期日志备份,达到日志空间重用。

full与bulk-logged恢复模式下的备份

在创建第一个日志备份之前,必须先创建完整备份(如数据库备份或一组文件备份中的第一个备份)。
仅使用文件备份还原数据库会较复杂。
因此,建议您尽可能从完整数据库备份开始。 此后,必须定期备份事务日志。
这不仅能最小化工作丢失风险,还有助于事务日志的截断。
通常,事务日志在每次常规日志备份之后截断。
  在full恢复模式或bulk-logged日志恢复模式下,以下两种情况视为数据库未处于完整日志维护状态:

  • 从未进行过全库备份
  • 数据库设置为简单模式

未处于完整日志维护状态时,执行checkpoint后,SQL Server会直接重用reusable
VLF。
  SQL
Server的日志归档(即事务日志备份)要用户手工进行,或者设置为自动执行的作业。SQL
Server数据库处于完全恢复模式,而且配置了事务日志备份的自动作业,即SQL
Server自动执行事务日志备份,在执行一次完整备份后,其数据库所处状态类似于Oracle数据库的归档模式,区别是SQL
Server定时执行事务日志备份,而不是在VLF写满时执行,而Oracle是当前日志组写满时,在日志切换的同时执行日志归档。而简单恢复模式就类似于Oracle的非归档模式。

恢复模式 大容量操作的记录方式 优点 缺点 说明 工作丢失的风险
full 影响的数据都记录 全库备份后,处于日志维护模式,保持连续的日志链,执行数据恢复时,一般不会有数据丢失 日志文件的大小会因为大容量操作而急剧增加 需要日志备份 正常情况下没有。如果日志尾部损坏,则必须重做自最新日志备份之后所做的更改。
bulk-logged 发生变化的区记录在Bulk Changed Map数据页中 通过使用最小方式记录大多数大容量操作,减少日志空间使用量。 有关尽量减少日志量的操作的信息 事务日志备份的数据量会很大 需要日志备份,是完整恢复模式的附加模式,允许执行高性能的大容量复制操作。 如果在最新日志备份后发生日志损坏或执行大容量日志记录操作,则必须重做自该上次备份之后所做的更改。否则不丢失任何工作。
simple 与bulk-logged方式相同 日志文件的VLF会不断重用,日志文件一般不需要自动增长 VLF不能保持连续的日志链,执行数据恢复时会有数据丢失。在发生灾难时,这些更改必须重做。 无日志备份 最新备份之后的更改不受保护。 在发生灾难时,这些更改必须重做。

一.备份概述

  数据安全是数据库的生命,数据库在使用过程中难免会遇到如:使用者的误操作或是被恶意修改,硬件故障导致数据文件无法被访问,自然灾害导致机房在物理上的损毁。本章从备份与恢复的功能作为解决问题的切入点。在实际工作中会遇到:使用什么样的备份策略(比如完整备份,文件备份,差异备份,日志备份),如何减少备份恢复时间(比如尽快恢复上线),如何将数据库恢复到想要的时间点(比如恢复到误操作以前),如何迁移数据库系统到一台新机器(比如用户账号,密码,任务脚本备份还原)。

  1.备份类型

     在sql
server数据库里包括数据文件和日志文件,相应包括数据备份和日志备份。数据备份可以是完整数据库备份,文件备份,差异备份也叫增量备份。日志备份也叫事务日志备份。

完整备份

   会记录数据库里的所有信息,可以将数据库数据恢复到某个时间点的状态。但一个大的数据库备份可能

需要很长时间。假如每天或每小时只用完整备份类型就需要发费大量存储空间和备份恢复时间,仅完整备份不能满足用户需求。

文件备份

   备份一个或多个文件或文件组的所有数据,多数针对大型数据库。文件备份+日志备份=完整备份。如果是一个文件损坏,只需还原该文件,从而加快恢复速度。

差异备份                              要求数据库之前做过一次完整备份称为基准。它是完整备份以后,发生更改的数据. 便于频繁备份,降低数据丢失的风险。
日志备份   要求数据库之前做过一次完整备份,自从上次完整备份或日志备份以后写入的日志记录。连续不断的日志链可以将数据库还原到任意时间点。 所以在备份策略中扮演重要角色。

   2.  备份策略

    (1)数据库最多能容忍多长时间的数据丢失。
    (2)投入多少人力物力做数据库备份和恢复策略。每次备份都会有时间间隔,数据丢失容易发生在最近一次备份之后的所有数据库操作,之后如文件损坏数据库需要恢复,备份尾日志肯定不成功,数据也会丢失,
为了保证数据不丢失需要引用镜像等技术。
    (3)
备份文件越多,数据库恢复的文件也越多,要建立一个合适的备份管理制度。备份虽然不会阻塞数据库的正常操作,但会产生一系列的磁盘读写,这时要避免在服务器I/O繁忙时。备份越多,失败的概述也会越大,需要管理员及时处理错误,将备份任务恢复常态。

  3. 常用的备份方法

分级

数据备份

日志备份

数据库级

完整数据库备份

差异数据库备份

日志备份

文件级

完整文件备份

差异文件备份

 

图片 2

  SQL
Server数据库有3种恢复模式:完整(full)恢复模式、大容量日志(bulk-logged)恢复模式、简单(simple)恢复模式。

在SQL
Server中,数据库不能像Oracle数据库一样设置归档模式,但是可以进行事务日志备份,其作用等同于Oracle数据库的日志文件归档。
  SQL Server 备份和还原操作发生在数据库的恢复模式的上下文中。
恢复模式旨在控制事务日志维护。“恢复模式”是一种数据库属性,它控制如何记录事务,事务日志是否需要(以及允许)进行备份,以及可以使用哪些类型的还原操作。可以通过在SSMS里或通过SQL语句进行配置恢复模式:

简单恢复模式(Simple Recovery Mode)

在simple恢复模式下,日志文件的作用仅仅是保证了SQL
Server事务的ACID属性,并不承担具体的恢复数据的角色,正如”simple”这个词的字面意思一样,数据的备份和恢复仅仅是依赖于手动备份和恢复。
  在simple恢复模式下,checkpoint进程启动后,会把数据库日志文件中不包含活动事务(未结束事务)的VLF状态修改为reusable,从而VLF会不断重用(也称为事务日志被截断),执行checkpoint进程后,数据缓冲区中的脏数据会写入磁盘。因为VLF不断被重用,如果没有执行大的事务,日志文件的大小一般不会自动增长。但是因为VLF不断被重用,日志文件中的VLF显然不可能保持一个连续的序列,日志备份也就没有必要了。

在简单恢复模式下,日志几乎是不用进行管理的。每一次CheckPoint都有可能截断日志,从而来回收不活动的VLF以便重复利用空间。因此在简单恢复模式下,日志的空间使用几乎可以不去考虑。

事实上,在simple模式下SQL
Server不允许对数据库执行日志备份,而只能进行完整备份及差异备份
,这样发生故障时可能会有数据丢失,因此,simple模式一般在开发环境或测试环境下使用,生产数据库很少使用这种模式运行。

图片 3

  我们在每周一0点做一次完整备份,在周三0点和周五0点分别做差异备份。在简单恢复模式下,如果周六数据库崩溃。我们的恢复计划只有根据周一0点的做的完整备份恢复后,再利用周五0点的差异备份进行恢复,而周五0点之后到服务器崩溃期间所有的数据将会丢失。

所以在简单恢复模式下,每次备份后,如果出现严重故障,数据库将有可能丢失工作,每次更新都会增加丢失工作的风险,这种情况将一直持续到下一次备份。这时,工作丢失风险将变为零,并开始新一轮的工作丢失风险。
备份之间的工作丢失风险随着时间的推移而增加。
下图显示了仅使用完整数据库备份的备份策略的工作丢失风险。

图片 4

  对于大容量操作在日志文件中的记录方式,simple模式下与bulk-logged模式相同。
  要注意的是,在simple模式下,日志文件的大小不一定总是很小,当有包含很多操作的事务长时间未结束时,checkpoint不能阶段包含这个事务的VLF,从而日志文件也可能很大。

大容量日志(Bulk-logged)恢复模式

bulk-logged恢复模式与full恢复模式的区别只是对大容量操作记入重做日志的处理方式不同,其他方面都是相同的。
  在full恢复模式下,对数据库的每一项操作都会记录在日志中。而对于某些大量数据的导入导出操作,无疑会在日志中留下大量记录。很多情况下,我们并不需要将这些信息记录在日志中。
  SQL Server中有一类Bulk Changed
Map数据页,其中的每个位对应数据库中的一个区,在bulk-logged恢复模式下,只会在Bulk
Changed
Map数据页中记录大容量操作导致内容发生变化的区的信息,即从上次日志备份以来,数据发生变化的区在Bulk
Changed
Map数据页中对应的位会被设置为1,从而大容量操作所产生的重做数据量会显著减少,使得执行效率能够有很大提高,这是其优点。

图片 5

  因为在bulk-logged恢复模式下,日志文件中并未记录大容量操作影响的数据,在日志备份时,除了备份日志本身以外,还会备份其中记录的大容量操作影响的那些区的内容,从而会导致日志备份的大小急剧增加,这是其缺点。使用bulk-logged恢复模式下进行的数据库备份或日志备份进行恢复操作时,所花费的时间与full模式类似。

简单来说,full模式下,大容量操作会导致事务日志量急剧增加,在bulk-logged模式下,大容量操作会导致事务日志备份数据量急剧增加。

大容量日志恢复模式作为完整恢复模式的备选方案。微软推荐的最佳实践是在进行大量数据操作时(比如索引的创建和rebuilt,select
into操作等),暂时由完整恢复模式切换到大容量恢复模式来节省日志。这个转换并不会破坏日志链。

完整(Full)恢复模式

完整恢复模式通过将对数据库的任何修改记录到日志来给予数据最大程度的保护。在完整恢复模式下,日志的作用不仅仅是保证了数据库事务的ACID,并且还可以使数据恢复到在日志范围内的任何时间点。
  与简单恢复模式相反,在完整恢复模式下,日志作为恢复数据的重要组成部分,日志的管理和对日志空间使用的管理则需要重视。
  大容量操作是指bulk insert、bcp、select into、create
index、writetext等操作。执行一个大容量操作命令,会导致大量数据受影响,full模式下,对大容量操作影响的数据都记入事务日志,如执行select
into命令对一个表添加了1000条数据,则在事务日志中会记录这1000条记录的所有数据,从而在执行大容量操作时,可能会导致产生大量重做日志,影响运行效率。
  在full与bulk-logged恢复模式下,如果没有对数据库执行完整备份,则与simple恢复模式相同。对设置了full恢复模式或bulk-logged日志恢复模式的数据库执行一次全库备份后,除非执行了事务日志备份,否则VLF不会被重用,从而维持一个连续的日志链,当数据库出现故障时,可以将其恢复至数据库出现故障的时刻。这种情况称为数据库处于完整日志维护状态。但是这样会导致事务日志文件的大小不断增长,如果最后导致没有可用的空间,则可以执行事务日志备份,或把数据库恢复模式修改为simple,以截断日志文件,释放空间。

在完整恢复模式下,CheckPoint不会截断日志。只有对日志的备份才会将MinLSN向后推并截断日志。因此在一个业务量稍大的系统中,日志的增长速度将会变得很快。

图片 6

  如上图,在DB_1处做了完整备份,并且接下来两次分别做了两次日志备份(Log_1和Log_2),在Log_2备份完不久服务器由于数据所在磁盘损坏。这时如果日志文件完好,则可以通过备份尾部日志(Tail
of
log)后,从DB_1开始恢复,依次恢复Log_1,Log_2,尾部日志来将数据库恢复到灾难发生时的时间点。理论上可以使数据的损失为0。
  下图显示了在完整恢复模式下可以使用的复杂性最小的备份策略。

图片 7

从日志恢复数据的原理是Redo,也就是将日志中记载的事务再重做一遍。这个开销和从完整或差异备份中恢复相比要大很多。因此尽可能的减少利用日志的恢复量。而使用完整或者差异备份来恢复更多的数据。

图片 8

日志链(Log Chain)

日志备份的连续序列称为“日志链”。 这个概念可以用下图表示:

图片 9

  假设上面两个日志备份可以简单抽象成如上2个备份,则日志备份1的末尾LSN必须小于等于日志备份二的第一个LSN(通常情况下是第一个末尾LSN等于第二个日志备份的第一个LSN),则这两个备份的日志链是连续的。
  下图是一个生产环境下,在SSMS中查看日志链连续的例子:

图片 10

  可以看出,第一次完整备份后,备份多次事务日志,每一个事务日志的开始LSN都等于上一个事务日志的结束LSN。因此可以从第一次完整备份开始,恢复到最后一个日志备份期间的任何时间点。
  完整的日志链以第一次完整备份或由简单恢复模式转为完整或大容量日志模式开始,到当前的时间点结束。除非在创建完整数据库备份时选择覆盖现有备份集,否则现有的日志链将保持不变。所以事务日志备份“日志链”
的序列与数据备份无关。 例如,假设有下列事件顺序:

图片 11

  事务日志备份序列是连续的,从创建初始完整数据库备份的时间(上午 8:00)
到创建最后事务日志备份的时间(晚上8:00)。
  若要将数据库还原到故障点,必须保证日志链是完整的。
也就是说,事务日志备份的连续序列必须能够延续到故障点。

使用日志备份来还原到故障点

假设有下列事件顺序。

图片 12

若要将数据库还原到晚上 9:45(故障点)时的状态,
可以使用以下两种备选过程:

备选过程 1:使用最新的完整数据库备份还原数据库

  1. 失败时创建当前活动事务日志的结尾日志备份。
  2. 不要还原上午 8:00 的 所需的时间长。 相反,应还原下午 6:00 的
    这一时间更近的完整数据库备份,然后应用晚上 8:00 的
    日志备份和结尾日志备份。

备选过程 2:使用较早的完整数据库备份还原数据库
  注意:如果因某个问题而无法使用下午 6:00
的完整数据库备份,则此备选过程很有用。 所需的时间长。 此过程比从下午
6:00 的完整数据库备份还原 所需的时间长。

  1. 失败时创建当前活动事务日志的结尾日志备份。
  2. 还原上午 8:00 的
    完整数据库备份,然后按顺序还原所有四个事务日志备份。
    所有完成的事务都将前滚到晚上 9:45。

此备选过程指出了冗余安全性,该安全性通过维护一系列完整数据库备份中的事务日志链备份来获得。