你真的会使用SQL Server的备份还原功能吗？之一：恢复模型

在SQL Server中，除了系统数据库外，你创建的每一个数据库都有三种可供选择的恢复模型: Simple(简单), full(完整), bulk-logged(批量日志)。 下面这条语句可以显示出所有在线数据库的恢复模型:
SELECT name, (SELECT DATABASEPROPERTYEX(name, 'RECOVERY')) RecoveryModel FROM master..sysdatabases ORDER BY name

SQL Server 2005及以上版本也可以使用下面这条语句来查看:
SELECT name, recovery_model_desc FROM master.sys.databases ORDER BY name

如果想改变数据库的恢复模型，可以使用下面SQL语句:

简单恢复模型:ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE
完整恢复模型:ALTER DATABASE AdventureWorks SET RECOVERY FULL
批量日志恢复模型:ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED

在实际情况中，你应该选择使用哪种恢复模型呢？答案在于你能承受丢失多少数据。让我们用下面这些图表来说明这三种恢复模型之间的不同。下面这张图是一个数据库分别在9点和11点进行了一次完整备份。

 

1.简单恢复模型

假设硬件在10:45分时坏了。 如果数据库使用的是简单模型的话，那你将要丢失105分钟的数据。因为你可以恢复的最近的时间点是9点，9点之后的数据将全部丢失。当然你可以使用差异备份来分段运行，如下图:

像这样使用差异性备份的话，你将丢失45分钟的数据。现在，假设用户在9:50删除了一张很重要的表，你能恢复删除点之前的数据吗？答案当然是 No。因为差异性备份仅仅包含数据页的修改，它不能用于恢复一个指定的时间点。你不得不把数据库恢复到9点的状态，然后重做后面49分钟的事情。

2.完整恢复模型

假如在9点和11点之间没有进行事务日志的备份，那么你将面临和使用简单恢复模型一样的情况。另外，事务日志文件会很大，因为SQL Server不会删除已经提交和已经CheckPoint的事务，直到它们被备份。

假设每30分钟备份一次事务日志：

假如硬件在10:45分时坏了，那你只会丢失15分钟的数据。你可以使用9点的完整备份及直到10:30的事务日志来恢复。假如9:50分删除了重 要数据怎么办呢？没关系，你可以使用在10点备份的事务日志，把数据库恢复到9:49分的状态。 因为你恢复时无法直接跳过9:50那次误删除的操作日志而恢复9:50之后的数据, 所以你还必须重做误删除之后的操作。不过，这已经是不错的选择了。

注意：市场上有一些工具，可以使用事务日志来恢复用户误操作而丢失的数据，就是利用了上述原理

3.批量日志恢复模型

批量日志恢复模型被定义成一种最小化事务日志的完整恢复模型。例如select into就是一种最小化事务日志，假设这种事务发生在9:40分

这个事务将被最小化的记录下来，这就意味着SQL Server仅仅记录由于这个事务而产生的数据页的变化，它不记录每一条插入到数据表中的数据。假如9:50时一个重要的表数据被删除了，那意味着什么 呢？意味着你不能把数据库再恢复到9:49分的状态了，因为事务日志在10点时被备份并且不能恢复到一个指定的时间点上。你只能把数据库恢复到9:30分 的状态。你要记住，无论在什么时候，只要事务日志备份包含一个或多个最小化日志事务，那你就不能再把备份还原到一个指定的时间点了。

既然如此，那人们为什么还要使用批量日志恢复模型呢？一个最主要的原因就是性能。让我们以select into以例，从一个结果集来创建一张大表。假如你使用完整备份模型，那这张表中的每一条插入的数据都被记录下来，事务日志会消耗很多磁盘空间。假如你使 用批量日志恢复模型，那么仅仅会记录数据页的修改细节以达到最好的性能。就像我们刚才描述的那样，使用事务日志的好处就是可以恢复到某一个指定的恢复点， 但是会大大影响性能。

下面的几种操作都会最小化日志操作:
·批量导入操作(例如:INSERT ... SELECT * FROM OPENROWSET(BULK...), and BULK INSERT)
·select into 操作
·使用update来更新部分的大数据值数据类型。写入语句是插入或是追加数据，注意当被更新的数据存在时最小化日志不会被记录
·假如数据库恢复模型被设置为批量恢复或是简单恢复，那么一些索引的DDL操作会产生最小化日志，无论这个操作是在线还是离线被执行
·删除索引新建堆时

注意：当一个数据库的数据文件不可用时(也许是硬件坏了)，假如媒介依旧在线可用，那么你依然可以备份事务日志文件。但是你需要确定backup log命令一定要加上no_truncate选项。这样你就可以备份硬件毁坏前的事务日志了，这种方法常用来备份事务日志结尾。

然则，假如你的数据库使用批量日志恢复模型且事务日志包括最小化日志事务，那么包括被修改过的页的数据文件一定要可用，假如数据文件不可用了，也就意味着你将不能备份事务日志结尾。这也是使用批量日志恢复模型另一个需要考虑的地方。

总之，简单恢复模型提供了数量最少的恢复选项和最简单的管理模型。完整恢复模型在恢复数据库时允许更复杂的情况存在。批量恢复模型简化了一些复杂性，从而得到了更好的性能。大家可以从Books Online中得到这三种恢复更细致的对比。