《SQL Server企业级平台管理实践》读书笔记——关于SQL Server数据库的还原方式

本篇是继上篇的备份方式，本篇介绍的是还原方案，在SQL Server在2005以上现有的还原方案一般分为以下4个级别的数据还原：

1、数据库完整还原级别：

还原和恢复整个数据库。数据库在还原和恢复操作期间会处于离线状态

2、数据文件级别

还原和恢复一个数据文件或一组文件。在文件还原过程中，包含相应文件的文件组在还原过程中自动变为离线状态。访问离线文件组的任何尝试都会导致错误。但是其他文件组还能保持在线。

3、数据页级别

在完整恢复模式或大容量日志恢复模式下，可以对数据库指定还原特定的一个或一些数据页面，无须吧整个数据或整个文件都重新创建一遍。对于只损坏了很少一部分页面的大数据库，这种还原方式可以大大节约还原时间。

4、段落级别

在大型数据库里，往往包含了若干个文件或文件组。使用段落还原，可以使得数据库在还原了一部分文件或文件组以后，这部分数据就可以访问，从而达到降低数据库离线时间的目的。

这里有一点需要注意：SQL Server不允许用户备份或还原单个表

对于不同的恢复模式所支持的还原方式也不一样，下表我们列出不同的方法

还原方案	在简单恢复模式下	在完整/大容量日志恢复模式下
数据库完整还原	这是最基本的还原策略。数据库完整还原可能涉及完整数据库的简单还原和恢复。另外，完整的数据库还原还可能涉及还原完整数据库备份，以及还原和恢复差异备份	这是基本的还原策略。数据库完整还原涉及还原完整数据库备份或差异备份（如果有），以及还原所有后续日志备份（按顺序）。通过恢复并还原上一次日志备份（RESTORE WITH RECOVERY），完成数据库完整还原
文件还原	只能还原损坏的只读文件，但是不还原整个数据库，所以实用性不是很强	能够还原一个或多个文件，而不还原整个数据库。可以在数据库处于离线状态或数据库保持在线状态（对于SQL Server 2005）时执行文件还原。在文件还原过程中，包含正在还原的文件的文件组一直处于离线状态。其它文件组有可能被访问
页面还原	不适用	还原损坏的页面，可以在数据库处于离线状态或数据库保持在线状态，在页面还原过程中，正在还原的页面一直处于离线状态。必须具有完整的日志备份链（包含当前日志文件），并且必须回复所有的这些日志备份，以使页面与当前日志文件保持一致
段落还原	按文件组级别并从主文件组和所有读写辅助文件组凯斯，分阶段还原和恢复数据库	按文件组级别并从主文件组开始，分阶段还原和恢复数据库

无论以何种方式还原数据，在恢复数据库之前，SQL Server数据库引擎都会保证整个数据库在逻辑上的一致性。例如，还原一个文件以后，必须恢复完整的一套日志文件备份，以便将该文件里的事务前滚足够长度，与数据库保持一致，才能恢复该文件并使其在线。

1、数据库完整还原

将一个数据库从无到有，完整地还原出来，是最常使用的还原操作。在简单的情况下，还原操作只需要一个完整数据库备份、一个差异数据库备份和后续日志备份。很容易构造一个正确的还原顺序。例如，若要将整个数据库还原到故障点。需要首先备份活动事务日志（日志的“尾部”）。然后，按照备份的创建顺序还原最新的完整数据库备份、最新的差异备份（如果有）及所有后续日子备份。如果源数据库是简单模式，则没有响应的日志备份。恢复工作仅限于还原一个完整数据库备份，以及最后一个差异备份。

在这种模式下，用户经常遇到的一个挑战，是发生灾难后如何将数据库恢复到一个特定的恢复点。例如，一个关键数据表被人在中午12点01分误删。如何将其恢复到12点钟的那个状态？

SQL Server能够很好的支持这类需求，可它是通过恢复日志文件到指定恢复点的方式来实现的。所以，它有几个先决要求。而且是在灾难发生之前，数据库就必须满足一下条件：

1、数据库的恢复模式必须是完整恢复模式

2、灾难发生前，数据库曾经做过一个完整数据库备份（或有一套完整的文件备份）

3、在上次完整数据库备份后，如果做过任何日志备份，这些日志备份现在每个都能找到。

符合以上几点要求的数据库，就可以使用备份恢复方法将数据库恢复到完整备份后的任意一个时间点。

恢复步骤如下：

1、备份活动事务日志（也称为日志尾部）。此操作将创建尾日志备份。如果活动事务日志在灾难发生后变得不可用，则该日志部分的所有事务都将丢失

2、还原最新完整数据库备份，而且不做事务恢复

采用以下语句执行：RESTORE DATABASE database_name FROM back_dervice WITH NORECOVERY

3、如果存在差异备份，则还原最新的差异备份，而不做事务恢复

RESTORE DATABASE databse_name WITH NORECOVERY

4、从还原备份后创建的第一个事务日志备份开始，使用NORECOVERY依次还原日志。

RESTORE LOG database_name FROM back_logdervice

5、恢复数据库到某个时间点，此步骤也可以与还原上一次日志备份结合使用

RESTORE DATABSE databse_name WITH STOPAT='12：00', RECOVERY

下面我们新建一测试库，然后模拟执行一下备份、还原的整个过程，脚本如下

--做一次数据库全备份
BACKUP DATABASE [TestDB] TO  DISK = N'F:SQLTESTTestDB.bak' WITH NOFORMAT,NOINIT, 
 NAME = N'TestDB-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
--第二个模拟增量备份
BACKUP DATABASE [TestDB] TO  DISK = N'F:SQLTESTTestDBUpdate.trn' WITH DIFFERENTIAL , NOFORMAT, NOINIT, 
 NAME = N'TestDB-差异 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
--第三个事务日志备份
BACKUP LOG [TestDB] TO  DISK = N'F:SQLTESTTestDB29141107.trn' WITH NOFORMAT, NOINIT, 
 NAME = N'TestDB-事务日志  备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
--第四个事务日志备份
BACKUP LOG [TestDB] TO  DISK = N'F:SQLTESTTestDB29141108.trn' WITH NOFORMAT, NOINIT, 
 NAME = N'TestDB-事务日志  备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
--最后一次事务结尾日志（尾部日志），并且数据库处于“还原”状态
BACKUP LOG [TestDB] TO  DISK = N'F:SQLTESTTestDBLastLog.trn' WITH  NO_TRUNCATE , NOFORMAT, NOINIT, 
 NAME = N'TestDB-事务日志  备份', SKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 10
GO

然后我们按照上面描述的顺序进行一次，还原过程，代码如下

--从备份中恢复第一个全量备份
RESTORE DATABASE [TestDB] FROM  DISK = N'F:SQLTESTTestDB.bak' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
--恢复第二个增量备份
RESTORE DATABASE [TestDB] FROM  DISK = N'F:SQLTESTTestDBUpdate.trn' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
--恢复第三个事务日志备份
RESTORE LOG [TestDB] FROM  DISK = N'F:SQLTESTTestDB29141107.trn' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
--恢复第四个事务日志备份
RESTORE LOG [TestDB] FROM  DISK = N'F:SQLTESTTestDB29141108.trn' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
--恢复第五个尾部事务日志备份，并且采用recovery上线该库
RESTORE LOG [TestDB] FROM  DISK = N'F:SQLTESTTestDBLastLog.trn' 
WITH  FILE = 1, RECOVERY, NOUNLOAD,  STATS = 10
GO

如果备份事务日志尾部日志的时候，如果提示当前多用户登录，可以采用以下脚本先将数据库设置成单用户，然后还原之后再改成多用户模式

--设置成单用户模式
ALTER DATABASE TESTDB SET SINGLE_USER
GO
--设置成多用户模式
ALTER DATABASE TESTDB SET MULTI_USER
GO

此种备份还原的方式是比较简单并且易用的一种方式，也是应用最广泛使用的还原方案。如果数据库是超大数据库，这个方案已经基本能满足需求了。

但是这个方案也有它自己的缺点，最大的缺点就是要做一次数据库的完整备份恢复。这一步在空间上和时间上，都是代价昂贵的一步。

1、在时间上，SQL Server需要很长的时间来重建整个数据库。而且在这个过程中，数据库都是不能访问的。时间的长短，基本由硬盘的速度决定。一个上TB的数据库，做一个完整恢复可能需要将近一天的时间。这个等待时间，是很多系统不能接受的。

2、在空间上，一个完整备份的大小和数据库已经使用大小基本一致。如果备份是要放在硬盘上，基本上就需要能提供2倍的空间。一份放数据库，一份放备份。

其实很多时候，空间的问题还不大，因为现在的存储已经提高至很大，可是时间上的等待往往没法让人接受。这时候数据库管理员可以根据灾难的严重程度，以及手里的备份文件，以及数据的结构，看看采取别的方式来缩短这个恢复时间。下面这些方案都需要数据库预先选择完整恢复模式，操作起来比较复杂，但是用得好可以大大的缩短数据库离线时间。

2、文件还原

一个数据库会有若干文件和文件组。如果损坏只是集中在其中一个文件或文件组上，而其他大部分数据文件都没有损坏，使用传统的数据库还原方案将所有的文件重建，这样是浪费时间的。如果SQL Server只是把坏掉的那个数据文件或者文件组重建，肯定能够省下来很多时间。

可是，数据库的事务修改是会分布在各个数据文件上的。如果用备份只恢复其中一个文件，而其他文件不恢复，那他们的状态一定会不一致。一定会有许多修改在被恢复的文件里没有被包含进来。这样的数据库是无法使用的。为了使新恢复的文件能够自动恢复备份以后做的修改，SQL Server需要借助事务日志。首先在恢复之前，必须做一次当前数据库的日志备份（即所谓的尾日志备份）。恢复所有日志备份，SQL Server就能利用前滚的方法将数据文件恢复到一致的时间点。

该过程如下步骤

1、创建活动事务日志的尾日志备份

这一步是文件还原的至关重要的一步。对于离线文件还原，在文件还原之前必须使用先进行一次尾部日志备份。对于在线文件还原，在文件还原之后必须始终先进行一次日志备份。此日志备份对于将文件恢复到与数据库的其余部分一致的状态至关重要。如果因为日志已损坏而无法执行此操作，则文件还原无法进行，必须还原整个数据库。

这也是文件备份这种方式不能完全保证数据完整性的原因

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

3、针对每个还原的文件，还原最近的差异文件备份（如果有）。

4、按顺序还原事务日志备份，从覆盖最早还原文件的备份开始，到此步骤1中创建的尾日志备份结束。

虽然这里恢复的日志备份针对的是整个数据库，但是事务日志备份的恢复会是比较迅速的，因为这里仅处理还原文件所做的更改。与还原整个数据库相比，这样做还是比较会节省很多时间。

下面我们通过一个详细的例子，来进行这个备份还原的过程，我先将一个数据库建立几份文件组，如下：

下面我们先将这几个文件组进行备份，当然这个过程一般在计划任务中进行，我们先模拟这个过程：

--依次备份所有的数据文件
BACKUP DATABASE [Sales] FILEGROUP = N'PRIMARY' TO  DISK = N'F:SQLTestSalesOneFile.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'Sales-完整 文件组 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [Sales] FILEGROUP = N'FG1' TO  DISK = N'F:SQLTestSalesFileTwo.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'Sales-完整 文件组 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [Sales] FILEGROUP = N'FG2' TO  DISK = N'F:SQLTestSalesFileThree.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'Sales-完整 文件组 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [Sales] FILEGROUP = N'FG3' TO  DISK = N'F:SQLTestSalesFileFour.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'Sales-完整 文件组 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [Sales] FILEGROUP = N'FG4' TO  DISK = N'F:SQLTestSalesFileFive.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'Sales-完整 文件组 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
--先添加一个增量备份文件
BACKUP DATABASE [Sales] FILEGROUP = N'FG4' TO  DISK = N'F:SQLTestSalesFileFiveUpdate.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'Sales-完整 文件组 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
--备份一个事务日志文件
BACKUP LOG [Sales] TO  DISK = N'F:SQLTestSalesLog.trn' WITH NOFORMAT, NOINIT,  NAME = N'Sales-事务日志  备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

假设这个时候，FG4文件组坏掉了，我们要采用文件恢复的模式，进行数据恢复，我们采取线上恢复的方式

--在线还原文件组FG4也就是第四个文件
RESTORE DATABASE [Sales] FILE = N'File4' FROM  DISK = N'F:SQLTestSalesFileFive.bak' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
--经过上面的步骤该文件已经还原上了，但是文件组FG4为NoRECOVERY状态
--我们这时候需要进行日志尾部备份，以确保该文件离线时候所做的事务能正确执行
--我们采用Copy_Only
BACKUP LOG [Sales] TO  DISK = N'F:SQLTestSaleslastLog.trn' WITH Copy_only
GO
--我们还原一系列的日志备份文件,最后将我们上面获取的最后的日志备份恢复
RESTORE LOG [Sales] FROM  DISK = N'F:SQLTestSalesLog.trn' 
WITH  NORECOVERY
GO

RESTORE LOG [Sales] FROM  DISK = N'F:SQLTestSaleslastLog.trn' 
WITH  RECOVERY
GO

到此文件还原的方式实现完毕，如果说只读文件，只需要一句RESTORE恢复备份文件组就可以，不需要任何日志，需要注意的是在SQL Server2005一下版本中系统中的“维护计划”还不支持文件备份的方式。有没有更细粒度的恢复呢，我们来看下一步的恢复方法。

3、页面还原

还有一种损坏，比如经典的824页面读取错误，这种损坏不像前面那么严重。数据库的每个文件都能打开，只是其中的一些页面坏了。

对于这种情况可以借助DBCC CHECKDB来进行数据修复。如果DBCC命令无法在不丢数据的前提下修复数据，或者哪怕是允许丢数据库也修不好数据库，管理员只能做数据库完整恢复。但这种情况下，为了少数几个页面而恢复整个数据库，代价就非常昂贵。在SQL Server2005之后，引入了一个页面还原的功能，可以只还原指定若干页面，从而能够大大节省数据库恢复时间。可以说是个能够急救的功能项。

页面还原用于修复隔离的损坏页。还原和恢复少了页面的速度可能比还原一个文件更快，因此减少了还原操作中处于离线状态的数据量。然而，如果文件中要还原的不只是少了页面而是多数页面，则还原整个文件更为有效。例如，如果某个文件上的大量页都指出此文件有未解决的故障，不妨考虑直接还原该文件。

通常，要进行还原的页已经由于在访问页时遇到错误而标记为“可疑”。可疑页在msdb数据库的suspect_pages表中进行标识。可以立即还原多个数据库页。其过程跟上面的文件还原过程一样。在页面还原后，也要恢复所有的日志文件备份。每次传递日志重做，前滚集都会前进一步。

当然这里面还是有限制的，页面还原仅可以还原数据页。页面不能还原不能用于还原下列内容：

事务日志
分配页：全局分配映射（GAM）页、共享全局分配映射（SGAM）页和页可用空间（PFS）页。这些系统页面损坏，页面还原无法恢复
所有数据文件的页0（文件启动页）
页1:9（数据启动页）
全文目录（Fulltext search catalog）

页面还原还要符合下列要求：

数据库必须使用完整恢复模式。使用大容量日志恢复模式时可能不能成功。简单恢复模式无法使用这一功能。
只读文件组中的页面无法还原
还原顺序必须从完整备份、文件备份或文件组备份中恢复页面开始。所以如果没有一份数据页面损坏之前做的的备份，也是无法进行还原
页面还原需要截止到当前日志文件的连续日志备份，并且必须恢复所有日志备份后，页才能恢复到当前正常状态。所以如果数据库曾经曾经做过截断日志动作，或者有份日志备份现在找不到了，那也无法进行页面恢复。
数据库备份和页面还原不能同时进行。

页面还原的步骤如下：

1、获取要还原的损坏页的页ID。校验和或残缺写错误将返回页ID,并提供指定页所需的信息。可以通过查询msdb数据库里的suspect_pages表。或者监视事件和SQL Server errorlog文件里所报出的错误信息，查找随坏页的页ID.

select * from msdb..suspect_pages

2、从包含页的完整数据库备份、文件备份或文件组备份开始进行页面还原。在RESTORE DATABASE 语句中，使用PAGE子句列出所有要还原的页的页ID。

PAGE='filepage'

3、应用最近的差异备份。

4、应用后续日志备份。

5、创建新的数据库尾日志备份。

6、还原新的尾日子备份。应用这个新的日志备份后，就完成了页面还原，可以开始正常访问页面。

比如：我们有个库，文件B的文件ID为1，损坏的页的ID分别为57、202，而之前这个库存在B文件的文件备份，并且还有两个日志备份，我们执行以下脚本顺序还原

RESTORE DATABASE <database> PAGE='1:57,1:916'
FROM <file_backup_of_file_B>
WITH NORECOVERY
GO
RESTORE LOG  <database>     FROM <log_backup>
WITH NORECOVERY
GO
RESTORE LOG  <database>     FROM <log_backup>
WITH NORECOVERY
GO
--这一步很关键，一定要备份数据库尾部日志
BACKUP LOG <database>  TO <new_log_backup>
GO
--还原最后备份的尾部日志
RESTORE LOG <database>  FROM <new_log_backup> WITH RECOVERY
GO

此方法针对824问题是最直接的，也是最快的解决方式，但是在SQL SERVER 2012版本之前不提供界面方式提供，只能使用SQL语句执行。

数据库备份方式选择，可参考此篇文章：http://www.cnblogs.com/zhijianliutang/p/4063697.html