澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站:数据库备份这点事儿,备

2019-05-19 作者:数据库网络   |   浏览(170)

一.概述

  前面介绍了简单恢复模式和大容量恢复模式,这篇继续写完整恢复模式下的备份与还原。在完整恢复模式里最大的优点是只要能成功备份尾日志,就可以还原到日志备份内包含的任何时点("时点恢复")。当然对比前二种模式它是牺牲了磁盘I/O性能。

恢复模式

备份策略

数据安全性

I/O性能

简单恢复

完整备份 差异备份

安全最差。最后一次备份之后,所有数据操作丢失。

最优

大容量恢复

完整备份 差异备份 日志备份

折中。批量操作有丢失风险。尾日志备份失败。最后一次备份之后,所有数据操作丢失

折中

完整恢复

完整备份 差异备份 日志备份

相比上面二种最安全。尾日志备份失败。最后一次备份之后,所有数据操作丢失

最差

  在完整恢复模式下,最常见的备份策略,如下图所示:澳门新萄京官方网站 1

一.备份概述

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

  1.备份类型

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

完整备份

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

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

文件备份

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

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

   2.  备份策略

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

  3. 常用的备份方法

分级

数据备份

日志备份

数据库级

完整数据库备份

差异数据库备份

日志备份

文件级

完整文件备份

差异文件备份

 

一. 概述

  在sql server 备份与恢复系列的第一篇里,有讲到大容量模式下备份与还原的相关知识。这篇重点来演示在大容量模式下常用的备份与还原模式“完整备份 差异备份 日志备份”。 在大容量恢复模式下,特别要注意的是在什么情况下会导致数据还原丢失风险,带着这个问题,来进行演示说明。备份策略如下图所示:

澳门新萄京官方网站 2

原始出处:

写在前面

  最近一直在整理数据库最佳实践的东西,我也会将各种文章建议,同步到博客园,希望能够帮助更多的人了解数据库,轻松玩转数据库,同时也减轻运维人员的工作压力,毕竟熟能生巧,熟练既是效率。

  数据库备份老生常谈的话题,一搜索数据库备份可能上千上万篇,那么为什么还要写一篇?因为重要!而往往却不能引起运维人员的重视。上周还帮助一个客户恢复了数据,原因是断电,启动服务器后发现磁盘损坏,重要的系统页大面积损坏。使用常规数据库恢复手段全无用,使用第三方恢复工具也只能恢复部分数据,根本无法满足业务的正常运转,数据是企业的命根子,丢了,找不回来怎么办? 难道要经历一次这样的洗礼才能体会到备份的重要性么?

  数据库备份是个很重的话题,太多东西无法写在同一篇文章中,另外这是一篇大量文字的扫盲文章,不足之处希望大家多多包涵。

二. 备份

  在前章中讲到了大容量恢复模式下的备份。备份策略与大容量模式是一样的,同样是完整备份 差异备份 日志备份。这里要突出点是:当误操作发生后,如何还原到误操作之前的一分钟,找出误操作之前的数据。
在"sql server 日志文件结构及误操作数据找回"中有介绍误操作数据找回,但是基于第三方工具ApexSQL Log。虽然该工具方便,但要收费哟。

  我这里有一个BackupTest库,库里有个Employees表

use master
--设置完全模式
ALTER DATABASE BackupTest SET  RECOVERY FULL  
--创建备份设备(有就不要执行)
use master
exec sp_addumpdevice 'disk', 'BackupTestDevice','F:SqlServicebackupBackupTestBackup.bak'
go
--做一次完整备份到备份设备中(备份基准)
backup database  BackupTest to BackupTestDevice

--新增数据
insert BackupTest.dbo.Employees values('湖南长沙')
insert BackupTest.dbo.Employees values('湖南湘潭')
--日志备份
backup log BackupTest to BackupTestDevice

 备份集如下所示:

澳门新萄京官方网站 3

-- 误操作发生, 忘记加where条件,操作时间是:2018-8-12 10:55  
delete from BackupTest.dbo.Employees 

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

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

澳门新萄京官方网站 4

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

二.备份

    我这里有TestBulkLogged库,库里新建了一个product空表。备份SQL语句如下所示:

use master
-- 设置大容量模式
ALTER DATABASE TestBulkLogged SET RECOVERY bulk_logged

-- 做一次完整备份到备份设备中(备份基准) 
backup database  TestBulkLogged to BackupTestDevice

-- 新增
insert into TestBulkLogged.dbo.product(model,upbymemberid,brand) values('第一次新增数据',9708,'IT')

-- 做一次日志备份
backup log   TestBulkLogged to BackupTestDevice

-- 批量插入(5998 行受影响)
insert into TestBulkLogged.dbo.product(model,upbymemberid,brand)
select model,upbymemberid,brand from test.dbo.product

-- 做二次日志备份
backup log   TestBulkLogged to BackupTestDevice

-- 第二次日志备份后的新增
insert into TestBulkLogged.dbo.product(model,upbymemberid,brand) values('第二次新增数据',9708,'IT')

-- 做差异备份
backup database  TestBulkLogged to BackupTestDevice with differential 

-- 全部删除(6000 行受影响)
delete from TestBulkLogged.dbo.product

  查看备份集列表如下图所示:

澳门新萄京官方网站 5

 

一些名词

  完整数据库备份:完整数据库备份就是复制数据库里的所有信息,通过单个完整备份,就能将数据库恢复到某个时间点的状态。

注:由于数据库备份是一个在线的操作,一个大的完整数据库备份可能需要一个小时甚至更长的时间,数据库在这段时间里还会发生变化,所以完整数据库备份还要对部分事务日志进行备份,以便能够恢复数据库到一个事务一致的状态。

  文件备份:文件备份指备份一个或多个文件或文件组中的所有数据。

注:在完整恢复模式下,一整套完整文件备份和涵盖所有文件备份的日志备份合起来等同于完整数据库备份。

使用文件备份能够只还原损坏的文件,而不用还原数据库的其余部分,从而可加快恢复速度。例如,如果数据库由位于不同磁盘上的若干个文件组成,在其中一个磁盘发生故障时,只需还原这个故障磁盘上的文件的备份,其他磁盘上的文件无须还原,这样会缩短还原时间。
  部分备份:部分备份与完整数据库备份类似,但是部分备份默认只包含数据库可读写部分,数据库的只读文件将不会被备份。

注:因为只读部分是不会发生变动的,总是去备份它有点浪费时间与精力所以部分备份在希望不备份只读文件组时非常有用。部分备份可以说是数据库备份和文件备份之间的一个中间类型。如果一个数据库里没有只读文件,那么部分备份和数据库备份就没什么差别。 

  差异备份:差异备份要求数据库之前做过一次完整备份。差异备份仅捕获自该次完整备份后发生更改的数据,这个完整备份被称为差异备份的“基准”。差异备份仅包括建立差异基准后更改的数据。差异备份比差异基准更小且更快,便于执行频繁备份,从而降低了数据丢失的风险。

  日志备份:数据备份集中精力于数据文件的备份。对于日志文件,相应地有事务日志备份。每个日志备份包括创建备份时处于活动状态的部分事务日志,以及先前日志备份中未备份的所有日志记录。不间断的日志备份序列包含数据库的完整(即连续不断的)日志链。在完整恢复模式下(或者在大容量日志恢复模式下的某些时候),连续不断的日志链可以将数据库还原到任意时间点。

  尾日志备份:“结尾日志备份”捕获尚未备份的任何日志记录(“结尾日志”),以防丢失所做的工作并确保日志链完好无损。 在将 SQL Server 数据库恢复到其最近一个时间点之前,必须先备份数据库的事务日志。 结尾日志备份将是数据库还原计划中相关的最后一个备份。

注意:并非所有还原方案都要求执行结尾日志备份。 如果恢复点包含在较早的日志备份中,则无需结尾日志备份。 此外,如果您准备移动或替换(覆盖)数据库,并且在最新备份后不需要将该数据库还原到某一时间点,则不需要结尾日志备份。

  仅复制备份(Copy-Only):独立于常规SQL Server备份序列的SQL Server备份。通常,进行备份会更改数据库并影响其后备份的还原序列。但是,有时在不影响数据库全部备份和还原过程的情况下,为特殊目的而进行备份还是有用的。为实现此目的,SQL Server引人了下列两种仅复制备份
  (1)仅复制完整备份
仅复制完整备份也备份整个数据库的内容。它和正常的完整备份的区别是,做完了以后差异备份的基准不会变,因此不影响差异备份序列。
  (2)仅复制日志备份
仅复制日志备份只备份当前日志文件里现有的内容,但是不会清空日志文件里备份下的日志。因此,下次再做正常日志备份的时候,这些内容还会被再次备份下来,从而不影响常规日志备份的序列。这种备份主要用在以下情况:数据库上已经有了一个备份计划任务在运行,但是现在需要紧急做一个日志备份,但同时不能影响到原有的备份序列。

  恢复模式:SQL Server 备份和还原操作发生在数据库的恢复模式的上下文中。 恢复模式旨在控制事务日志维护。 “恢复模式”是一种数据库属性,它控制如何记录事务,事务日志是否需要(以及允许)进行备份,以及可以使用哪些类型的还原操作。 有三种恢复模式:简单恢复模式、完整恢复模式和大容量日志恢复模式。 通常,数据库使用完整恢复模式或简单恢复模式。 数据库可以随时切换为其他恢复模式。

恢复模式 说明 工作丢失的风险 能否恢复到时点?
Simple 无日志备份。

自动回收日志空间以减少空间需求,实际上不再需要管理事务日志空间。 有关简单恢复模式下数据库备份的详细信息,请参阅完整数据库备份 (SQL Server)

简单恢复模式不支持要求事务日志备份的操作。 在简单恢复模式中不能使用以下功能:

-日志传送

-AlwaysOn 或数据库镜像

-没有数据丢失的介质恢复

-时点还原
最新备份之后的更改不受保护。 在发生灾难时,这些更改必须重做。 只能恢复到备份的结尾。 有关详细信息,请参阅完整数据库还原(简单恢复模式)。 

有关简单恢复模式的更多深入说明,请参阅由 MSSQLTips! 人员提供的 SQL Server 简单恢复模式
Full 需要日志备份。

数据文件丢失或损坏不会导致丢失工作。

可以恢复到任意时点(例如应用程序或用户错误之前)。 有关完整恢复模式下的数据库备份的信息,请参阅 完整数据库备份 (SQL Server) 和完整数据库还原(完整恢复模式)
正常情况下没有。

如果日志尾部损坏,则必须重做自最新日志备份之后所做的更改。
如果备份在接近特定的时点完成,则可以恢复到该时点。 有关使用日志备份还原到故障点的信息,请参阅将 SQL Server 数据库还原到某个时间点(完整恢复模式)

注意:如果有两个或更多必须在逻辑上保持一致的完整恢复模式数据库,则最好执行特殊步骤,以确保这些数据库的可恢复性。 有关详细信息,请参阅包含标记的事务的相关数据库的恢复
大容量日志 需要日志备份。

是完整恢复模式的附加模式,允许执行高性能的大容量复制操作。

通过使用最小方式记录大多数大容量操作,减少日志空间使用量。 有关尽量减少日志量的操作的信息,请参阅事务日志 (SQL Server)

有关大容量日志恢复模式下的数据库备份的信息,请参阅完整数据库备份 (SQL Server) 和完整数据库还原(完整恢复模式)
如果在最新日志备份后发生日志损坏或执行大容量日志记录操作,则必须重做自该上次备份之后所做的更改。

否则不丢失任何工作。
可以恢复到任何备份的结尾。 不支持时点恢复。

三.还原(1)

  当误操作发生后,是需要找管理员来进行数据还原。 如果数据库太大,还原是需要很长时间(注意使用副本,不要使用生产库)。 这种情况下就需要等待了。 避免的方法:(1)是做sql审核,不在Managemnet studio里直接操作,避免此类事情发生.(2)是使用粒度更小的备份方式,但相应的复杂些。

--步骤1 备份尾日志
use master
go
backup log BackupTest to BackupTestDevice with norecovery 

澳门新萄京官方网站 6

go
--步骤2 从备份恢复一个全备份 ,norecovery(正在还原...)不可读写. file指备份集位置号
restore database BackupTest from BackupTestDevice with file=19, norecovery --事务不恢复

--步骤3 
restore log BackupTest from BackupTestDevice  with file=20,  norecovery --事务不恢复

--步骤4 用stopat恢复到10:54
restore log BackupTest from BackupTestDevice  with file=21, stopat='2018/8/12 10:54', recovery --事务恢复

--数据又回来了
select * from  BackupTest.dbo.Employees 

  澳门新萄京官方网站 7

三. 还原(1)批量插入的是否会丢失

  通过还原查看批量插入操作是否丢失,在备份尾日志时如果报错, 信息如下:"因为数据库正在使用,所以无法获得对数据库的独占访问权" 需要将库设置成单用户模式

use master

-- 先还原完整备份 ,norecovery(正在还原...)不可读写. file指备份集位置号
restore database TestBulkLogged from BackupTestDevice with file=10, norecovery 

    澳门新萄京官方网站 8

   在大容量模式下还原时,sql server会检测你是否进行了尾日志备份,也是确保最后一次日志备份后,所做的数据操作在还原后不丢失。(如果尾日志备份失败,则丢失数据)。下面先备份一下尾日志, 使用norecovery 暂不提交

-- 尾日志备份
backup log TestBulkLogged to BackupTestDevice with norecovery 

澳门新萄京官方网站 9

 上图备份了尾日志后,备份集里多出了一个文件号14, 下面在重新还原完整备份

-- (重新)从备份恢复一个全备份 ,norecovery(正在还原...)不可读写. file指备份集位置号
restore database TestBulkLogged from BackupTestDevice with file=10, norecovery 

    澳门新萄京官方网站 10

-- 恢复到日志文件11  
restore database TestBulkLogged from BackupTestDevice  with file=11, norecovery

-- 恢复到日志文件12  
restore database TestBulkLogged from BackupTestDevice  with file=12, recovery

    澳门新萄京官方网站 11

 接下来我们来查询下库中的product表,查看数据是否全部恢复。

-- 查询大批量操作的数据,是否已还原出来
select * from TestBulkLogged.dbo.product

  澳门新萄京官方网站 12

  结论:通过上图我们可以了解到,第一次和第二次做的日志备份都完美的还原了过来。 大批量插入操作也得到了还原。证明在大容量模式下,大批量操作的数据, 还原恢复可能存在丢失的风险,但不一定会丢失掉

无论是数据库Dev还是DBA,都希望关键业务数据库的完整性和可用性能得到保障,数据库备份是一种不错的选择。SQL Server 2008支持不同应用层次的多种备份方式,为我们的业务数据提供了强有力的保障,这一篇博文就来探讨如何在SQL Server 2008下设计合理的备份策略。

常规建议

四.还原(2)

澳门新萄京官方网站:数据库备份这点事儿,备份与还原种类四。  在前面介绍中,有讲过,完整恢复模式切换到大容量模式,日志链是不会中断。下面来验证

--从完整恢复模式切换到大容量模式
ALTER DATABASE BackupTest SET  RECOVERY bulk_logged 
-- 新增
insert BackupTest.dbo.Employees values('湖南株洲')
--日志备份
backup log BackupTest to BackupTestDevice
-- 删除
delete from BackupTest.dbo.Employees 

-- 尾日志
backup log BackupTest to BackupTestDevice with norecovery 

 备份集如下所示,日志文件ID:22是在大容量模式下备份的,23是尾日志

澳门新萄京官方网站 13

restore database BackupTest from BackupTestDevice with file=19, norecovery --事务不恢复
restore log BackupTest from BackupTestDevice  with file=20,  norecovery --事务不恢复
restore log BackupTest from BackupTestDevice  with file=21,  norecovery --事务不恢复
restore log BackupTest from BackupTestDevice  with file=22,  recovery 

  当日志还原到文件ID:22时,报错,如下图所示

澳门新萄京官方网站 14

   跳过文件ID:22, 使用23来提交事务,也会报错,如下所示:

restore log BackupTest from BackupTestDevice  with file=23,  recovery

澳门新萄京官方网站 15

   经过测试,还原失败,错误是指:与上一次还原到指定时间点有关系。

  下面在测试一个新库TestFULLToBulk

--设置完全模式
ALTER DATABASE TestFULLToBulk SET  RECOVERY FULL  
--做一次完整备份到备份设备中(备份基准)
backup database  TestFULLToBulk to BackupTestDevice
insert TestFULLToBulk.dbo.product values('湖南株洲')
--日志备份
backup log TestFULLToBulk to BackupTestDevice
--设置大容量
ALTER DATABASE TestFULLToBulk SET RECOVERY bulk_logged   

insert TestFULLToBulk.dbo.product values('湖南湘潭')
--日志备份
backup log TestFULLToBulk to BackupTestDevice

  备份集如下:文件ID28是在大容量下进行的备份

  澳门新萄京官方网站 16

backup log TestFULLToBulk to BackupTestDevice with norecovery 
go
restore database TestFULLToBulk from BackupTestDevice with file=26, norecovery 
go
restore log TestFULLToBulk from BackupTestDevice  with file=27,  norecovery 
go
restore log TestFULLToBulk from BackupTestDevice  with file=28,  recovery 

  上面还原成功,证明了完整恢复模式切换到大容量模式,日志链是不会中断。

 

 四. 还原(2)打断日志链

  在前面讲述事务日志时提到了, 事务日志链LSN, 在还原的时候必须要保持事务链的顺序,依次的还原。 下面演示跳过日志链文件ID:11 ,直接还原日志链文件ID:12。

-- 尾日志备份
backup log TestBulkLogged to BackupTestDevice with norecovery 

-- 从备份恢复一个全备份 ,norecovery(正在还原...)不可读写. file指备份集位置号
restore database TestBulkLogged from BackupTestDevice with file=10, norecovery 

-- 跳过日志文件11,恢复到日志文件12  
restore database TestBulkLogged from BackupTestDevice  with file=12, recovery

  澳门新萄京官方网站 17

  结论:如果只有(完整备份和事务日志备份), 在还原时,事务日志必须保持LSN顺序,依次还原,否则还原失败就会丢失数据。

为了设计合理的备份策略,首先要熟悉SQL Server 2008都支持哪些恢复模式,它支持的恢复模式有如下:

 生产系统不要使用简单恢复模式

  建议说明:简单恢复模式并不适合生产系统。因为对生产系统而言,丢失最新的更改是无法接受的,我们建议使用完整恢复模式。

  基础小知识:在简单模式下,可以采用两种备份方式:全备份和差异备份。这两种备份消耗都会比较大,所以不是可以频繁备份的类型,所以在两次备份间隔的时间段内数据都存在丢失的风险。微软官方文档 :简单恢复模式下的备份.aspx)

  实际场景小故事:很多维护人员喜欢简单模式,因为简单模式自动回收日志空间以减少空间需求,实际上不再需要管理事务日志空间。但实际情况时因为明白这其中的奥妙原理么?并不是,甚至相反,我在很多的客户系统看到跑着上TB的数据,而数据库备份模式竟然是简单模式,只有每天的全备份,连差异备份都没有。

  我一般会问:“现在的备份模式可能会丢一天的数据,公司能接受么?”  

  维护人员:“那肯定不能接受呀!”

  我又问:“那为什么不采用更好的备份方式呢?”

  维护人员:“我也不太懂,不知道该怎么做,数据库跑这么久了,没那么容易坏吧?”

  

 

五. 还原(3) 基于差异备份下的日志还原

  在生产环境中,由于日志文件备份频繁,导致日志文件太多,如果按日志文件一个一个来还原,需要大量时间和精力。下面演示直接从差异备份还原开始,看后面的日志文件是否能还原成功。

澳门新萄京官方网站 18

-- 尾日志备份
backup log TestBulkLogged to BackupTestDevice with norecovery 

-- 从备份恢复一个全备份 ,norecovery(正在还原...)不可读写. file指备份集位置号
restore database TestBulkLogged from BackupTestDevice with file=10, norecovery 

-- 恢复到差异备份文件13. 跳过日志文件11,12 
restore database TestBulkLogged from BackupTestDevice  with file=13, recovery

   上面还原是跳过了日志文件,直接使用差异备份文件还原。我们来查看下表中的数据,会发现差异备份完全可以还原正确成功。

  澳门新萄京官方网站 19

下面是差异备份与日志备份组合来还原,结论是日志文件不需要一个一个来还原,可以直接定位到,一个差异备份来还原,再还原,之后的日志文件。

-- 尾日志备份
backup log TestBulkLogged to BackupTestDevice with norecovery 

-- 从备份恢复一个全备份 ,norecovery(正在还原...)不可读写. file指备份集位置号
restore database TestBulkLogged from BackupTestDevice with file=10, norecovery 

-- 恢复到差异备份文件13. 跳过日志文件11,12 
restore database TestBulkLogged from BackupTestDevice  with file=13, norecovery

-- 恢复到日志文件14 
restore database TestBulkLogged from BackupTestDevice  with file=14, recovery

   结论:有了差异备份,在还原时就节省了很多还原时间和精力。可以在完整备份的基准内,直接选择最后一次的差异备份加上之后的日志备份来还原。

 

 使用完整恢复模式,要有日志备份计划

  建议说明:完整恢复模式使用日志备份在最大范围内防止出现故障时丢失数据,这种模式需要备份和还原事务日志(“日志备份”)。使用日志备份的优点是允许您将数据库还原到日志备份中包含的任何时点(“时点恢复”)。可以使用一系列日志备份将数据库前滚到其中一个日志备份中包含的任意时点。

  基础小知识:完整模式下,可以采用日志的频繁备份来缩小数据丢失的时间,比如:00:00点做了全备份,每10分钟一次日志备份,那么当23:50数据库损坏,只需要使用0点的全备份和损坏之前日志备份就可以还原到23:50的数据,而不是丢失整个近一天的数据。日志备份会使不活动的日志重用,这样也解决了完整模式下日志不断增长的问题。微软官方文档 :在完整恢复模式下备份.aspx)

  实际场景小故事:很多客户的系统采用了完整恢复模式,但是缺少日志备份,那么这样和简单模式有什么区别呢?有区别,没有降低数据丢失的风险反而增加了日志的空间消耗。很多时候被问到这样的问题,数据库日志很大,怎么收缩?很多数据库新手可能完全不知道日志备份的作用,而采用把恢复模式改成简单,然后收缩!再改回完整模式!比较搞笑的问题还有数据库搭建了镜像或AlwaysOn可用组(必须完整恢复模式),竟然把镜像拆掉,然后改成简单,收缩后

再重新搭建....很多时候只需要一个日志备份就可以解决的问题!

 

 

 系统数据库备份

  SQL Server 维护一组系统级数据库(称为“系统数据库”),这些数据库对于服务器实例的运行至关重要。 每次进行大量更新后,都必须备份多个系统数据库。 必须备份的系统数据库包括 msdb、 master和 model。 如果有任何数据库在服务器实例上使用了复制,则还必须备份 distribution 系统数据库。 备份这些系统数据库,就可以在发生系统故障(例如硬盘丢失)时还原和恢复 SQL Server 系统。

  而往往系统数据库得不到关注,在维护任务中是缺失的。

澳门新萄京官方网站 20

 使用压缩备份

  数据库往往比较大,那么同样备份文件占用的空间也很大,由于常常要保留几天甚至一周的数据在本地磁盘,压缩备份可以极大的减少备份文件对磁盘空间的占用。同时因为文件小了,备份产生IO的压力也会降低,但会对消耗比较多的CPU。

  澳门新萄京官方网站 21

 

翻译后如下:

 使用校验和(CHECKSUM)

  此选项主要是在备份的时候校验是否存在残缺页(也可以理解成是否有数据页损坏),开启此选项可以在备份时及时发现数据是否存在问题。

  澳门新萄京官方网站 22

 

  详细说明请参见:数据库备份checksum选项你会用么?

 

 验证备份可用性

  验证备份但不还原备份,检查备份集是否完整以及整个备份是否可读。 但是,RESTORE VERIFYONLY 不尝试验证备份卷中的数据结构。 在Microsoft SQL Server 中,RESTORE VERIFYONLY 得到了增强以对数据进行附加检查,从而提高检测到错误的可能性。 其目标是尽可能接近实际的还原操作。

  RESTORE VERIFYONLY 执行下列检查:

  • 备份集是否完整以及所有卷是否可读。

  • 数据库页中的一些标头字段,例如页 ID(就如同要写入数据一样)。

  • 校验和(如果介质中提供的话)。

  • 目标设备中是否有足够的空间。

 

 

 有异地备份

  防止本地磁盘损坏或者整个机房故障,对这种至关重要的数据,必须采取异地备份的办法。

澳门新萄京官方网站 23

 定期检查磁盘空间

  很多客户运维的策略不完善,同时又缺少巡检的过程,很多时候备份作业创建后没有及时维护,导致磁盘空间被占满,备份作业失败。

 

 

简单与完整模式下的备份详细描述

  简单恢复模式下的备份

  简单恢复模式是最简单的备份和还原形式。该恢复模式同时支持数据库备份和文件备份,但不支持日志备份。事务日志数据仅与关联的用户数据一起备份。缺少日志备份可简化备份和还原的管理。但是,数据库只能还原到最近备份的末尾。

  下图显示了简单恢复模式下最简单的备份与还原策略。此策略仅使用包含数据库中所有数据的完整数据库备份。存在五个完整数据库备份,但只需要还原最近的备份(在 t5 时点执行的备份)。还原此备份会将数据库恢复到 t5 时点。由 t6 框表示的所有后续更新都将丢失。

澳门新萄京官方网站 24

 

 

  最大程度地降低工作丢失的风险

 

  在简单恢复模式下,在执行下次完整备份或差异备份前,所做工作丢失的风险会随时间的推移而增加。与完整备份不同的是,差异备份仅包括自上次完整备份以来所做的更改。因此,我们建议您在不影响备份管理的前提下时常备份,以免丢失大量数据。

下图显示了仅使用数据库备份的备份计划的工作丢失风险。此策略仅适用于可经常备份的小型数据库。

澳门新萄京官方网站 25

下图显示的备份策略通过使用差异数据库备份对数据库备份进行补充,从而减少了工作丢失风险。在第一个数据库备份完成后,会接着进行三个差异数据库备份。第三个差异备份足够大,因而下一个备份为完整数据库备份。该数据库备份将成为新的差异基准。

澳门新萄京官方网站 26

 

 

  在完整恢复模式下备份

  完整恢复模式使用日志备份在最大范围内防止出现故障时丢失数据,这种模式需要备份和还原事务日志(“日志备份”)。使用日志备份的优点是允许您将数据库还原到日志备份中包含的任何时点(“时点恢复”)。可以使用一系列日志备份将数据库前滚到其中一个日志备份中包含的任意时点。请注意,为了最大程度地缩短还原时间,可以对相同数据进行一系列差异备份以补充每个完整备份。

假定可以在发生严重故障后备份活动日志,则可将数据库一直还原到没有发生数据丢失的故障点处。使用日志备份的缺点是它们需要使用存储空间并会增加还原时间和复杂性。

 

  下图显示了在完整恢复模式下的最简单的备份策略。在此图中,已完成了完整数据库备份 Db_1 以及两个例行日志备份 Log_1 和 Log_2。在 Log_2 日志备份后的某个时间,数据库出现数据丢失。在还原这三个备份前,数据库管理员必须备份活动日志(日志尾部)。然后还原 Db_1、Log_1 和 Log_2,而不恢复数据库。接着数据库管理员还原并恢复结尾日志备份 (Tail)。这将把数据库恢复到故障点,从而恢复所有数据。

澳门新萄京官方网站 27

 

  最大程度地降低工作丢失的风险

 

 在第一个完整数据库备份完成并且常规日志备份开始之后,潜在的工作丢失风险的存在时间仅为数据库损坏时以及执行最新的常规日志备份时。因此,建议经常执行日志备份,以将工作丢失的风险限定在业务要求所允许的范围内。

下图显示的备份策略使用差异数据库备份来补充完整数据库备份和日志备份。事务日志备份可缩短潜在的工作丢失风险的存在时间,使该风险仅在最新日志备份 t14 之后存在。进行一系列差异备份(三次备份)来减少在出现故障时需要还原的事务日志数。第三个差异备份很大,足以使下一个备份成为完整数据库备份。该数据库备份将成为新的差异基准。

澳门新萄京官方网站 28

在此图中的第一个数据库备份创建之前,数据库存在潜在的工作丢失风险(从时间 t0 到时间 t1)。该备份建立之后,例行日志备份将工作丢失的风险降为丢失自最近日志备份之后所做的更改(在此图中,最近备份的时间为 t14)。如果在最新备份后出现故障,数据库管理员将尝试备份日志尾部(尚未备份的日志)。如果结尾日志备份成功,则数据库管理员可以通过将数据库还原到故障点来避免任何工作丢失。

简单恢复模式:

更多建议

  1. 定期进行数据备份(完备或差异备份)和日志备份。
  2. 使用压缩备份来减少磁盘空间占用和提高备份效率。
  3. 定期检查磁盘剩余空间和备份文件增长情况,以确保有足够空间进行下一次备份。
  4. 使用校验和(CHECKSUM)来检查数据完整性。
  5. 使用RESTORE VERIFYONLY来验证备份可用性。
  6. 根据数据变动情况决定完整备份和差异备份的频率。
  7. 根据日志生成速度来决定日志备份的频率。
  8. 优先使用脚本来备份数据库。
  9. 如果使用维护计划备份,请确认是否需要生成“报告和记录”。
  10. 定期检查日志文件大小和VLF数量。
  11. 定期清理msdb数据库中备份和还原记录。
  12. 在磁盘空间充足条件下,应在本地保留一份最新备份(最后一次完备及之后备份文件)。
  13. 定期复制数据库备份至其他服务器,并定期检查异地备份。
  14. 在备用服务器上还原数据库以测试备份可用性,并运行DBCC CHECKDB来检查数据完整性。
  15. 定期归档历史数据,条件允许情况下,应将历史数据归档到专门存放历史记录的数据库。
  16. 除有特殊需求修改数据库恢复模式外,应保证数据库运行在完整恢复模式下。
  17. 当数据库从简单恢复模式切换到完整恢复模式下,应立即完整备份或差异备份来修复断裂的日志链。
  18. 当数据库从大日志恢复模式切换到完整恢复模式下,应立即日志备份,以保证此后可按照时间点还原。
  19. 在做任何可能存在风险的操作前,请确保先确保备份有效。
  20. 维护一个列表,记录数据库进行备份的频率、路径以及异地备份的路径等信息,以便故障时能第一时间找到备份。
  21. 关于备份的误区:SQL Server误区30日谈-Day30-有关备份的30个误区

 

--------------博客地址---------------------------------------------------------------------------------------

原文地址: 

如有转载请保留原文地址! 

 


 

  总结 :备份真的很重要!文章讲述的东西很少,只想起到一个引起重视的目的!

  备份的更多更详细的文章,请参见:[微软官方文档,备份概述]()

 ----------------------------------------------------------------------------------------------------

注:此文章为原创,欢迎转载,请在文章页面明显位置给出此文链接!
若您觉得这篇文章还不错请点击下右下角的推荐,非常感谢!

在简单恢复模式下,只支持完整备份和差异备份,不支持事务日志备份。在简单恢复模式下还原数据库时只能还原到上一次数据库备份的数据,而上一次数据库备份以后的数据将无法进行还原,在发生灾难时,这些上一次数据库备份以后的数据必须重做。所以简单恢复模式并不适用于生产系统。另外在简单恢复模式下,由于事务日志会被截断,所以日志文件不会一直膨胀,非常小。

完整恢复模式:

完整恢复模式是微软建议在生产环境中使用的恢复模式。在正常情况下(即能备份日志尾部)发生灾难进行还原数据库时,不会丢失任务数据。但是如果日志尾部损坏,则必须重做自上一次日志备份或差异备份等之后所做的更改。在完整恢复模式下,所有的操作都会在日志中完整地记录下来。

大容量日志恢复模式:

大容量日志恢复模式简单地记录了大多数大容量操作日志(如Bulk INSERT,CREATE INDEX,SELECT INTO等),而不是记录全部大容量操作日志,所以这些大容量操作比在完整恢复模式下执行要快很多,同时大容量日志恢复模式完整记录了其他事务日志。所以大容量日志恢复模式是一种特殊用途的恢复模式,只应用于提高某些大规模大容量操作(如大量数据的大容量导入)的性能。完整恢复模式下有关备份的许多说明也适用于大容量日志恢复模式。

如果在最新日志备份后发生日志损坏或执行大容量日志记录操作,则必须重做自该上次备份之后所做的更改,否则不丢失任何数据。

另外设计合理的备份策略,还要熟悉SQL Server 2008都支持哪些备份类型,它支持的备份类型有如下:

 

澳门新萄京官方网站 29

 

 

翻译后如下:

 

澳门新萄京官方网站 30

 

 

完整数据库备份:

完整备份会备份数据库中的所有数据,以及可以恢复这些数据的足够的日志。它为差异、事务日志备份创建基准备份。在数据库底层上,完整备份实际上是把所有页(page)复制到备份设备上。

差异数据库备份:

差异备份仅备份自上次完整备份后发生更改的数据。通常,建立基准备份之后执行的差异备份比基准备份更小,创建速度也更快。因此,使用差异备份可以加快进行频繁备份的速度,从而降低数据丢失的风险。通常,一个差异基准会由若干个相继的差异备份使用。还原时,首先还原完整备份,然后再还原最后一个的差异备份。
业务数据库运行一段时间后,随着数据库的更新,包含在差异备份中的数据量会增加。这使得创建和还原差异备份的速度变慢。因此,必须重新创建一个完整备份,为另一个系列的差异备份提供新的差异基准。

同样,差异备份和完整备份类似,也会备份恢复数据的足够日志,这是由数据库系统控制的。

在数据库底层上,差异备份是备份自上次完整备份以后所有修改的区(extent)。

部分备份:

部分备份与完整数据库备份类似,但是部分备份不包含所有文件组。部分备份包含主文件组、每个读写文件组以及任何指定(可选)的只读文件中的所有数据。部分备份在希望不包括只读文件组时非常有用。只读数据库的部分备份仅包含主文件组。

部分备份功能从SQL Server 2005开始引入。

创建部分备份时,必须在BACKUP 语句中指定 READ_WRITE_FILEGROUPS 选项。也可以指定任何只读文件或文件组,以便将其包括在部分备份中。

事务日志备份:

在完整恢复模式或大容量日志恢复模式下,需要定期进行事务日志备份。每个日志备份都包括创建备份时处于活动状态的部分事务日志,以及先前日志备份中未备份的所有日志记录。在创建第一个事务日志备份之前,必须先创建完整备份(如完整数据库备份或一组文件备份中的第一个完整备份)。此后,必须定期备份事务日志。这不仅能最小化工作丢失风险,还有助于事务日志的截断。通常,事务日志在每次常规日志备份之后截断。

连续的日志备份序列称为“日志链”。日志链从数据库的完整备份开始。通常,仅当第一次完整备份数据库时或者将恢复模式从简单恢复模式切换到完整恢复模式或大容量日志恢复模式之后,才会开始一个新的日志链。在完整恢复模式下(或者在大容量日志恢复模式下的某些时候),连续不断的日志链可以将数据库还原到任意时间点。

若要将数据库还原到故障点,必须保证日志链是完整的。也就是说,事务日志备份的连续序列必须能够延续到故障点。此日志序列的开始位置取决于所还原的数据备份类型:数据库备份(包括完整或差异备份)、部分备份或文件备份。对于数据库备份或部分备份,日志备份序列必须从数据库备份或部分备份的结尾处开始延续。对于一组文件备份,日志备份序列必须从整组文件备份的开头开始延续。

如果日志备份丢失或损坏,则可通过创建完整数据库备份或差异数据库备份并随后备份事务日志来开始一个新的日志链。如果要将数据库还原到事务日志备份内的某个时点,则建议保留丢失的日志备份之前的事务日志备份。

尾日志备份:

在完整恢复模式或大容量日志恢复模式下数据库发生灾难时,SQL Server 2005或2008可以备份日志结尾以捕获尚未备份的活动日志记录,把还原数据库操作之前对日志尾部执行的日志备份称为尾日志备份。

所以这里面有一点特别重要,在完整恢复模式或大容量日志恢复模式下一旦数据库发生灾难,还原数据库时,进行的第一步操作是尾日志备份(如果尾日志能备份的话),这样才不会丢失自上一次日志备份(也可能是完整或差异备份,主要是看用什么备份策略)后的数据。如果日志文件受损且无法创建结尾日志备份,则必须在不使用结尾日志备份的情况下还原数据库。最新日志备份(也可能是完整或差异备份,主要是看用什么备份策略)后提交的任何事务都将丢失。

文件和文件组备份:

针对大型数据库和性能要求使完整数据库备份显得不切实际时,则可以创建文件备份。文件备份包含一个或多个文件(或文件组)中的所有数据。文件备份包括完整文件备份和差异文件备份。针对大型数据库可以分别备份和还原数据库中的文件,而且可以仅还原已损坏的文件,而不必还原数据库的其他部分。

差异文件备份为创建当前文件备份提供了一种快速并且节省空间的方式。在简单恢复模式下,仅为只读文件组启用了差异文件备份。在完整恢复模式下,允许对具有差异基准的任何文件组进行差异文件备份。

文件和文件组备份增加了备份和还原的复杂度。

Copy_only备份:

仅复制备份可以在不打断正常备份序列的情况下复制数据库的内容,这个功能从SQL Server 2005开始引入。事务日志从不在仅复制备份后出现截断,这对平时DEV或DBA仅想获得一份完整的数据库用于测试工作,而又不影响当前的备份序列非常方便。

 

   设计一个数据库的最佳备份策略,会面临如何选择使用哪种恢复模式的问题,因为恢复模式控制着备份和还原的行为。一般来讲,简单恢复模式一般适合用于测试或开发数据库。对于生产数据库,最佳选择通常是完整恢复模式,还可以选择大容量日志恢复模式作为补充。但简单恢复模式有时也适合小型生产数据库(尤其是当其大部分或完全为只读时)或数据仓库使用。

若要为特定数据库确定最佳恢复模式,应考虑数据库的恢复目标和要求,数据使用方式,员工因素以及是否可对日志备份进行管理等。

恢复目标要求 :

l  不丢失任何更改的重要程度如何?

l  重新创建丢失的数据的难易程度如何?

l  是否有两个或两个以上的数据库在逻辑上必须保持一致?

员工因素 :

是否雇用系统或数据库管理员?如果没有,那么由谁负责执行备份和恢复操作,如何对他们进行培训?

数据使用方式,针对当前数据库考虑下列问题:

l  数据库中的数据多长时间更改一次?

l  是否有些表明显比其他表修改频繁?

l  是否有关键生产周期?如果有,那么在这些周期中的使用方式是怎样的?数据库是否会经历插入操作和其他更新操作的高峰期?
可能需要计划在非高峰期进行数据备份。当大量使用 I/O 系统时,通常只需使用日志备份。

l  数据库是否会遇到可能无法立即检测到的危险更新或应用程序错误?
如果数据库会遇到这些情况,请考虑使用完整恢复模式。这可以使用日志备份将数据库恢复到特定时间点。

何时使用简单恢复模式?

如果符合下列所有要求,则使用简单恢复模式:

l  不需要故障点恢复。如果数据库丢失或损坏,则会丢失自上一次备份到故障发生之间的所有更新,但你愿意接受这个损失。

l  愿意承担丢失日志中某些数据的风险。

l  不希望备份和还原事务日志,希望只依靠完整备份和差异备份。

 

何时使用完整恢复模式?

如果符合下列任一要求,则使用完整恢复模式(还可以选择使用大容量日志恢复模式):

l  必须能够恢复所有数据。

l  必须能够恢复到故障点。

l  希望可以还原单个页。

l  愿意承担事务日志备份的管理开销。

l  数据库包含多个文件组,并且希望逐段还原读/写辅助文件组(以及可选地还原只读文件组)。

 

何时使用大容量日志恢复模式?

大容量日志恢复模式作为完整恢复模式的附加补充。建议仅在运行大规模大容量操作期间以及在不需要数据库的时间点恢复时使用该模式。

*l  数据库是否会发生周期性的数据库大容量操作?

在该恢复模式下,多数大容量操作仅进行最小日志记录。如果使用完整恢复模式,则可以在执行此类大容量操作前临时切换到大容量日志恢复模式。通常,大容量日志恢复模式与完整恢复模式相似,只是它按最小方式记录多数大容量操作。大容量日志恢复模式仅适合在能够以最小方式记录操作的大容量操作期间使用。建议在其余时间使用完整恢复模式。当完成一组大容量操作后,建议立即切换回完整恢复模式。*

 

下面就以简单恢复模式和完整恢复模式来设计几个备份策略。

一,简单恢复模式下的备份策略设计:

1, 仅完整数据库备份策略

这种策略仅适用于经常备份的小型数据库,数据丢失风险比较大

此策略仅使用包含数据库中所有数据的完整数据库备份。例如下图完成5个完整数据库备份后发生灾难,只需要还原最近的备份(在 t5 时点执行的备份)。还原此备份会将数据库恢复到 t5 时点。由t6 框表示的所有后续更新都将丢失。

 

澳门新萄京官方网站 31

 

在这种策略下为了最大程度降低数据丢失的风险,可以增加备份次数和缩短备份间隔,如下图:

 

 

 

澳门新萄京官方网站 32

2,完整数据库备份 差异数据库备份策略

这种策略比只使用仅完整数据库备份策略,减少了数据丢失风险。例如下图在第一个完整数据库备份完成后,会接着进行三个差异数据库备份。随着时间推移,第三个差异备份已经足够大,因而下一个备份重新使用完整数据库备份。该数据库备份将成为新的差异基准。

 

澳门新萄京官方网站 33

在这种备份策略下,如果在t4时进行“差异数据库备份3”完成后而t5时的“完整数据库备份2”还没进行的情况下发生灾难,只需要先还原t1时的“完整数据库备份1”,接着还原最后一次即t4时的“差异数据库备份3”就可以恢复数据库,但是t4以后的数据会丢失。

二,完整恢复模式下的备份策略设计:

1, 完整数据库备份 日志备份策略

例如下图已完成了完整数据库备份 Db_1 以及两个日志备份 Log_1 和 Log_2。在 Log_2 日志备份后的某个时间,数据库出现数据丢失或灾难。在还原这三个备份前,必须备份活动日志(日志尾部,如果能备份的话)。然后依次还原 Db_1、Log_1 和 Log_2,而不恢复数据库(还原时必须使用norecovery选项),接着还原并恢复结尾日志备份 (还原时必须使用recovery选项)。这将把数据库恢复到故障点,从而恢复所有数据。

 

 

澳门新萄京官方网站 34

2, 完整数据库备份 差异数据库备份策略

例如下图在第一个数据库备份完成后,会接着进行三个差异数据库备份。随着时间推移,第三个差异备份已经足够大,因而下一个备份重新使用完整数据库备份。该数据库备份将成为新的差异基准。

 

澳门新萄京官方网站 35

 

在这种备份策略下,如果在t10时进行“差异数据库备份3”完成后而t13时的“完整数据库备份2”还没进行的情况下发生灾难,还原时,必须先备份活动日志(日志尾部,如果能备份的话)。然后依次还原t10时的“完整数据库备份1”,最后一次即t4时的“差异数据库备份3”(还原时必须使用norecovery选项)接着还原并恢复结尾日志备份 (还原时必须使用recovery选项)。这将把数据库恢复到故障点,从而恢复所有数据。

3, 完整数据库备份 差异数据库备份 日志备份策略

这种备份策略可以最大程度地降低数据丢失的风险,也是比较推荐的备份策略!

例如下图从t1到t12时间段内,进行了一次完整数据库备份,若干日志备份,三个差异数据库备份。

 

 

澳门新萄京官方网站 36

在这种备份策略下,当t12时的日志备份完成后数据丢失或发生灾难,如何还原数据库呢?步骤如下:

第一步:备份活动日志(日志尾部,如果能备份的话)

第二步:还原t1时的“完整数据库备份1”

第三步:还原t10时的“差异数据库备份3”

第四步:还原t11时的日志备份

第五步:还原t12时的日志备份

第六步:还原第一步的“尾日志备份”

其中第二,三,四,五步还原时必须用norecovery选项,第六步用recovery选项。

根据业务系统级别的不同,一般可以一周进行一次完整数据库备份,一天进行一次差异数据库备份,30分钟或1小时进行一次日志备份。

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:澳门新萄京官方网站:数据库备份这点事儿,备

关键词: