· Primary data files:每个数据库都有一个单独的主偠数据文件默认以.mdf扩展名。主要数据文件不仅包含数据信息还包含与该数据库结构相关的信息。创建数据库时数据库结构相关信息鈈仅存在于master数据库中，同时还包含在primary data file上.

· Secondary data files一个数据库可以有一个或者多个二级数据文件默认以.ndf为扩展名。一般来说二级数据文件并不是必须的因为二级数据文件不包含文件位置等信息。

· Transaction logs: 数据库必须至少有一个事务日志文件默认以.ldf为扩展名。日志是整个数据库的命脉事务日志不可读的话，将无法对数据库进行任何操作

当你在数据库上进行数据操作时，数据并不是直接写入数据文件而是先将相关操作信息写入事务日志文件。当一个事务结束时该事务被标记为已提交，但这也并不意味着数据从日志文件写入了数据文件中一个标記为已提交的事务仅仅意味着所有与该事务相关的元素已经成功完成。The buffer cache may be updated, but not necessarily the data file.

检查点(checkpoint)会周期性的发生检查点发生时，是确认所有已提交的事务不管是在buffer cache或者事务日志中，都被写入相关的数据文件中检查点(checkpoint)可以通过以下方式来触发：

3、 做了数据库备份（在简单模式下）；

4、 数據库文件结构被改变（在简单模式下）；

5、 数据库引擎被结束。

一般来说写数据的过程是由系统自动完成的，如下图所示但数据并不昰直接写入.mdf或者.ndf文件中，而是先将有关变化写入事务日志中这也是数据库中的write-ahead机制。

3. 日志缓存会更新物理事务日志文件同时将在buffer cache上执荇相关变化

4.数据缓存（data buffer）清除所有在缓存上的脏数据，数据文件被更新

所有的数据库都可以设置为三个不同的恢复模式：简单(simple)， 完全(full)夶容量日志(Bulk-Logged).

完全恢复模式是默认的恢复模式。在完全恢复模式下需要手工的对事务日志进行管理。使用完全恢复模式的优点是可以恢复箌数据库失败或者指定的时间点上缺点则是，如果没有进行管理的话事务日志将会快速增长，消耗磁盘空间要清除事务日志，只能通过备份事务日志或者切换至简单模式。

如上图所示在完全恢复模式下，事务日志会持续增长而不管checkpoint的发生。

与完全恢复模式不同嘚是在简单恢复模式下，在检查点发生时（checkpoint）当前已被提交的事务日志将会被清除。

如上图所示在检查点发生时，所有已提交的事務日志信息将会从事务日志里面删除

因此，在简单恢复模式下容易造成数据丢失，因为无法将数据库恢复到失败的那一刻

需要注意嘚是，虽然在简单恢复模式下系统会自动定期清除日志，但这并不意味着事务日志文件不会增长例如，如果执行一个批量插入操作时SQL SERVER会将该相关操作当成一个事务，期间产生的日志量在极端情况下还是非常可观的。

大容量日志恢复模式与完全恢复模式非常相似但與完全恢复模式不同的是，批量操作将会尽量被最少记录

批量操作有以下几种类型：

在完全恢复模式下，上述操作产生的日志将会是非瑺大的而使用大容量日志恢复模式将会阻止不需要或者非预期的日志增长。

使用大容量日志恢复模式将会使数据仓库或者有大批量操莋的数据库减少很大的空间。但使用大容量恢复模式时会使得恢复变得比较困难，一般来说只能恢复到最后的事务日志备份点上，但洳果所有的事务日志都被备份后还是可以恢复成功的。

只要在必要时才使用大容量恢复模式而且使用完成后，还需切换至完全恢复模式同时进行备份。

改变数据库的恢复模式可以通过以下语句来实现：

也可以通过GUI界面来修改。

改变恢复模式并不需要重启数据库实例

在SQL Server上，有多种备份位置可以选择如本地磁盘，网络磁盘远程地址，磁带等

各种备份位置均有自己的优点和缺点。

在SQL Server上可以通过創建逻辑备份设备来完成备份。使用逻辑备份设备的好处是当变更备份地址时，不需要更改备份脚本只需要更改逻辑备份设备的定义即可。

创建逻辑备份设备的脚本如下：

上述脚本只是删除逻辑备份设备的定义下述脚本将同时删除备份文件：

使用逻辑备份设备的方法洳下：

当然，还可在逻辑备份设备上指定过期时间等备份属性如：

每一份备份包含于一个备份集，而一个备份集包含于一个存储集通過系统GUI进行备份时，SQL Server会自动指定备份集和存储集目的则是为了简化管理。用T-SQL显示指定则用如下语法：

NAMEs是指备份集名称MEDIANAME是指存储集名称。

不管恢复模式是哪一个所有的备份都必须要有一个全备份，特别是日志备份和差异备份如果没有全备份的话，将无法进行恢复

简單的全备份脚本如下所示，也可以通过维护计划来指定全备份：

但需要注意的是上述命令是将数据库备份附加到当前的存在的文件上，洳果不存在则创建它并不会覆盖原有文件。要覆盖同名的备份文件需要指定INIT参数。

在完全恢复模式或者大容量日志恢复模式下日志備份不仅仅是恢复的需要，同时也是手工管理事务日志文件的一种方式如果从不进行备份的话，在完全恢复模式或者大容量恢复模式下事务日志将会持续增长，直至消耗完所在磁盘

需要养成使用.trn为日志备份的扩展名的习惯。

每个在数据库上的动作都会被安排一个Log Sequence Number (LSN)如果需要还原到指定的时间点，需要有持续的LSN记录也就是说，在完全恢复模式或者大容量日志模式下一个不被打断的事务日志备份链是恢复数据库的基本要求。

使用日志备份来恢复时无疑是一个很慢的过程，特别是上一个全备份的历史比较悠久时使用差异备份，便能縮短恢复时间事实上，差异备份只是BACKUP DATABASE的一个选项如下：

进行数据库恢复时，先恢复数据库全备份再恢复数据库差异备份，最后才恢複日志备份

差异备份是与上一次全备份紧密相连的，不管期间有多少次日志备份和差异备份差异备份还是会从上一次全备开始备份。洇此经常会遇到这样的一种情况，在生产库上需要临时使用数据库时便用BACKUP DATABASE … TO DISK=’..’进行了一个备份，下一次的差异备份便会以这回的全備为准如果过后把这个临时全备删除掉后，后面的差异备份就没用了

差异备份并不意味着磁盘空间肯定会少，这取决于实际情况当期间大量操作发生时，差异备份还是会变得很大

在备份过程中，备份进程会同时验证数据或者校验不完整页（torn page），或者验证校验和(checksum)偠使用该功能，需要激活该选项

不完整页检测(Torn-page dection)仅仅检查每一个页看是否已经写完成。如果发现一个页只有部分被写入那么就将其标记為torn。

校验和验证(checksum validation)是一种新的页验证机制它会为每个页添加一个值来表明该页实际的大小。虽然看起来是个代价很高影响性能的操作但倳实上，它的效率非常高与torn-page差不多。

备份进程在备份数据库时会通过比较在数据库里的和随着备份页写入硬盘时这两个之间的值来进荇验证。但是这个验证并不是自动完成的，需要显示指定在GUI页面上是个选项。如果通过T-SQL来备份的话语句如下：

如果备份过程中，发現了错误SQL Server会错误信息写入MSDB上的SUSPECT_PAGE表里面。同时在默认情况下，备份行为会停止的（STOP_ON_ERROR）以便管理员排查错误。

但备份过程中的校验和验證还有另外一个选项（CONTINUE_ON_ERROR）,也就是说如果发现错误，备份过程并不会中断而是将错误页信息记录在MSDB..SUSPECT_PAGE上而已。需要注意的是SUSPECT_PAGE表是有行限淛的，最多只能达到1000行如果达到了的话，备份同样会失败

激活校验和验证的话，很明显会影响备份的性能但还是很有必要的。

完全備份和日志备份语句还支持使用密码属性如：

所指定的密码是很容易破解的。因此如果确实需要对某些备份数据进行加密的话，可以將备份存放于加密的文件系统或者其它安全的存储设备上

同时，SQL Server还提供了对真实列进行加密的功能该加密功能是工业标准。

有些情况丅单独一个硬盘无法存储一个完整的数据库备份时，可以将数据库备份分成多个部分存储在不同的磁盘上这种备份方式成为条带备份。使用条带备份的优点很明确就是能很好的利用空间，但如果某部分备份丢失或者损坏那整个备份将无效。

上述D盘和E盘上的备份是不鈳分割的

与条带备份在多个磁盘上保留同一份备份不同的是，镜像备份是在不同磁盘上保留多份备份其语句如下：

在实际情况下，对ㄖ志备份采取镜像备份方式会比较合适

在差异备份里曾提到过，差异备份是建立在上一个全备份的基础上的因此如果在一个事先安排恏的备份计划里，如果在全备份和差异备份之间再进行了一次全备份后其差异备份会被打断，如果把临时全备份删除掉后就产生了数據丢失。

在SQL SERVER 7.0以后SQL Server提供了filegroup的概念。文件组不仅仅提供了一个逻辑的存储地址还允许将不同的表和索引放在不同的文件组上来提高性能和減少备份时间。

在数据存储概述上提到了数据库有三类数据文件，一般来说数据库至少需要两个（.mdf和.ldf）或者更多的文件。SQL Server不仅允许有哆个文件存在(.ldf)还允许多个文件组存在。

一个文件组可以有多个文件每个文件需要仔细规划好初始大小及增量。

在创建对象时如果未奣确指定文件组，那么该对象将会存放在默认的文件组上在默认情况下，默认的文件组是primary但由于primary文件组不仅可以包含用户数据，同时還存储着数据库结构等技术信息因此一般建议添加额外的一个文件组，并将其指定为默认文件组

SQL Server只能有一个默认的文件组。

修改默认攵件组的语句如下：

当创建表或者索引时不管是用户还是系统都需要将表或索引放在某个文件组上。如果在创建表或索引时指定文件组那么表或索引将会存储在指定的文件组上，而不是默认的文件组

也可以通过GUI创建表的页面来指定文件组。

3、将对象迁移至指定文件组

洳果需要变更对象的存储位置最简单的方式则是通过GUI属性页面来进行修改通过该方法，可以直观的看到对象的迁移过程

同时也可以通過T-SQL来修改，例如：

备份数据文件同样可以通过BACKUP DATABASE语句来实现如下：

上述语句相当于数据文件级别的全备份，与数据库级别的备份类似文件级别上的备份也有差异备份，当然前提是要有相对应的文件全备份

与单独备份文件类似，也可以对文件组进行类似的备份操作备份攵件组的方式也有两种，一种是通过GUI界面指定一种则是通过T-SQL。

T-SQL的语句如下：

在文件组备份上不完全备份其实相当于完全备份，可以通過指定关键字READ_WRITE_FILEGROUPS来实现不完全备份

那不完全备份到底是什么意思呢？什么时候需要不完全备份如果对一个文件组设置了只读，而这只读嘚文件组又需要进行一次备份这时，可以不用BACKUP DATABASE语句进行备份只需要挑个时间停止实例，然后执行不完全备份

Restore和recovery模式备份数据是两个鈈同的概念，但在数据恢复过程中又是紧密联系的

Restore相当于从备份集中重建整个或者部分数据库，Restore是无法改变数据库状态的如脱机和联機等。

recovery模式备份数据则是将数据库从脱机状态恢复到联机状态中供用户使用recovery模式备份数据在SQL Server启动时也会发生，在数据库启动过程中SQL Server会檢查事务日志，看是否存在已提交或未提交的事务如果发现在最后一次检查点发生后，还有已提交的事务则SQL Server会对这些事务进行REDO(ROLL FORWARD)；而如果发现未提交的事务，则进行UNDO（ROLL

一旦对数据库进行了recovery模式备份数据则将无法再进行Restore操作。

recovery模式备份数据事实上是Restore的一个选项默认情况丅，进行Restore操作时SQL Server还会进行recovery模式备份数据操作。在单独对全备份进行恢复时可以不用考虑recovery模式备份数据，但如果后续仍有日志备份或差異备份需要恢复则必须注意recovery模式备份数据选项的选择。

数据库恢复的语句如下：

一般情况下如果SQL Server处于recovery模式备份数据过程中时，用户是無法使用数据库的但可以通过设置fast recovery模式备份数据使得用户在recovery模式备份数据中使用数据库。

在所有已提交的事务都被ROLL FORWARD后数据库就是联机狀态。

3、备份文件里面的信息

在primary文件里存储着与数据库结构有关的一些信息，如文件位置等因此备份后，这些信息同样保留在备份文件里面如果要恢复到不同的磁盘上或服务器后，需要进行额外的更改

不管是在简单恢复模式或者完全恢复模式下和大容量日志模式下，对全备份恢复都是差不多的最大的差别在于完全恢复模式与大容量日志恢复模式，除了需要恢复全备份外还需要指定WITH NOrecovery模式备份数据,鉯便后续的差异备份和日志备份的恢复。

当然在简单恢复模式下，也是有差异备份的这种情况下，同样需要指定WITH NOrecovery模式备份数据

需要紸意的是，在做恢复之前应该养成对当前日志进行备份的习惯，否则容易造成数据丢失SQL Server虽然提供REPLACE选项以便强制恢复，但这样子末尾日誌就丢掉了

通常，如果未对当前日志进行备份那么会收到如下错误

上述错误，提到了使用WITH REPLACE 或者WITH STOPAT命令来完成恢复但应尽量避免使用这兩个命令。

如果要将数据库恢复到不同的磁盘上可以通过GUI页面指定，也可以通过T-SQL语句来实现

T-SQL实现的方式如下：

5、恢复到指定的时间点

偠恢复到指定的时间点有三种选择，一种是通过明确指定时间一种通过指定LSN号，另外一种则是通过创建和指定log marks

通常情况下，恢复都会囿要求恢复到指定时间点的要求可通过GUI界面来实现，也可以通过T-SQL 来实现

如果知道确切的LSN号也可以通过LSN号来恢复指定的LSN。获取LSN相关信息可以通过RESOTRE HEADERONLY。这种方式只能通过T-SQL来实现

6、对镜像备份或条带备份的恢复

对镜像备份而言，每一份备份都是一样的因此恢复任何一份备份都可以完成恢复。

对条带备份而言则需要同时指定所有的条带备份，这种备份可以比单独一个备份来得快

在SQL Server2005以后，SQL Server提供了对数据页恢复的功能对数据页恢复可以在联机或者脱机状态下进行

恢复数据页相当于进行完全恢复，不同的是需要指定具体的页面

8、对系统数據库的恢复

Master数据库的恢复与其他数据库的恢复是不同的。要恢复MASTER数据库需要从将SQL Server切换至单用户模式，如果无法切换则停止SQL Server服务，然后鼡sqlserver –m命令行启动

当然，也可以用net start “服务器名” 来启动SQL Server服务启动后，再用SQLCMD命令进行还原

其恢复过程与普通数据库恢复过程是一样的并苴由于是在简单模式下，因而其恢复过程更加简单

Model数据库用来存放创建数据库时需要的信息，如果有使用MODEL数据库的话也需要对其进行備份和恢复。

Tempdb是不需要备份和恢复的在每次的启动过程中，SQL Server会自动清除tempdb并重新启动tempdb。在tempdb上需要注意的是其空间规划因为某些情况下tempdb會变得非常大，耗尽空间最终导致SQL Server关掉。

若要修改tempdb的存储路径请使用如下语句：

Resource是SQL Server2005以后新引进的一个数据库，将以前存放于master等其他系統数据库的部分信息存放于Resource数据库里

对Resource不能通过T-SQL或者GUI备份，因为看不到它要对其进行备份，只能通过手工直接拷贝其物理文件

6、数據文件备份的恢复

在第三部份里面提到了文件的备份，这种单个或多个文件的备份其恢复方式与数据库恢复类似。

但要养成一个良好的習惯在恢复前，备份当前的日志文件

接着对需要还原的文件进行还原，如 

如果在数据文件上还有差异备份日志恢复前进行差异备份恢复，如：

与数据文件恢复原理是一致的只不过是将具体文件文件组。

对SQL Server来讲从2005开始，提供了很多智能化的备份方式如可以通过制萣维护计划来进行备份，并自动产生备份作业通过结合SQL Mail就能够方便DBA建立一个良好的备份计划。但备份是与恢复结合在一起的备份的目嘚是为了减少数据丢失，而要求数据零丢失又需要间隔更短的备份周期，进而影响性能因此，一个良好的备份计划应该要是多种因素嘚折中

一个良好的备份/恢复计划，应该要做好如下几点：

1、 文档化的数据保护需求；

2、 文档化的日/周/月的备份计划；

3、 文档化的恢复过程；

4、 文档化的测试及验证结果

可见，关键在于文档化养成良好的文档功能是很必要的。

Server崩溃时DBA还可以通过事务日志将數据恢复到指定的时间点。当SQL Server运转良好时多了解一些事务日志的原理和概念显得并不是那么重要。但是一旦SQL SERVER发生崩溃时，了解事务日誌的原理和概念对于快速做出正确的决策来恢复数据显得尤为重要.本系列文章将会从事务日志的概念原理，SQL Server如何使用日志来确保持久性屬性等方面来谈SQL

事务日志的物理组织构架

事务日志仅仅是记录与其对应数据库上的事务行为和对数据库修改的日志文件.在你新建数据库时伴随着数据库文件，会有一个默认以ldf为扩展名的事务日志文件. 当然一个数据库也可以配有多个日志文件，但是在逻辑上他们可以看荿一个.

在SQL Server对于日志文件的管理，是将逻辑上一个ldf文件划分成多个逻辑上的虚拟日志文件(virtual log files,简称VLFs).以便于管理用个类比方法来看，日志文件(ldf)好仳一趟火车每一节车厢都是一个虚拟日志文件(VLFs):

那为什么SQL Server要把日志文件划分出多个VLFS呢？因为SQL Server通过这种方式使得存储引擎管理事务日志更加囿效.并且对于日志空间的重复利用也会更加高效使用VLF作为收缩数据库的最小单位比使用ldf文件作为最小单位无疑是更加高效的.

VLFS的个数和大尛无法通过配置进行设定,而是由SQL Server进行管理.当Create或Alter数据库时,SQL Server通过ldf文件的大小来决定VLFS的大小和数量。在日志文件增长时SQL Server也会重新规划VLFS的数量.

注意：根据这个原理不难看书，如果设置日志文件的增量过小则会产生过多的VLFS,也就是日志文件碎片，过多的日志文件碎片会拖累SQL Server性能.

SQL Server创建數据库时根据日志文件(ldf)的大小，生成VLF的数量公式如下:

下面我们来看一个例子:

创建数据库指定日志大小为65M

通过DBCC，我们可以看到对应的囿8个VLFs:

再次创建数据库，指定日志初始大小为28M:

可以看到对应的，VLF的数量变为4:

而对于日志文件的增长SQL Server使用了和创建数据库时相同的公式，吔就是每次增长比如为2M则按照公式每次增长4个VLFs.

我们创建一个TestGrow数据库，指定日志文件为2M此时有4个VLFS:

当我们增长2M时，这个2M则是按照公式再佽分配4个VLFs:

此时，这时能看到的VLFs数量应该为4+4=8个:

由此可以看出指定合适的日志文件初始大小和增长，是减少日志碎片最关键的部分.

事务日志嘚逻辑组织构架

当针对数据库对象所做的任何修改保存到数据库之前相应的日志首先会被记录到日志文件。这个记录会被按照先后顺序記录到日志文件的逻辑末尾并分配一个全局唯一的日志序列号(log sequence number,简称LSN）,这个序列号完全是按照顺序来的，如果日志中两个序列号LSN2>LSN1,则说明LSN2所茬LSN1之后发生的.

由此可以看出将日志文件分为多个文件除了磁盘空间的考虑之外。完全不会像数据那样可以并行访问所以将日志文件分為多个完全不会有性能上的提升.

LSN号可以看作是将日志文件和其记录数据之间的纽带.每一条日志不仅有LSN号，还有其对应事务的事务日志:

一个簡单的图片示例如下:

许多类型的操作都记录在事务日志中这些操作包括：

• 每个事务的开始和结束。

• 每次数据修改（插入、更新或删除）这包括系统存储过程或数据定义语言 (DDL) 语句对包括系统表在内的任何表所做的更改。

• 每次分配或释放区和页

对于LSN如何在ROLLBACK或者是ROLL FORWARD中以及在備份恢复过程中起作用，会在后续文章中提到

本篇文章从事务日志的逻辑和物理构架简单介绍了事务日志的构成.这是理解SQL Server如何利用日志保證持久性和数据备份恢复的基础下一篇文章将会介绍SQL Server在操作中会如何使用到日志文件

logging技术来保证了事务日志的原子性和持久性.而这项技術不仅仅保证了ACID中的原子性(A)和持久性(D),还大大减少了IO操作，把对数据的修改提交到磁盘的工作交给lazy-writer和checkpoint.本文主要讲述了SQL Server修改数据时的过程以及楿关的技术

WAL的核心思想是:在数据写入到数据库之前，先写入到日志.

因为对于数据的每笔修改都记录在日志中所以将对于数据的修改实時写入到磁盘并没有太大意义，即使当SQL Server发生意外崩溃时在恢复(recovery模式备份数据)过程中那些不该写入已经写入到磁盘的数据会被回滚(RollBack),而那些應该写入磁盘却没有写入的数据会被重做(Redo)。从而保证了持久性(Durability)

但WAL不仅仅是保证了原子性和持久性还会提高性能.

硬盘是通过旋转来读取数據,通过WAL技术，每次提交的修改数据的事务并不会马上反映到数据库中而是先记录到日志.在随后的CheckPoint和lazy Writer中一并提交,如果没有WAL技术则需要每次提交数据时写入数据库:

而使用WAL合并写入，会大大减少磁盘IO：

也许你会有疑问那每次对于修改的数据还是会写入日志文件.同样消耗磁盘IO。仩篇文章讲过每一笔写入日志的记录都是按照先后顺序，给定顺序编号的LSN进行写入的日志只会写入到日志文件的逻辑末端。而不像数據那样可能会写到磁盘的各个地方.所以，写入日志的开销会比写入数据的开销小很多

SQL Server对于数据的修改,会分为以下几个步骤顺序执行:

2.在SQL Server嘚缓冲区的日志页写入要修改的信息

3.在SQL Server的缓冲区将要修改的数据写入数据页

5.将缓冲区的日志写入日志文件

可以看到,事务日志并不是一步步寫入磁盘.而是首先写入缓冲区后，一次性写入日志到磁盘.这样既能在日志写入磁盘这块减少IO还能保证日志LSN的顺序.

上面的步骤可以看出，即使事务已经到了Commit阶段也仅仅只是把缓冲区的日志页写入日志，并没有把数据写入数据库.那将要修改的数据页写入数据库是在何时发生嘚呢?

上面提到SQL Server修改数据的步骤中并没有包含将数据实际写入到磁盘的过程.实际上，将缓冲区内的页写入到磁盘是通过两个过程中的一个實现：

任何在缓冲区被修改的页都会被标记为“脏”页将这个脏页写入到数据磁盘就是CheckPoint或者Lazy Writer的工作.

当事务遇到Commit时，仅仅是将缓冲区的所囿日志页写入磁盘中的日志文件:

而直到Lazy Writer或CheckPoint时才真正将缓冲区的数据页写入磁盘文件:

前面说过，日志文件中的LSN号是可以比较的如果LSN2>LSN1,则说奣LSN2的发生时间晚于LSN1的发生时间。CheckPoint或Lazy Writer通过将日志文件末尾的LSN号和缓冲区中数据文件的LSN进行对比只有缓冲区内LSN号小于日志文件末尾的LSN号的数據才会被写入到磁盘中的数据库。因此确保了WAL（在数据写入到数据库之前先写入日志)。

Lazy Writer和CheckPoint往往容易混淆因为Lazy Writer和CheckPoint都是将缓冲区内的“脏”页写入到磁盘文件当中。但这也仅仅是他们唯一的相同点了

Lazy Writer存在的目的是对缓冲区进行管理。当缓冲区达到某一临界值时Lazy Writer会将缓冲區内的脏页存入磁盘文件中，而将未修改的页释放并回收资源

Server的内存管理毫无兴趣。所以CheckPoint也就意味着在这个点之前的所有修改都已经保存到了磁盘.这里要注意的是：CheckPoint会将所有缓冲区的脏页写入磁盘不管脏页中的数据是否已经Commit。这意味着有可能已经写入磁盘的“脏页”会茬之后回滚（RollBack).不过不用担心如果数据回滚，SQL Server会将缓冲区内的页再次修改并写入磁盘。

通过CheckPoint的运作机制可以看出CheckPoint的间歇(recovery模式备份数据 Interval)長短有可能会对性能产生影响。这个CheckPoint的间歇是一个服务器级别的参数可以通过sp_config进行配置，也可以在SSMS中进行配置:

恢复间歇的默认参数是0意味着由SQL Server来管理这个回复间隔。而自己设置恢复间隔也是需要根据具体情况来进行界定更短的恢复间歇意味这更短的恢复时间和更多的磁盘IO，而更长的恢复间歇则带来更少的磁盘IO占用和更长的恢复时间.

通常情况下将“脏”页写入磁盘的工作，Lazy Writer要做的比CheckPoint会多出许多

本文簡单介绍了WAL的概念和修改对象时，日志所扮演的角色还分别介绍了CheckPoint和Lazy Writer,对于这些概念的理解是理解SQL Server DBA工作的基础。下篇文章将会讲述在简单恢复模式下日志的机制

在简单恢复模式下日志文件的作用仅仅是保证了SQL Server事务的ACID属性。并不承担具体的恢复数据的角色正如”简单”这個词的字面意思一样，数据的备份和恢复仅仅是依赖于手动备份和恢复.在开始文章之前首先要了解SQL Server提供的几种不同备份类型。

SQL Server所提供的幾种备份类型基本可以分为以下三种（文件和文件组备份以及部分备份不在本文讨论之列):

1.完整(Full)备份:直接将所备份的数据的所有区(Extent)进行复制这里值得注意的有2点:

• 完整备份并不像其名字“完整”那样备份所有部分，而是仅备份数据库本身而不备份日志(虽然仅仅备份少量日志鼡于同步）
• 完整备份在备份期间，数据库是可用的完整备份会记录开始备份时的LSN号，结束备份时的LSN号以便在备份结束时将这期间的改動应用到备份，所以完整备份后数据的时间点是备份结束的时间

2.差异(Differential)备份:只备份上次完整备份后，做修改的部分备份单位是区(Extent)。意味著某个区内即使只有一页做了变动则在差异备份里会被体现.差异备份依靠一个BitMap进行维护，一个Bit对应一个区自上次完整备份后，被修改嘚区会被置为1而BitMap中被置为1对应的区会被差异备份所备份。而到下一次完整备份后BitMap中所有的Bit都会被重置为0。

3.日志(Log)备份:仅仅备份自上次完整备份或日志备份之后的记录在简单模式下，日志备份毫无意义(SQL Server不允许在简单恢复模式下备份日志)下文会说明在简单恢复模式下，为什么日志备份没有意义

在简单恢复模式下，日志仅仅是为了保证SQL Server事务的ACID并没有恢复数据的功能.

比如，我们有一个备份计划如下:

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

正如”简单”这个詞所涵盖的意思，在简单恢复模式下日志可以完全不用管理。而备份和恢复完全依赖于我们自己的完整和差异备份.

恢复模式是一个数据庫级别的参数可以通过在SSMS里或通过SQL语句进行配置:

简单恢复模式下日志的空间使用

在本系列文章的第一篇文章提到过，日志文件会划分成哆个VLF进行管理在逻辑上记录是线性的，给每个记录一个顺序的唯一的LSN。

而在简单恢复模式下,为了保证事务的持久性那些有可能回滚嘚数据会被写入日志。这些日志需要被暂时保存在日志以确保在特定条件下事务可以顺利回滚这就涉及到了一个概念—最小恢复LSN（Minimum recovery模式備份数据 LSN（MinLSN） ）

MinLsn是在还未结束的事务记录在日志中最小的LSN号,MinLSN是下列三者之一的最小值:

• 还未结束的事务在日志的最小LSN

• 尚未传递给分发数据库嘚最早的复制事务起点的 LSN.

下图是一个日志的片段：

可以看到，最新的LSN是148147是CheckPoint,在这个CheckPoint之前事务1已经完成，而事务2还未完成所以对应的MinLSN应该昰事务2的开始，也就是142.

而从MinLSN到日志的逻辑结尾处则称为活动日志(Active Log)。

而活动日志分布在物理VLF上的关系可以用下图表示:

因此VLF的状态是源自其上所含有的LSN的状态，可以分为两大类:活动VLF和不活动VLF

而更加细分可以将VLF的状态分为以下四类:

1. 活动(Active) –在VLF 上存储的任意一条LSN是活动的时则VLF则為活动状态，即使一个200M的VLF只包含了一条LSN如上图的VLF3

而所谓的截断(truncated)只是将可恢复状态的VLF转换到可重用状态。在简单恢复模式下每一次CheckPoint会引發一次截断.而每一次CheckPoint都会将MinLSN向后推.所以当事务结束后并且过了CheckPoint点，其相关的日志将会被截断以便重复利用空间

在日志达到日志文件(ldf文件）末尾时，也就是上图的VLF8时会重新循环到VLF1开始，以便让空间进行重复利用.所以日志虽然可以从物理顺序上是从VLF1到VLF8但逻辑顺序可以是从VLF6開始到VLF2结束:

因此可以看出，简单恢复模式下日志是不保存的（当事务结束后相关的会被截断）。仅仅是用于保证事务回滚和崩溃恢复的鼡途.所以备份日志也就无从谈起更不能利用日志来恢复。

本文介绍了简单恢复模式下日志的原理并简单的引出了一些备份或者恢复数據的基础。而实际上除了在开发或测试环境下。使用简单恢复模式的场景并不多因为在现实生活中，在生产环境允许几个小时的数据丟失的场景几乎没有.下篇文章将会讲述在完整恢复模式下日志的作用

生产环境下的数据是如果可以写在资产负债表上的话，我想这个资產所占的数额一定不会小而墨菲定律（事情如果有变坏的可能，无论这种可能性有多小它总会发生）仿佛是给DBA量身定做的。在上篇文嶂介绍的简单恢复模式下从最近一次备份到当前的数据都会存在丢失的风险。而完整备份模式使得数据丢失的风险大大减少本文主要介绍在完整备份模式下概念原理和日志所处的角色。

完整恢复模式通过将对数据库的任何修改记录到日志来给予数据最大程度的保护在唍整恢复模式下，日志的作用不仅仅是保证了数据库事务的ACID并且还可以使数据恢复到在日志范围内的任何时间点。

在上一篇文章中说过在简单恢复模式下，日志几乎是不用进行管理的每一次CheckPoint都有可能截断日志，从而来回收不活动的VLF以便重复利用空间因此在简单恢复模式下，日志的空间使用几乎可以不去考虑与之相反，在完整恢复模式下日志作为恢复数据的重要组成部分，日志的管理和对日志空間使用的管理则需要重视

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

因此日志备份的目的分为以下两个:

通过从MSDN中摘自的下图可以看到:

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

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

大容量恢复模式在很多地方和完整恢复模式相同但由于在完整恢复模式下，对数据库的每一项操作都会记录在ㄖ志中而对于某些大量数据的导入导出操作，无疑会在日志中留下大量记录很多情况下，我们并不需要将这些信息记录在日志中

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

本文不会深入探讨这个模式，仅仅是对这个概念做简单解释假设我要插入一批数据，则完整恢复模式和大容量日志恢复模式在日志中所记录的信息如下:

由此可以看出在日志中，大容量恢复模式将這类操作变为一个原子所以在大容量日志恢复模式下，不能redo大容量日志中的这类操作(select into之类的）

连续的日志备份被称之为日志链表示日誌是连续的.这个概念可以用下图表示:

假设上面两个日志备份可以简单抽象成如上2个备份，则日志备份1的末尾LSN必须大于等于日志备份二的第┅个LSN(通常情况下是第一个末尾LSN等于第二个日志备份的第一个LSN,但由于存在“只备份日志”选项只备份日志并不截断日志，所以有可能重叠)则这两个备份的日志链是连续的。

下图是一个生产环境下在SSMS中查看日志链连续的例子:

可以看出，第一次完整备份后备份多次事务日誌，每一个事务日志的开始LSN都等于上一个事务日志的结束LSN因此可以从第一次完整备份开始，恢复到最后一个日志备份期间的任何时间点

完整的日志链以第一次完整备份或由简单恢复模式转为完整或大容量日志模式开始，到当前的时间点结束

而从日志恢复数据要求从最菦一次完整或差异备份到所恢复的时间点之间的日志链是连续的。

从备份恢复数据需要经历如下几步骤:

1.复制数据阶段:从完整备份和差异备份中将数据索引页和日志复制到被恢复数据库文件。

2.Redo(roll forward)阶段:将记录在日志中的事务应用到从备份中复制过来的数据使数据Roll Forward到指定的时间點.这个阶段完成后，数据库还处于不可使用阶段:

3.Undo(Roll Back)阶段:这也是传说中的recovery模式备份数据将任何未提交的事务回滚。这个阶段过后数据库处於可用状态。任何后续备份将不能接着应用到当前数据库

在连续两个日志链的日志备份，在第一个事务日志备份中定义的事务在第二個事务日志备份中Commit.如果在第一个事务日志还原后使用了recovery模式备份数据选项.也就是经历了Undo阶段。则事务1在Undo阶段会被回滚：

可见日志备份1中嘚T1被回滚，在日志备份2中的Commit也就毫无意义这也就是为什么经历过Undo阶段后不允许再恢复后续备份。因此微软推荐的最佳实践是使用Norecovery模式備份数据选项不进行Undo阶段。而在所有备份恢复后单独进行Undo阶段,这个操作可以通过还原日志尾部时指定recovery模式备份数据选项进行。

本文简单介绍了在完整恢复模式下日志的作用以及对数据恢复的一些概念。理解完整恢复模式的概念对于减少数据丢失的风险是无可替代的