数据库内核月报

数据库内核月报 － 2018 / 02

MSSQL · 最佳实践 · 数据库恢复模式与备份的关系

Author: fengyi

摘要

在SQL Server备份专题分享中，前三期我们分享了三种常见的数据库备份、备份策略的制定以及如何查找备份链。本期我们将分享数据库的三种恢复模式与备份之间的关系，SQL Server的三种数据库恢复模式包括：

简单恢复模式(Simple)

完全恢复模式(Full)

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

SQL Server三种恢复模式

简单恢复模式(Simple)

简单恢复模式下的数据库事务日志会伴随着Checkpoint或者Backup操作而被清理，最大限度的保证事务日志最小化。

工作原理

按照我个人的理解，简单恢复模式（Simple）这个名字不足以很好的描述数据库的工作原理，准确的说法应该是”Checkpoint with truncate log”。详细的解释就是：所有已经提交的事务，会伴随着数据库的Checkpoint或者Backup操作的完成而被清理掉，仅保留少许用于实例重启时Recovery所需必要少量日志。这样做的好处是，数据库的事务日志非常的少，空间占用小，节约存储开销，不需要专职的DBA去维护和备份数据库日志。但是坏处也是显而易见的，比如：

无法实现数据库的日志备份

基于简单恢复模式的数据库无法实现任意时间点恢复（point-in-time recovery）

数据最多能够恢复到上一次的备份文件（可以是完全或者差异备份），无法恢复到最近可用状态

适用场景

基于以上数据库简单恢复模式的工作原理和缺点，因此简单恢复模式的适用场景就非常清楚了，包括：

数据库存储的是非关键数据（比如：日志信息等）

数据库在任何时间，任何场景下都没有任意时间点恢复的需求

在数据库灾难发生时，可以接受部分数据库丢失

数据库中数据变化频率非常低

数据库在可以预见的时间内没有高可用（HA）需求（比如：Database Mirroring, AlwaysOn, Log Shipping等）

设置简单恢复模式

在介绍完简单恢复模式使用场景之后，让我们来看看如何将数据库修改为简单恢复模式，以下两种方法任选其一即可。 方法一，使用SSMS IDE界面操作 右键点击需要修改恢复模式的数据库名 -> Properties -> Options -> 在右侧Recovery model中选择Simple -> OK 01.png

方法二，使用语句修改 如果你觉得使用SSMS IDE修改操作繁琐，你也可以使用ALTER DATABASE语句来修改数据库的恢复模式为Simple，以下语句是将AdventureWorks2008R2数据修改为简单恢复模式。

USE [master]
GO
ALTER DATABASE [AdventureWorks2008R2] SET RECOVERY SIMPLE WITH NO_WAIT
GO

应用举例

数据库为简单恢复模式的应用举例如下图所示：

02.png

注：以上图片来自于网络：https://sqlbak.com/academy/simple-recovery-model/

10:00和22:00数据库进行了完全备份（Full Backup）

16:00数据库完成了差异备份（Differential Backup）

19:00一些重要的数据库被误删除

在这种情况之下，我们最多能够找回到16:00这个差异备份文件中的数据，而16:00 - 22:00之间的数据将会丢失而无法找回。即我们最多能够找回异常发生时间点的前一个备份文件中的数据，找回方法是，先还原10:00这个Full Backup，然后还原16:00这个Differential Backup。还原数据库的方法类似于如下语句：

USE [master]
GO

RESTORE DATABASE [AdventureWorks2008R2] 
FROM DISK = 'D:\Backup\10:00_Full.bak' WITH NORECOVERY, REPLACE

RESTORE DATABASE [AdventureWorks2008R2] 
FROM DISK = 'D:\Backup\16:00_Diff.bak' WITH RECOVERY

通过以上方法，在简单恢复模式下的数据库，无法找回数据删除时间点靠前的所有数据，最多能够找回前一个有效备份的数据。

完全恢复模式(Full)

与SQL Server数据库简单恢复模式相反的一种模式叫着：完全恢复模式（Full），以下会对数据库完全恢复的工作原理、使用场景、设置以及应用举例四个方面来了解。

工作原理

相对于简单恢复模式而言，完全恢复模式我们可以叫着“Checkpoint without truncate log”，换句话说，SQL Server数据库引擎本身不会自己主动截断事务日志，也因此，完全恢复模式下的数据库相对于简单恢复模式的数据库，事务日志文件涨的更快，大得更多。这些数据库日志文件中包含了近期所有已经提交的事务，直到事务日志备份发生且成功结束。所以，完全恢复模式下的数据库：

允许数据库日志备份

可以实现任意时间点的恢复（point-in-time recovery）

可以恢复到灾难发生时间点非常近的数据，最大限度保证数据不丢失

适用场景

基于以上对完全恢复模式的介绍，让我们来看看完全恢复模式的适用场景，包括：

数据库中存储的是非常关键的业务数据（比如：订单信息、支付信息等）

对数据安全有着非常强烈的需求，需要在任何时间，任何情况下找回最多的数据

在灾难发生时，仅能接受极少数据的丢失

对数据库的高可用（HA）要求极高（比如：Database Mirroring、Alwayson等有强需求）

对数据库有任意时间点恢复（point-in-time recovery）的能力要求

需要数据库能够实现页级别的还原能力

当然，完全恢复模式下的数据库事务日志文件增长速度和涨幅相对简单模式更大，所以，也需要DBA对数据库事务日志做定期维护，监控和备份管理。

设置完全恢复模式

将数据库设置为完全恢复模式，同样也有两种方法。 方法一、使用SSMS IDE修改数据库为完全恢复模式，同“设置简单恢复模式”中方法一。 方法二、使用ALTER DATABASE语句将数据库设置为完全恢复模式，如下语句。

USE [master]
GO
ALTER DATABASE [AdventureWorks2008R2] SET RECOVERY FULL WITH NO_WAIT
GO

应用举例

对于完全恢复模式应用举例，如下图所示：

03.png

注：以上图片来自网络 https://sqlbak.com/academy/full-recovery-model/

对于该图的场景做如下解释：

10:00和22:00：对数据库做了完全备份

16:00：对数据库做了差异备份

12:00、14:00、18:00和20:00对数据库做了事务日志备份

19:00灾难发生，数据库中一些关键数据被误删除

那么，接下来的问题就是，我们如何利用数据库的备份信息，将19:00误删除的数据找回？也就是将数据库还原到18:59:59这个时间点的状态。根据数据库的所有备份信息，我们可以按照如下思路找回被误删除的数据。 首先，我们需要还原10:00的完全备份文件，并且状态为norecovery； 其次，我们还原16:00的差异备份文件，状态也为norecovery； 然后，还原18:00的事务日志备份文件，状态依然为norecovery； 最后，还原20:00的事务日志备份文件，需要特别注意的是这里需要指定还原到的时间点（使用STOPAT关键字）为18:59:59，并且将状态设为recovery带上线。 将以上的文字描述找回数据的步骤，以代码的形式表达出来如下：

USE [master]
GO
RESTORE DATABASE [AdventureWorks2008R2] 
FROM DISK = 'D:\Backup\10:00_Full.bak.bak' WITH NORECOVERY, REPLACE

RESTORE DATABASE [AdventureWorks2008R2] 
FROM DISK = 'D:\Backup\16:00_Diff.bak' WITH NORECOVERY

RESTORE LOG [AdventureWorks2008R2] 
FROM DISK = 'D:\Backup\18:00_Log.trn' WITH NORECOVERY

RESTORE LOG [AdventureWorks2008R2] 
FROM DISK = 'D:\Backup\20:00_Log.trn' WITH STOPAT = '2018-02-20 18:59:59', RECOVERY

通过以上步骤，在数据库完全恢复模式下，借助于备份信息，我们可以成功找回被误删除的数据，而假如数据库是工作在简单模式下，则不能达到此效果。

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

大容量日志恢复模式是简单恢复模式和完全恢复模式的结合体，是工作在完全恢复模式下对Bulk Imports操作的改良和适应。

工作原理

在SQL Server数据库系统中，有一种快速导入数据的方法叫Bulk Imports，比如：BCP、Bulk INSERT或者INSERT INTO …SELECT。如果这些Bulk操作发生在完全恢复模式下的数据库，将会产生大量的日志信息，对SQL Server性能影响较大。大容量日志恢复模式的存在就是为了解决这个问题的，工作在Bulk-logged模式下的数据库在Bulk Imports的时候，会记录少量日志，防止事务日志的暴涨，以保证SQL Server性能的稳定和高效。可以简单的将Bulk-logged模式理解为：在没有Bulk Imports操作的时候，它与完全恢复模式等价，而当存在Bulk Imports操作的时候，它与简单恢复模式等价。所以，处于Bulk-logged模式下的数据库无法实现任意时间点恢复（point-in-time recovery），这个缺点与Simple模式类似。

适用场景

基于大容量日志模式的原理解释，它的适用场景包括：

Bulk Imports操作，比如：BCP、Bulk INSERT和INSERT INTO…SELECT

SELECT INTO操作

关于索引的一些操作：CREATE/DROP INDEX、ALTER INDEX REBUILD或者DBCC DBREINDEX

Bulk-logged模式最常用的使用场景是在做Bulk操作之前切换到Bulk-logged，在Bulk操作结束之后切换回Full模式

设置大容量日志恢复模式

将数据库设置为大容量日志模式，还是有两种方法。 方法一、使用SSMS IDE修改数据库为大容量日志恢复模式，同“设置简单恢复模式”中方法一。 方法二、使用ALTER DATABASE语句将数据库设置为大容量日志恢复模式，如下语句。

USE [master]
GO
ALTER DATABASE [AdventureWorks2008R2] SET RECOVERY Bulk_LOGGED WITH NO_WAIT
GO

应用举例

对于大容量日志恢复模式应用例子，参见如下图所示：

04.png

注：以上图片来自网络 https://sqlbak.com/academy/Bulk-logged-recovery-model/

对于该图的场景做如下解释：

10:00：对数据库做了完全备份

12:00、14:00、16:00和18:00：对数据库做了事务日志备份，其中16:00的日志备份（黄颜色标示）是在修改为Bulk-logged的模式后进行的

20:00：对数据库做了差异备份

14:30：将数据库修改为Bulk-logged模式

15:00：灾难发生，重要的数据被误删除

那么，接下来的问题就是，我们如何利用这些备份文件，尽可能的找回更多的数据，使丢失的数据最少。在原理部分，我们已经知道了，处于Bulk-logged模式下的数据库，无法实现任意时间点的恢复，因此16:00这个事务日志备份文件就无法使用，即使尝试使用也会报告如下错误：

This log backup contains Bulk-logged changes. It cannot be used to stop at an arbitrary point in time.

The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.

RESTORE LOG is terminating abnormally

最终，我们能够找回的数据最多能够使用到14:00这个事务日志备份。找回的步骤如下： 首先：我们需要还原10:00的完全备份文件，并且状态为norecovery； 其次：我们还原12:00的事务日志备份文件，状态也为norecovery； 最后：还原14:00的视图日志备份，并且将状态设为recovery带上线。 将以上的文字描述步骤用代码来表达，如下：

USE [master]
GO
RESTORE DATABASE [AdventureWorks2008R2] 
FROM DISK = 'D:\Backup\10:00_Full.bak.bak' WITH NORECOVERY, REPLACE

RESTORE LOG [AdventureWorks2008R2] 
FROM DISK = 'D:\Backup\12:00_Log.trn' WITH NORECOVERY

RESTORE LOG [AdventureWorks2008R2] 
FROM DISK = 'D:\Backup\14:00_Log.trn' WITH RECOVERY

通过以上步骤，工作在Bulk-logged模式下的数据库，无法实现任意时间恢复；因此一般该模式是使用在Bulk操作的过程中。

最后总结

本期分享了SQL Server三种恢复模式的工作原理、适用场景以及典型应用举例，以此来探讨数据库恢复模式与备份之间的关系，从中我们很清楚的理解了数据库恢复模式与备份的协同工作，来保证我们数据的安全性，以及在灾难发生的时候，可以最大限度减少数据损失。