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

澳门新萄京官方网站Server恢复模式与事务日志备

2019-06-08 作者:数据库网络   |   浏览(114)

一. 概述

  文件备份是指备份一个或多个文件或文件组中的所有数据。使用文件备份能够只还原损坏的文件,而不用还原数据库的其余部份,从而加快恢复速度。例如,如果数据库由位于不同磁盘上的若干文件组成,在其中一个磁盘发生故障时,只需还原这个故障磁盘上的文件,其它磁盘文件无须还原,这样缩短还原时间。

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

  1.1 文件备份具有如下优点:
    (1)可以迅速还原损坏的文件。
    (2)当超大型数据库在完整备份下变得难以管理时,文件备份增加了计划和媒体处理的灵活性。

  1.2 文件备份具有不足:
    (1) 与完整数据库备份相比,文件备份的主要缺点是管理较为复杂。如果某个损坏的文件未备份,那么媒体故障可能会导致无法恢复整个数据库。因此必须维护一组完整的文件备份,还必须维护一个或多个日志备份。
    (2) 维护和跟踪这些完整备份是一种耗时的任务,所需空间会超过完整数据库备份所需的空间。

写在前面

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

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

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

写在前面

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

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

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

原始出处:

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

二. 文件备份策略  

   使用文件备份和日志备份还原数据库的操作可能比较复杂,因此最好先执行完整数据库备份,并在第一个文件备份开始之前,进行日志备份。下图在t0创建数据库后,立即执行完整数据库备份t1,创建第一个完整数据库备份后,便可以开始执行事务日志备份。事务日志备份按计划的间隔时间执行,文件备份以最适合数据库业务要求的间隔执行,下面是先备份主文件组A,再是辅助文件组B。在完整恢复模式下,恢复一个文件组备份,不但需要恢复文件组备份本身,还需要依次恢复从上一次完整数据库备份后到恢复的目标时间点为止的所有日志备份。如果日志备份数量多,可以考虑再给合差异文件备份,但这样备份计划更加难于管理。

澳门新萄京官方网站 1

 

一些名词

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

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

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

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

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

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

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

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

  尾日志备份:“结尾日志备份”捕获尚未备份的任何日志记录(“结尾日志”),以防丢失所做的工作并确保日志链完好无损。 在将 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) 和完整数据库还原(完整恢复模式)
如果在最新日志备份后发生日志损坏或执行大容量日志记录操作,则必须重做自该上次备份之后所做的更改。

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

一些名词

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

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

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

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

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

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

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

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

  尾日志备份:“结尾日志备份”捕获尚未备份的任何日志记录(“结尾日志”),以防丢失所做的工作并确保日志链完好无损。 在将 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) 和完整数据库还原(完整恢复模式)
如果在最新日志备份后发生日志损坏或执行大容量日志记录操作,则必须重做自该上次备份之后所做的更改。

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

 

澳门新萄京官方网站 2

三.文件还原  

   当一个大数据库有若干个文件和文件组,如果损坏只是集中在其中一个文件或文件组上,sqlserver只要把坏掉的那个数据文件组重建,肯定可以节约时间。但是数据库的事务修改是会分布在各个数据文件上的,如果用备份只恢复其中一个文件,而其它文件不恢复,那么它们的状态一定会不一致,这样数据库是无法使用的,为了使新恢复的文件能够自动恢复备份以后做的修改,就需要借助事务日志。使用文件备份还原一个或多个受损文件的步骤如下:

  (1) 创建活动事务日志的尾日志备份。 对于离线文件还原,在文件还原之前必须始终先进行一次尾日志备份。对于在线文件还原,在文件还原之后必须始终先进行一次日志备份。因为日志文件一日损坏,文件还原则无法进行。

  (2) 从每个损坏的文件的最新文件备份还原相应文件。

澳门新萄京官方网站Server恢复模式与事务日志备份,备份与恢复系列六。  (3) 针对每个还原的文件,还原最近的差异文件备份(如果有,因为这样还原快)

  (4) 按顺序还原事务日志备份,从时间上最早备份的日志文件开始,到步骤1的尾日志结束。

常规建议

常规建议

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

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

四 . 数据初始化  

--第一步: 创建数据库
CREATE DATABASE [FileGroupTest]
go
USE [FileGroupTest]

--第二步:创建文件组
ALTER DATABASE [FileGroupTest] ADD FILEGROUP [FG_Test_Id_01]
ALTER DATABASE [FileGroupTest] ADD FILEGROUP [FG_Test_Id_02]

--第三步:创建文件添加到文件组
ALTER DATABASE [FileGroupTest] ADD FILE
(NAME = N'FG_TestUnique_Id_01_data',FILENAME = N'D:DataFG_TestUnique_Id_01_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_Test_Id_01]

ALTER DATABASE [FileGroupTest] ADD FILE
(NAME = N'FG_TestUnique_Id_02_data',FILENAME = N'D:DataFG_TestUnique_Id_02_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_Test_Id_02]

--第四步创建表存放在不同文件上
CREATE TABLE  Student(ID INT,Name varchar(50),[Address] varchar(100)) ON [FG_Test_Id_01]
CREATE TABLE  Teacher(ID INT,Name varchar(50),[Address] varchar(100)) ON [FG_Test_Id_02]
CREATE TABLE  School(ID INT,Name varchar(50),[Address] varchar(100)) ON [PRIMARY]

澳门新萄京官方网站 3

澳门新萄京官方网站 4

-- 养成好习惯先进行完整备份
backup database  [FileGroupTest] to BackupTestDevice

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

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

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

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

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

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

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

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

  

 

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

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

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

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

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

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

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

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

  

 

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

澳门新萄京官方网站 5

五. 备份演示

-- 给二个表插入数据
insert into Student values(1,'张三','广东深圳')
insert into Teacher values(1,'李四','广东佛山')

-- 日志备份
backup log  [FileGroupTest] to BackupTestDevice

-- 给二个表插入数据
insert into Student values(2,'张三2','广东深圳')
insert into Teacher values(2,'李四2','广东佛山')

-- 日志备份
backup log  [FileGroupTest] to BackupTestDevice

-- 文件组FG_Test_Id_01备份
backup database [FileGroupTest] file='FG_TestUnique_Id_01_data' to BackupTestDevice

-- 给二个表插入数据
insert into Student values(3,'张三3','广东深圳')
insert into Teacher values(3,'李四3','广东佛山')
-- 日志备份
backup log  [FileGroupTest] to BackupTestDevice
-- 给二个表插入数据
insert into Student values(4,'张三4','广东深圳')
insert into Teacher values(4,'李四4','广东佛山')
-- 日志备份
backup log  [FileGroupTest] to BackupTestDevice

-- 文件组FG_Test_Id_02备份
backup database [FileGroupTest] file='FG_TestUnique_Id_02_data' to BackupTestDevice

-- 给主文件组表插入数据
insert into School values(1,'深圳大学','广东深圳南山')
-- 主文件组备份
backup database [FileGroupTest] file='FileGroupTest' to BackupTestDevice

  查看备份集如下图所示:type=F 代表文件组备份类型

澳门新萄京官方网站 6

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

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

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

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

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

 

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

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

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

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

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

 

 

简单恢复模式(Simple Recovery Mode)

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

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

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

澳门新萄京官方网站 7

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

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

澳门新萄京官方网站 8

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

六. 还原演示

--步骤1:假设文件FG_TestUnique_Id_01_data已损坏,数据库处于在线状态来还原该文件
restore database [FileGroupTest] file='FG_TestUnique_Id_01_data' 
from BackupTestDevice with file=33, norecovery 

  澳门新萄京官方网站 9

--此时FileGroupTest库还能用,但FG_Test_Id_01文件组上的Student表现不能用,此时处于离线状态
select * from FileGroupTest.dbo.Student

  澳门新萄京官方网站 10

--这两个表在不同文件组上,可以使用
select * from FileGroupTest.dbo.School
select * from FileGroupTest.dbo.Teacher

    澳门新萄京官方网站 11
 BACKUP LOG 与 COPY_ONLY 选项将创建仅复制日志备份,该备份不会截断事务日志。 仅复制日志备份对日志链没有任何影响,因此其他日志备份的表现就像仅复制备份不存在一样。

--步骤2:进行新的日志备份,以确保捕获到该文件离线时的点
backup log  [FileGroupTest] to BackupTestDevice with copy_only

  澳门新萄京官方网站 12

--步骤3: 在线还原日志备份
restore log [FileGroupTest] from BackupTestDevice with file=34,norecovery
restore log [FileGroupTest] from BackupTestDevice with file=35,norecovery
restore log [FileGroupTest] from BackupTestDevice with file=38,recovery

--离线的文件组FG_Test_Id_01处于在线状态,Student表可以使用,数据库恢复完成
select * from FileGroupTest.dbo.Student

  澳门新萄京官方网站 13

 系统数据库备份

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

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

 系统数据库备份

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

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

 

完整(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向后推并截断日志。因此在一个业务量稍大的系统中,日志的增长速度将会变得很快。

澳门新萄京官方网站 14

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

澳门新萄京官方网站 15

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

 使用压缩备份

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

  澳门新萄京官方网站 16

 

 使用压缩备份

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

  澳门新萄京官方网站 17

 

澳门新萄京官方网站 18

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

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

澳门新萄京官方网站 19

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

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

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

 使用校验和(CHECKSUM)

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

  澳门新萄京官方网站 20

 

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

 使用校验和(CHECKSUM)

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

  澳门新萄京官方网站 21

 

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

翻译后如下:

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不能保持连续的日志链,执行数据恢复时会有数据丢失。在发生灾难时,这些更改必须重做。 无日志备份 最新备份之后的更改不受保护。 在发生灾难时,这些更改必须重做。

 验证备份可用性

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

  RESTORE VERIFYONLY 执行下列检查:

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

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

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

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

 

 验证备份可用性

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

  RESTORE VERIFYONLY 执行下列检查:

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

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

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

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

 

 

日志链(Log Chain)

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

澳门新萄京官方网站 22

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

澳门新萄京官方网站 23

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

澳门新萄京官方网站 24

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

 有异地备份

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

 有异地备份

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

 

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

假设有下列事件顺序。

澳门新萄京官方网站 25

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

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

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

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

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

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

 定期检查磁盘空间

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

 

 定期检查磁盘空间

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

 

澳门新萄京官方网站 26

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

  简单恢复模式下的备份

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

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

澳门新萄京官方网站 27

 

 

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

 

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

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

澳门新萄京官方网站 28

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

澳门新萄京官方网站 29

 

 

  在完整恢复模式下备份

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

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

 

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

澳门新萄京官方网站 30

 

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

 

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

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

澳门新萄京官方网站 31

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

澳门新萄京官方网站,简单与完整模式下的备份详细描述

  简单恢复模式下的备份

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

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

澳门新萄京官方网站 32

 

 

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

 

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

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

澳门新萄京官方网站 33

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

澳门新萄京官方网站 34

 

 

  在完整恢复模式下备份

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

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

 

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

澳门新萄京官方网站 35

 

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

 

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

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

澳门新萄京官方网站 36

在此图中的第一个数据库备份创建之前,数据库存在潜在的工作丢失风险(从时间 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个误区

 

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

原文地址: 

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

 


 

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

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

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

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

更多建议

  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都支持哪些备份类型,它支持的备份类型有如下:

 

澳门新萄京官方网站 37

 

 

翻译后如下:

 

澳门新萄京官方网站 38

 

 

完整数据库备份:

完整备份会备份数据库中的所有数据,以及可以恢复这些数据的足够的日志。它为差异、事务日志备份创建基准备份。在数据库底层上,完整备份实际上是把所有页(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 框表示的所有后续更新都将丢失。

 

澳门新萄京官方网站 39

 

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

 

 

 

澳门新萄京官方网站 40

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

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

 

澳门新萄京官方网站 41

在这种备份策略下,如果在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选项)。这将把数据库恢复到故障点,从而恢复所有数据。

 

 

澳门新萄京官方网站 42

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

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

 

澳门新萄京官方网站 43

 

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

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

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

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

 

 

澳门新萄京官方网站 44

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

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

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

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

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

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

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

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

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

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:澳门新萄京官方网站Server恢复模式与事务日志备

关键词: