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

一.备份概述

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

  1.备份类型

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

完整备份

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

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

文件备份

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

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

   2.  备份策略

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

  3. 常用的备份方法

分级

数据备份

日志备份

数据库级

完整数据库备份

差异数据库备份

日志备份

文件级

完整文件备份

差异文件备份

 

图片 1

切换模式

如果你曾从完整或大容量日志模式切换到简单模式,这会中断日志链,你只能恢复数据库到在你切换前,上一次日志备份的时间点。因此,不建议在切换前马上进行日志备份。如果你马上从简单模式切换到完整或大容量日志模式,记住数据库实际上会继续运行在自动截断模式(刚才显示的NULL值),直到你进行了另一个完整备份。

如果你从完整切换到大容量日志,那这不会中断日志链。但是在大容量模式里发生的任何大容量操作不会在事务日志里完整记录,因此不能在操作上控制,同样的方法里完整记录可以。这表示恢复数据到包含大容量操作事务日志里的时间点是不可能的。你只能恢复到日志文件尾。为了“重新启用”到时间点的恢复,在大容量操作完成后切换回完整模式,并立即进行一次日志备份。

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

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

图片 2

  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)日志文件空间占用大必须定期日志备份,达到日志空间重用。

简单恢复模式(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,从而日志文件也可能很大。

恢复模式

SQL
Server数据库备份和恢复操作发生在数据库恢复模式的上下文里。恢复模式是决定你是否需要(或甚至可以)备份事务日志和操作如何记录的数据库属性。对于可用恢复操作,还有页粒度和文件恢复都有一些不同,但这个系列文章不会讨论这些。

一般来说,数据库会运行在简单和完整恢复模式,它们之间的重用区别如下:

  • 简单(SIMPLE)——事务日志只用作数据库恢复和回滚操作。在检查点期间是自动截断。它不会被备份,因此它不能用于还原数据库到过去存在的某个时间点。
  • 完整(FULL)——事务日志在检查点期间不会自动截断,因此可以被备份并用来还原数据到先前的某个时间点,也用作数据库恢复和回滚。只有当日志备份发生时,日志文件会截断。

还有第3个模式,大容量日志(BULK_LOGGED),在这个特定操作里,通常会生成很多写入到事务日志,为了不淹没事务日志而进行很少的记录。

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

假设有下列事件顺序。

图片 5

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

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

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

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

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

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


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

不能最小记录的操作

可以被最小记录的操作包括大容量导入操作(例如使用BCP或BULK
INSERT),SELECT/INTO操作和特定索引操作(例如索引重建)。完整列表可以在这里找到:

日志链(Log Chain)

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

图片 8

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

图片 9

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

图片 10

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

日志链和尾日志备份……

会在第5篇——完整恢复模式里的日志管理里详细介绍。

当然,使用完整备份会带来更多的维护,创建和监控用来频繁运行事务日志备份的作业,这些都要额外工作,这些备份需要I/O资源(尽管只是短时间),需要磁盘空间来存储大数量的备份文件。对数据库选择合适的恢复模式前,在业务层面,这些都要慎重考虑这些。

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website