sqlserver 数据库备份与恢复

 

数据库备份与恢复 之一 概述

分类： SQL Server 数据库管理2012-10-17 16:21 633人阅读 评论(0) 收藏 举报

目录(?)[+]

数据安全是数据库的生命。管理员可以小心地在软件层面配置各种安全策略，防止数据的意外丢失。可是再小心，也很难保证数据的100%安全。难免会有一些意外灾难发生，例如：

   使用者错误

比如，一个有管理员权限的使用者不小心把整张表都删掉了；或者安全策略有漏洞，数据被人恶意修改。

   硬件故障

比如硬盘损坏，里面的数据文件无法再被访问;或者服务器整个故障，甚至无法启动。

   自然灾害

例如火灾、地震等，使得服务器甚至整个机房在物理上彻底损毁。

意外发生后，如果数据库管理员想不出办法将系统恢复，后果可能非常严重。如果您的SQLServer系统部署有高可用性和灾难恢复方案，根据您选择的方案的能力，可能可以顺利地抵御上述的灾难。各种方案的灾难恢复手段各有不同，详细的内容需要参阅第二章和第三章，或者相关技术的技术文档。本章讨论的内容，是当您没有部署灾难恢复策略，或者您现有的方案无法抵御所有可能发生的灾难时，必须使用的数据库备份与恢复技术。

备份数据库并确保在灾难出现后能够将其及时恢复到预期的状态，是数据库管理员不可推卸的职责。系统不可用、性能缓慢这些问题，可能是由其他不可控制的因素导致。但是如果当意外发生，数据库管理员却不能恢复数据，可能就找不到什么合理的理由推卸责任。所以备份与恢复可以说是数据库管理员的第一要职，虽然技术含量可能没有其他问题那么高，但是却往往会更为基础和重要。

数据库管理员在实际工作中，经常遇到的挑战有哪些呢？

1. 应该选择什么样的备份策略？

SQLServer提供了许多种备份方法，例如，完整备份、部分备份、文件备份、差异备份、日志备份等，有些让人眼花缭乱。怎样根据自己企业的具体需求和资源限制，设计出最合适的备份策略呢？

2. 如何减少备份恢复时间？

在生产环境里，数据库不可用就意味着企业业务无法正常运作。每多拖一分钟，就会多一份损失。面对心急如焚的高层领导，数据库管理员不仅要求能够将数据库恢复，而且还要求能在尽量短的时间内将数据库上线。怎么才能将恢复时间降到最少呢？

3. 如何将数据库恢复到我想要的时间点？

当数据库发生了意外的修改（误操作，或者数据攻击），管理员需要将数据库恢复到最近正常的时间点。选择什么样的备份策略，怎么才能做到这一点呢？

4. 如何迁移数据库系统到一台新机器？

当灾难发生，很可能原有的服务器将无法继续使用。管理员需要准备一台新的服务器，将整个系统恢复起来。对于SQLServer，需要做什么样的操作才能使得原先系统的所有内容，包括用户账号、密码、任务脚本都能移到新系统上？

要解决以上这些挑战，管理员要先了解SQLServer能够提供哪些数据库备份和恢复功能，能将这些功能最恰当地应用。

 

数据库备份与恢复 之二 备份概述

分类： SQL Server 数据库管理2012-10-17 16:23 490人阅读 评论(0) 收藏 举报

目录(?)[+]

SQL Server的开发部门一直在致力完善SQL Server的备份恢复功能，希望帮助数据库管理员用最小的代价保证数据安全。所以基本上每个版本在这方面都有功能扩充。丰富的功能带来的一个副作用是，在联机丛书或其他介绍备份恢复的文档里，会看到很多种备份方法，让人有点莫衷一是。它们之间是什么关系呢？这里让我们来梳理一下。

SQL Server数据库分数据文件和日志文件。为了使得数据库能够恢复到某个一致点，备份不仅需要拷贝数据库数据文件里的内容，还要拷贝日志文件里的内容。那么根据每次备份的目标不同，我们可以将备份分为数据备份和日志备份。

数据备份的范围可以是完整的数据库、部分数据库、一组文件或文件组。所以根据备份下来的数据文件的范围，又分成了完整数据库备份、文件备份和部分备份。

完整数据库备份

数据库备份就是拷贝下数据库里的所有信息，通过一个单个完整备份，就能将数据库恢复到某个时间点的状态。但是由于数据库备份是一个在线的操作。一个大的完整数据库备份可能需要一个小时甚至更长的时间。数据库在这段时间里还会发生变化。所以完整数据库备份还要对部分事务日志进行备份，以便能够恢复数据库到一个事务一致的状态。

完整数据库备份易于使用。它包含数据库中的所有数据。对于可以快速备份的小数据库而言，最佳方法就是使用完整数据库备份。但是，随着数据库的不断增大，完整备份需花费更多时间才能完成，并且需要更多的存储空间。仅做完整备份可能不能满足用户需求。

文件备份

文件备份指备份一个或多个文件或文件组中的所有数据。在完整恢复模式下，一整套完整文件备份加上跨所有文件备份的日志备份合起来等同于完整数据库备份。使用文件备份能够只还原损坏的文件，而不用还原数据库的其余部分，从而可加快恢复速度。例如，如果数据库由位于不同磁盘上的若干个文件组成，在其中一个磁盘发生故障时，只须还原这个故障磁盘上的文件的备份，其他磁盘上的文件无须还原。这样会缩短还原时间。

部分备份

部分备份与完整数据库备份类似，但是部分备份默认只包含数据库可读写部分，数据库的只读文件将不会被备份。因为只读部分是不会发生变动的，总是去备份它有点浪费。所以部分备份在希望不备份只读文件组时非常有用。

部分备份可以说是数据库备份和文件备份之间的一个中间类型。如果一个数据库里没有只读文件，那么部分备份和数据库备份就没什么差别。

数据库文件常常是非常巨大的。在流行数据集中的趋势下，库容上TB的数据库现在已屡见不鲜。对于这样的一个数据库，做数据库备份，哪怕是文件备份都是一件非常昂贵的事情，可能不是每天都能去做的。在这种背景下，只备份从上次备份以后的差异，就是个必需的技术。

从是否拷贝所有的数据来分，数据备份又可以分完整备份和差异备份。

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

对于完整数据库备份、文件备份和部分备份这3种数据备份形式，SQL Server都能够做完整备份和差异备份。所以，引出了一共6种数据备份模式：完整数据库备份、完整文件备份和完整部分备份，差异数据库备份、差异文件备份和差异部分备份。

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

仅复制备份(Copy-Only)是独立于常规SQL Server备份序列的SQL Server备份。通常，进行备份会更改数据库并影响其后备份的还原序列。但是，有时在不影响数据库全部备份和还原过程的情况下，为特殊目的而进行备份还是有用的。为实现此目的，SQLServer 引入了下列两种仅复制备份：

n   仅复制完整备份

仅复制完整备份也备份整个数据库的内容。它和正常的完整备份的区别是，做完了以后差异备份的基准不会变，因此不影响差异备份序列。

n   仅复制日志备份

仅复制日志备份只备份当前日志文件里现有的内容，但是不会清空日志文件里备份下的日志。因此，下次再做正常日志备份的时候，这些内容还会被再次备份下来，从而不影响常规日志备份的序列。这种备份主要用在以下情况：数据库上已经有了一个备份计划任务在运行，但是现在需要紧急做一个日志备份，但同时不能影响到原有的备份序列。

现在可以总结出SQL Server提供了以下11种主要备份方法（见表9-1）。

表9-1  SQL Server提供的11种主要备份方法

分　　级	数据备份			日志备份
数据库级	完整数据库备份	仅复制完整数据库备份	差异数据库备份	（一般） 日志备份	仅复制 日志备份
文件级	完整文件备份	仅复制完整文件备份	差异文件备份
部分	完整部分备份	仅复制完整部分备份	差异部分备份

面对这么多的备份方法，到底该怎么制定自己的备份策略呢？让我们先去掉几个不太常用的方法。

首先，仅复制备份这类方法的出现，是为了防止将要做的备份会破坏现有的备份策略。例如，对一个已经建立了严格备份规则（例如Logshipping）的数据库，现在需要做一个日志备份到另一个文件夹里。普通的日志备份会破坏现有备份文件系统所维护的日志链。仅复制备份就不会破坏。这种方法一般只是在偶尔的情况下有目的地使用，不需要在制定备份策略的一开始考虑。

在现实使用中，很少有数据库专门维护一个只读的文件或文件集。（这种方法维护成本较高，只会在非常巨大的数据库上才能体现出优势。）所以部分备份也很少用到。

因此上表可以简化成几个最传统，也是最常用的备份方法（见表9-2）。

表9-2  最常用的备份方法

分　　级	数据备份		日志备份
数据库级	完整数据库备份	差异数据库备份	（一般）日志备份
文件级	完整文件备份	差异文件备份

顺便想提一句的是，有些用户喜欢用直接拷贝数据库文件，然后用文件附加（Attach）的方式备份和恢复数据库。这种方式一般不推荐，因为：

（1）SQL Server在运行的时候，对文件施加了排他锁，通过一般的方法是不能直接拷贝文件的。除非通过一些备份软件，否则只能停掉SQLServer服务，或者关闭数据库，才能备份文件。

（2）SQL Server在理论上，只保证通过运行sp_detach_db语句得到的数据库文件，才一定能被成功地附加上。如果用户是通过暂停SQL Server服务或其他方法得到的文件，SQLServer不能保证就一定能附加得上。

（3）有些用户只拷贝数据文件，不拷贝日志文件的做法，是非常不规范的。很容易导致数据库不能正常恢复，从而丢失数据。

如果用SQL Server提供的接口做备份恢复，建议还是使用Backup和Restore命令。如果借助第三方软件，要确认其对SQL Server的支持度。

拷贝文件的方法也不是一定不能用。笔者在做灾难恢复的时候，如果数据库不是很大，时间又允许，会先做一个数据库备份，再做一个文件级备份，以期双保险。但是它的前提，是文件拷贝发生在SQLServer被成功关闭后，或者sp_detach_db后。而且所有的文件都要做备份，包括日志文件。

 

数据库备份与恢复 之三 选择备份策略和恢复模式

分类： SQL Server 数据库管理2012-10-17 16:29 1106人阅读 评论(0) 收藏 举报

目录(?)[+]

SQL Server提供了足够多的技术来做各种各样的数据库备份。作为一个数据库管理员，应该选择怎样的备份策略呢？建议您问自己两个问题。

（1）您管理的数据库最多能够容忍多长时间的数据丢失？

（2）您准备投入多少人力物力来做数据库备份与恢复策略？

问题似乎有点残酷。但是世界上大多数事情，要获得越好的效果，就需要越多的投入。数据库备份策略尤其是这样。不考虑镜像技术（包括SQLServer自己的数据库镜像和物理磁盘级镜像），SQL Server不可能时时刻刻地做数据库备份，每次备份之间总要有一定的时间间隔。而这个间隔之间的数据变化在下一次备份之前，是没有保护的。所以讲到底，数据丢失的最大时间段，就是两次备份之间的时间间隔。利用备份恢复机制保护数据，是不可能保证数据一点都不丢失的。如果您的用户提出的要求是不能有任何数据丢失，则必须跟用户沟通，让他们了解这样的要求仅使用数据库备份技术实现是不现实的，需要做更大的投入，引入镜像技术。

既然数据丢失的最大时间段，就是两次备份之间的时间间隔，那么备份做得越多，数据丢失量就会越少。可是，做备份越频繁，需要的投入也越多。涉及的因素有：

（1）备份越多，要管理的备份文件也越多，数据库恢复时要恢复的文件也越多。要建立一个合适的备份管理制度。

（2）备份虽然不会阻塞数据库的正常操作，但是会产生一系列的硬盘读写。如果服务器本身I/O就比较繁忙，备份动作会进一步影响数据库的性能。须要增强服务器的硬盘读写处理能力，才能避免这种问题发生。

（3）备份难免会因为种种因素失败。备份越勤，遇到失败的几率越大。管理员要及时处理错误，将备份任务恢复常态。这对管理员的要求也比较高。

当您对将要投入的人力物力心中有数以后，就可以来决定采用什么样的备份策略了。

使用日志备份，可以将数据库恢复到故障点或特定的时点。所以日志备份在备份策略中扮演着很重要的角色。但是日志备份只能在完整恢复模式和有些大容量日志恢复模式的数据库上进行。

制定备份策略，首先要决定是否需要做日志备份。如果需要做日志备份，数据库恢复模式就要选成完整模式。（大容量恢复模式不能总保证日志备份成功，所以一般不推荐在生产环境下使用。）如果不做日志备份，数据库模式就要设置简单，否则会遇到日志文件无限增长问题。

9.2.1 简单恢复模式下的备份

 

简单恢复模式下，不能做日志备份。所以它只支持最简单的备份和还原方式，很容易管理。不过如果没有日志备份，就只能将数据库恢复到最后一次备份的结尾。如果发生灾难，数据库最后一次备份之后做的数据修改将全部丢失。

在简单恢复模式下，工作损失风险会随时间增长而增加，直到进行下一个完整备份或差异备份为止。因此，建议您排订充足的备份的频率，以避免遗失大量数据。同时，频率也不能太高而让备份变得难以管理。

为了降低风险，可以引入差异备份。图9-2显示了使用差异数据库备份补充数据库完整备份，来减轻工作损失风险的一种备份策略。在第一次数据库备份之后，连续建立了3次差异备份。第3个差异备份后，进行数据库完整备份，建立新的差异基准。因为差异备份的开销一般都比完整备份低，所以能够比较经常地运行。这样的备份策略可以使用在数据量稍大，能够容忍较长时间数据丢失的数据库上。

以上两种备份策略的优势，是不管是备份还是恢复，管理起来都比较简单。但是不管是数据库完整备份，还是差异备份，都不可能以比较频繁的频率进行，一般都只能在晚间进行。如果数据库比较庞大，或者不允许比较长时间的数据丢失，这样的备份策略是不能满足要求的。必须引入日志备份，建立更为复杂，但是也更强大的备份恢复策略。

 

9.2.2 完整恢复模式下的备份

 

选取完整恢复模式，就可以使用日志备份。由于日志备份只拷贝上次日志备份以来的所有日志记录，所以开销会比数据库备份小很多。可以定义以一种很频繁的频率（5分钟甚至更短）来做备份，以达到在最大限度内，防止出现故障时丢失数据的目的。使用日志备份的优点是允许您将数据库还原到日志备份内包含的任何时点（“时点恢复”）。假定可以在发生严重故障后备份活动日志，则可将数据库一直还原到没有发生数据丢失的故障点处。使用日志备份的缺点是它们的数量很多，而且恢复备份时，需要严格按照备份产生的顺序依次恢复。中间不能有任何备份缺失或跳跃。所以日志备份做得越多，还原时间就越长，管理复杂性也越高。

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

出现故障后，可以尝试备份“日志尾部”（尚未备份的日志）。如果尾日志备份成功，则可以通过将数据库还原到故障点来避免任何工作丢失。所以这种备份计划的优点也是很明显的。

但是上述备份计划的一大缺陷，就是灾难发生后需要恢复的日志文件数目太多。假设每个小时做一次日志备份，每周日做一次数据库备份，如果灾难在周五发生，就不得不恢复上百个日志备份。这个工作量和所要花的时间是很大的。为了最大程度地缩短还原时间，可以对数据库进行一系列差异备份做补充。

 

 

 

下面我们以Adventure数据库为例子，来具体了解一下完整恢复模式下的数据库备份。

 

第一步：对数据库做一个全备份:

BACKUP DATABASE [AdventureWorks] TODISK='C:\temp\AdvFull1.bak'

 

然后用下面的查询，显示AdventureWorks这个数据库历史上曾经的备份信息。这些信息存放在msdb数据库中，所以可以查询得到。

SELECT DISTINCT s.first_lsn, s.last_lsn,

                           s.database_backup_lsn,s.backup_finish_date,

                           s.type,y.physical_device_name 

FROM msdb..backupset AS s INNER JOIN

msdb..backupfile AS f ONf.backup_set_id = s.backup_set_id INNER JOIN

msdb..backupmediaset AS m ONs.media_set_id = m.media_set_id INNER JOIN

msdb..backupmediafamily AS y ONm.media_set_id = y.media_set_id

WHERE  (s.database_name = 'AdventureWorks')

ORDER BY  s.backup_finish_date DESC;

 

这里有几个字段的含义我们需要稍微解释一下。对于日志备份来讲，first_lsn标识备份集中第一个日志记录的日志序列号，last_lsn标识备份集之后的下一条日志记录的日志序列号。所以(first_lsn,last_lsn-1)标识了这个日志备份所包含的所有日志序列。这里last_lsn-1表示前面一条日志序列号，而不表示日志序列号减1。因为日志序列号虽然是递增的，但并不一定以1为单位递增。

 

对于全备份或者差异备份来讲，它们也会包含有少量的日志信息。(first_lsn,last_lsn)表示做数据恢复时，在做roll forward（前滚）动作时，一定要遍历的LSN。不然的话，数据库在做恢复的时候，数据会不一致。

 

database_backup_lsn标识上一次数据库做全备份的起始LSN。Type标识数据库备份的类型，主要有: D(数据库)， L(日志)， I(差异数据库)，F(文件或文件组)。

 

所以上面的查询显示，我们做的备份是一个数据库全备份，备份存放在C:\temp\AdvFull1.bak。

 

第二步：对数据库做一个操作，然后紧接着做一个日志备份

CREATE TABLE TESTTABLE

(number int,

 name nvarchar(50)

)

INSERT INTO TESTTABLE VALUES (1,'aaaa')

GO

BACKUP LOG [AdventureWorks] TODISK='C:\temp\AdvLog2.bak'

 

再次执行查询，读者可以看到，又添加了一条新的记录，这个备份是日志备份。这个日志备份的LSN是从45000000052600001到46000000007400001。

 

第三步：对数据库做一个操作，再做一个日志备份

INSERT INTO TESTTABLE VALUES (2, 'bbbb')

GO

BACKUP LOG [AdventureWorks] TODISK='C:\temp\AdvLog3.bak'

 

再次执行查询，读者可以看到，又添加了一条新的记录，这个备份是日志备份， LSN是从46000000007400001到46000000007600001，读者可以看到这个日志备份的first_lsn和上一次日志备份的last_lsn一定是一样的。这是因为，数据库在做日志备份的时候，LSN要求是连续的。

 

第四步：对数据库做一个操作，再做一个差异备份

INSERT INTO TESTTABLE VALUES (3, 'cccc')

GO

BACKUP DATABASE AdventureWorks TODISK='C:\temp\AdvDiff4.bak' WITH DIFFERENTIAL

 

再次执行查询，读者可以看到，又添加了一条新的记录，这个备份是差异备份

一直这样操作，直到最后我们对数据库做一次日志操作。

从上面的查询结果，我们可以发现：

（1）不管是全备份还是差异备份，都不会影响LSN的序列。所以，即使最近的几个全备份或差异备份受损，只要有一个全备份，以及该全备份后所有的日志备份，我们也是能够完整无缺地把数据恢复出来，只是恢复的时间会稍微长一点。中间的差异备份或其他全备份只是减少了我们需要恢复的日志备份数目。这进一步说明了日志备份的重要性。

（2）日志备份的LSN是连续的。否则的话，在恢复的时候，则会碰到日志链断裂的问题，恢复是不能继续下去的。

 

9.2.3  文件或文件组备份

完整文件备份指备份一个或多个文件或文件组中的所有数据。在完整恢复模式下，一整套完整文件备份和跨所有文件备份的日志备份合起来，等同于一个完整数据库备份。使用文件备份能够只还原损坏的文件，而不用还原数据库的其余部分，从而可加快恢复速度。例如，如果数据库由位于不同磁盘上的若干个文件组成，在其中一个磁盘发生故障时，只须还原故障磁盘上的文件。

文件备份在默认情况下包含足够的日志记录，可以将文件前滚至备份操作的末尾。（但是在简单恢复模式下，必须一起备份所有读/写文件，而不是逐个指定每个读/写文件或文件组。）

相对于数据库备份，文件备份具有如下优点：

·        能够更快地从隔离的媒体故障中恢复。可以迅速还原损坏的文件。

·        与完整数据库备份（对于超大型数据库而言，变得难以管理）相比，文件备份增加了计划和媒体处理的灵活性。文件或文件组备份的更高灵活性对于包含具有不同更新特征的数据的大型数据库也很有用。

与完整数据库备份相比，文件备份的主要缺点是管理较复杂。如果某个损坏的文件未备份，那么媒体故障可能会导致无法恢复整个数据库。因此，必须维护一组完整的文件备份，对于完整/大容量日志恢复模式，还必须维护一个或多个日志备份，这些日志备份至少涵盖第一个完整文件备份和最后一个完整备份之间的时间间隔。

维护和跟踪这些完整备份是一种耗时的任务，所需空间可能会超过完整数据库备份的所需空间。所以这种备份策略在实际使用中应用得还是比较少的。它只有在管理超大数据库时，才能发挥出其不可替代的优势。

在完整恢复模式下，一整套完整文件备份与涵盖从第一个文件备份开始的所有文件备份的足够日志备份合起来等同于完整数据库备份。

仅使用文件备份和日志备份还原数据库的操作可能比较复杂。因此，如果可能，最好执行完整数据库备份并在第一个文件备份开始之前开始日志备份。图9-10显示了在创建数据库（在t0时间）之后立即执行完整数据库备份（在t1时间）的策略。创建了第一个数据库备份之后，便可开始执行事务日志备份。事务日志备份计划按设置的间隔执行。文件备份以最适合数据库业务要求的间隔执行。此图显示了4个文件组，每次备份其中的一个文件组。它们的备份顺序（A、C、B、A）反映了数据库的业务要求。

在完整恢复模式下，恢复一个文件组备份，不但需要恢复文件组备份本身，还需要依次恢复从上一次完整数据库备份后，到恢复的目标时间点为止的所有日志备份，以确保该文件与数据库的其余部分保持一致。所以要恢复的事务日志备份数量会很多。要避免这种情况，可以考虑使用差异文件备份。可是这样会使整个备份计划更加难于管理。这也是为什么文件备份不常使用的重要原因。但是在管理超大数据库时，这可能是唯一的选择。

 

数据库备份与恢复 之四 选择数据库还原方案

分类： SQL Server 数据库管理2012-10-17 16:33 673人阅读 评论(0) 收藏 举报

目录(?)[+]

为了帮助用户能以最快的速度还原数据库，SQLServer也在不断引入新的还原方法。SQL Server一共可以支持4个级别的数据还原：

   数据库（“数据库完整还原”）级

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

   数据文件（“文件还原”）级

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

   数据页（“页面还原”）

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

   一部分（“段落还原”）

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

但是SQL Server不允许用户备份或还原单个表。

SQL Server中的“还原方案”是指从一个或多个备份中还原数据，继而恢复数据库的过程。可选的还原方案取决于数据库的恢复模式和SQLServer的版本。

表9-3介绍了不同恢复模式所支持的各种还原方案。

表9-3  不同恢复模式所支持的各种还原方案

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

* 只有SQL Server 企业版支持在线还原。

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

下面我们从最传统的数据库完整还原开始，详细介绍SQLServer的主要还原方法。

数据库完整还原

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

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

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

（1）数据库的恢复模式必须是完整恢复模式。

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

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

符合这些要求的数据库，就可以使用备份恢复方法将数据库恢复到完整备份后的任意一个时间点。通常，将数据库恢复到故障点的基本步骤如下：

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

（2）还原最新完整数据库备份，而且不做事务恢复（RESTORE DATABASE database_nameFROM backup_deviceWITH NORECOVERY）。

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

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

（5）恢复数据库到某个时间点（RESTORE DATABASE database_name WITHSTOPAT='????????', RECOVERY）。此步骤也可以与还原上一次日志备份结合使用。

示例

AdventureWorks示例数据库在数据库备份之前已经设置使用完整恢复模式。下面的示例将创建AdventureWorks数据库的尾日志备份。接下来，将还原较早的完整数据库备份和日志备份，最后还原尾日志备份。事务恢复动作，将在最后的尾日志恢复步骤中完成。在此之前，数据库都不能被访问。

注意：在此示例之前，数据库已经做过一个数据库完整备份和一个日志备份。

 

--灾难发生后，试图创建一个尾日志备份

BACKUP LOG AdventureWorks

TO DISK = 'Z:\SQLServerBackups\AdventureWorks.bak'

   WITH NORECOVERY;

GO

--从备份恢复一个全备份

RESTORE DATABASE AdventureWorks

  FROM DISK ='Z:\SQLServerBackups\AdventureWorks.bak'

  WITH FILE=1,

    NORECOVERY;

 

--从备份中恢复一个正常的日志备份

RESTORE LOG AdventureWorks

  FROM DISK ='Z:\SQLServerBackups\AdventureWorks.bak'

  WITH FILE=2,

    NORECOVERY;

 

--用STOPAT恢复尾日志备份

RESTORE LOG AdventureWorks

  FROM DISK ='Z:\SQLServerBackups\AdventureWorks.bak'

  WITH FILE=3, STOPAT='XXXXxx:xx:xx',

   RECOVERY;

GO

上面的还原方案管理和执行都比较简单，是最广泛使用的还原方案。如果您的数据库不是超大数据库，这个方案就能够基本满足需求了。

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

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

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

空间的问题大部分时候还能找到办法，可是时间上的等待往往让人难以接受。这时候数据库管理员可以根据灾难的严重程度，以及手里的备份文件，以及数据库的结构，看看是不是可以用下面这些比较不常用的还原方案。这些方案都需要数据库预先选择了完整恢复模式，操作起来比较复杂，但是如果用得好，能够大大缩短数据库离线时间。

文件还原

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

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

从文件备份和差异文件备份中还原一个或多个受损文件的步骤基本如下：

（1）创建活动事务日志的尾日志备份。

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

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

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

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

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

下面举一个示例。一个名为adb的数据库（使用完整恢复模式）包含3个文件组。文件组A为读/写文件组，而文件组B和C是只读的。最初，所有文件组都处于在线状态。

现在文件组A中的文件a1已损坏，数据库管理员决定在数据库处于在线状态时还原该文件。

（1）在线还原文件a1。

RESTORE DATABASE adb FILE='a1' FROM backup WITH NORECOVERY

（2）此时，文件a1处于RESTORING状态，文件组A处于离线状态。

（3）完成文件还原之后，数据库管理员进行新的日志备份以确保捕获到该文件离线时的点。（之前曾经做过两次日志备份）

BACKUP LOG adb TO log_backup3WITH COPY_ONLY

（4）在线还原日志备份。

RESTORE LOG adb FROM log_backup1WITH NORECOVERY

RESTORE LOG adb FROM log_backup2WITH NORECOVERY

RESTORE LOG adb FROM log_backup3WITH RECOVERY

（5）文件a1现处于在线状态。数据库恢复完成。

页面还原

还有一种经常出现的情形，数据库发生了损坏，可是损坏不像前面那么严重。数据库的每个文件都能打开，只是其中的一些页面坏了。

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

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

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

需要注意的是，页面还原仅可以还原数据页。页面还原不能用于还原下列内容：

·        事务日志。

·        分配页：全局分配映射（GAM）页、共享全局分配映射（SGAM）页和页可用空间（PFS）页。这些系统页面损坏，页面还原无法恢复。

·        所有数据文件的页0（文件启动页）。

·        页1:9（数据库启动页）。

·        全文目录（Fulltext searchcatalog）。

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

·        数据库必须使用完整恢复模式。使用大容量日志恢复模式时可能不能成功。简单恢复模式无法使用这一功能。

·        只读文件组中的页面无法还原。

·        还原顺序必须从完整备份、文件备份或文件组备份中恢复页面开始。所以如果没有一份数据页面损坏之前做的备份，也无法进行还原。

·        页面还原需要截止到当前日志文件的连续日志备份，并且必须恢复所有这些日志备份后，页才能恢复到当前正常状态。所以如果数据库曾经做过截断日志动作，或者有份日志备份现在找不到了，那也无法进行页面恢复。

·        数据库备份和页面还原不能同时运行。

页面还原基本语法

若要在RESTOREDATABASE语句中指定一页，需要知道该页所在文件的文件ID和该页的页ID。所需语法如下：

RESTORE DATABASE database_name

   PAGE = 'file:page [ ,...n]' [ ,...n ]

   FROM _device> [ ,...n ]

WITH NORECOVERY

页面还原的过程

页面还原的基本步骤如下：

（1）获取要还原的损坏页的页ID。当SQL Server遇到校验或残缺写错误时，会返回页面编号，并将这些信息记录下来。可以通过查询msdb数据库里的suspect_pages表，或者监视事件和SQL Server errorlog文件里所报出的错误信息，查找损坏页的页ID。

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

PAGE = 'file:page'

（3）应用最近的差异备份。

（4）应用后续日志备份。

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

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

示例

以下示例使用NORECOVERY还原文件B的4个损坏页。随后，将使用NORECOVERY应用两个日志备份，然后是尾日志备份（使用RECOVERY还原）。

此示例中，文件B的文件ID为1，损坏的页的页ID分别为57、202、916和1016。

RESTORE DATABASE  PAGE='1:57, 1:202, 1:916,1:1016'

   FROM _backup_of_file_B>

   WITH NORECOVERY;

RESTORE LOG  FROM _backup>

   WITH NORECOVERY;

RESTORE LOG  FROM _backup>

   WITH NORECOVERY;

BACKUP LOG TO _log_backup>

RESTORE LOG  FROM _log_backup>WITH RECOVERY;

GO

段落还原

文件还原和页面还原适用于数据库损坏的范围比较小的情况，因为恢复时间比完整备份恢复短，所以可以大大缩短数据库恢复所造成的不可访问时间。但是如果数据库的损坏范围很大，可能跨越了若干个数据文件甚至文件组，这时候可能不得不恢复整个数据库。如果数据库很大，这段不可访问的时间就很长了。有时候一个大数据库有若干数据文件组，有些文件组里存放的是应用程序现在经常访问的数据，有些文件组存放的是历史数据，不经常访问。如果管理员能把应用经常访问的数据所在的文件组先恢复，数据库就恢复访问，那大部分应用程序就可以提前恢复正常。历史数据文件组可以在后台慢慢恢复。

为了满足这种需求，SQLServer提供了段落还原这种机制。段落还原包括从主文件组开始的一系列还原顺序，有时也可以从一个或多个辅助文件组开始。在还原顺序结束后，如果恢复的文件有效并且与数据库一致，则恢复的文件将能直接变为在线状态，恢复用户访问。

基本上所有版本的SQLServer 都支持离线段落还原。在SQL Server企业版中，段落还原还可以是在线的。离线和在线段落还原的含义如下：

n   离线段落还原方案

在离线段落还原中，数据库在部分还原顺序之后处于在线状态。尚未还原的文件组保持离线状态，而数据库必须进入离线状态后，才能继续还原没有还原的文件组。

n   在线段落还原方案

在进行在线段落还原时，数据库在完成部分还原顺序后，主文件组已经被恢复，它和所有其他已恢复的辅助文件组都处于可用状态，数据库进入在线状态，用户可以访问。尚未还原的文件组保持离线状态，而后面还原这些文件组时，数据库还能保持在线状态。

需要注意的是，如果仅还原了一部分文件组，则数据库和这些文件组有关的事务可能会被挂起，影响一部分数据页面的访问。这是正常现象，因为整个数据库必须一致。

无论数据库采用何种恢复模式，部分还原顺序都从RESTOREDATABASE语句开始，并且要指定PARTIAL选项。PARTIAL选项总是会启动一个新的段落还原，因此，在部分还原顺序的初始语句中，只能指定PARTIAL一次。当部分还原顺序完成并且数据库在线后，由于余下文件的恢复被推迟，这些文件的状态将变为“恢复已挂起”。

此后，管理员可以安排应用恢复数据库访问，而暂缓其他的文件组的还原。其他文件组还原的时候，不但要还原文件组备份，还要还原日志备份，以将文件组恢复到与数据库一致的点。

在简单恢复模式下，可以被推迟恢复的只有只读文件组。在完整恢复模式或大容量日志恢复模式下，任何包含多个文件组的数据库都可以使用段落还原，并且可以将数据库还原到任何时间点。段落还原的顺序如下：

1. 部分还原顺序。

部分还原顺序会还原主文件组和（可选的）部分辅助文件组。第一个RESTOREDATABASE语句必须执行以下操作：

·        指定PARTIAL选项。它表示段落还原的开始。

·        使用包含主文件组的任何完整数据库备份。常见的做法是通过还原部分备份来启动部分还原顺序。

·        若要还原到特定的时间点，必须在部分还原顺序中指定该时间。还原顺序的每个后续步骤都必须指定相同的时间点。

2. 文件组还原顺序会使其他文件组在线并处于与数据库一致的某个点。

在SQL Server 企业版中，当数据库在线时，可还原并恢复任何离线辅助文件组。如果特定只读文件未损坏且与数据库一致，则该文件无须还原。

如果在文件备份创建之前，只读文件组就已处于只读状态，则该文件组无须应用日志备份，并且文件还原会跳过日志备份的应用过程。如果文件组是读/写文件组，则必须将未中断的日志备份链应用于上一次完整还原或差异还原，文件组才能前进到当前的日志文件。

在下面的示例中，数据库adb将在发生灾难性事件后还原到一台新计算机。该数据库使用完整恢复模式，因此，开始进行还原之前必须先获取数据库的尾日志备份。发生灾难性事件前所有文件组都处于在线状态。文件组B是只读的。必须还原所有辅助文件组，但这些辅助文件组将按重要性顺序进行还原：A最高，其次是C，最后是B。在此示例中，存在4个日志备份，其中包括尾日志备份。

尾日志备份

在还原数据库之前，数据库管理员必须先备份日志尾部。由于数据库已损坏，因此创建尾日志备份要使用NO_TRUNCATE选项：

BACKUP LOG adb TO tailLogBackup WITH NORECOVERY, NO_TRUNCATE

尾日志备份是后面还原顺序中将要应用的最后一个备份。

还原顺序

（1）部分还原主文件组和辅助文件组A。

RESTORE DATABASE adb FILEGROUP='Primary' FROM backup1

   WITH PARTIAL, NORECOVERY

RESTORE DATABASE adb FILEGROUP='A' FROM backup2

   WITH NORECOVERY

RESTORE LOG adb FROM backup3 WITH NORECOVERY

RESTORE LOG adb FROM backup4 WITH NORECOVERY

RESTORE LOG adb FROM backup5 WITH NORECOVERY

RESTORE LOG adb FROM tailLogBackup WITH RECOVERY

此时，主文件组和辅助文件组A处于在线状态。文件组B和C中的所有文件都处于恢复挂起状态，这两个文件组处于离线状态。

步骤1中的最后一条RESTORE LOG语句的消息会指出：由于文件组C不可用，因此涉及此文件组的事务回滚已延迟。数据库可继续执行常规操作，但这些事务会在相应的页面上持有锁，阻止别人的访问和修改。

（2）在线还原文件组C。

在第二个还原顺序中，数据库管理员将还原文件组C，同时要把所有的日志备份再还原一遍：

RESTORE DATABASE adb FILEGROUP='C' FROM backup2a WITHNORECOVERY

RESTORE LOG adb FROM backup3 WITH NORECOVERY

RESTORE LOG adb FROM backup4 WITH NORECOVERY

RESTORE LOG adb FROM backup5 WITH NORECOVERY

RESTORE LOG adb FROM tailLogBackup WITH RECOVERY

（3）此时主文件组及文件组A和C处于在线状态。文件组B中的文件处于恢复挂起状态，该文件组处于离线状态。解析延迟的事务后，日志被截断。

（4）在线还原文件组B。

在第三个还原顺序中，数据库管理员将还原文件组B。文件组B的备份是在该文件组变为只读状态之后进行的，因此，在恢复过程中无须再恢复日志备份。

RESTORE DATABASE adb FILEGROUP='B' FROM backup2b WITHRECOVERY

（5）所有文件组现在都处于在线状态。

虽然段落还原的过程比较复杂，但是通过这样的还原顺序，管理员可以让最重要的文件组A提前交给用户使用。这个在灾难恢复时，其优势就非常大了。所以建议管理员还是要学会使用这种方式，以备不时之需。

还原方案小结

从上面的介绍可以看到，在备份文件相同的前提下，数据库管理员也可以根据具体情况选择不同的还原方案。传统的完全还原方案比较简单，易于操作，但是数据库离线的时间最长。后3种还原操作起来比较复杂，要求管理员对目标数据库的结构有详细了解。但是如果能够正确使用，能够大大缩短数据库离线时间。所以建议管理员可以预先针对自己的数据库，试验一下文件还原、页面还原和段落还原该怎么做，保留正确的脚本，有备无患。

文件还原、页面还原或段落还原的优点

只还原和恢复个别文件或页面（而非整个数据库）的方法具有以下优点：

·        还原少量数据，可以缩短复制和恢复数据的时间。

·        在SQL Server 企业版中，这种还原文件或页面的操作可能会允许数据库中的其他数据在还原操作期间仍保持在线状态，缩短数据库离线时间。

段落还原的方法具有以下优点：

·        能帮助让最重要的数据最先上线。

·        能允许在数据库恢复其他文件组的时候，先前已恢复的文件组仍保持在线状态。

恢复模式（Recovery Mode）和支持的还原操作

数据库的还原操作取决于其所用的恢复模式。表9-4简要说明了每种恢复模式是否支持给定的还原方案以及适用范围。

表9-4  每种恢复模式支持的还原方案以及适用范围

还原操作	完整恢复模式	大容量日志恢复模式	简单恢复模式
数据还原	完整还原（如果日志可用）	某些数据将丢失	自上次完整备份或差异备份后的任何数据将丢失
时点还原	日志备份所涵盖的任何时间	日志备份包含任何大容量日志更改时不允许	不支持
文件还原*	完全支持	不完全支持	仅对只读辅助文件可用
页面还原*	完全支持	不完全支持	无
段落还原*	完全支持	不完全支持	仅对只读辅助文件可用

* 仅适用于SQL Server企业版。

9.3.6 孤立用户故障排除

有时候管理员需要将一个用户数据库从一台SQLServer使用备份和恢复的方式迁移到另一台服务器。数据库恢复以后，系统管理员可以正常看到里面所有的数据。可是原先用户定义的一些其他数据库用户，却可能无法继续在新的服务器上使用。尤其对一些SQLServer账号，哪怕在新服务器上手工创建了它们，还是不能正常地映射到数据库用户上。这是为什么呢？

SQL Server的用户安全管理分两层，整个服务器一层，每个数据库一层。一个用户，在每一层上都有账号，在两个层面上都会分配不同的权力。在服务器层的账号，叫登录账号（Login），可以设置它管理整个SQL Server服务器，开启跟踪，修改SQLServer安全配置，备份所有数据库等。在数据库一层，叫数据库用户（Database User），可以设置它对这个特定的数据库有读写、修改表格结构、存储过程定义等权限。

所以服务器层面的安全，是设置在服务器的登录账号上的。所有登录账号的信息，存放在master数据库里。可以查询master数据库里的sys.server_principals这张视图。每个登录账号都有一个唯一编号SID。对于一个WindowsLogin，它的SID就是Windows用户在域里的SID，SQL Server不会做改变。对于SQLLogin，除了它的名字，SQL Server还会在创建的时候给它随机生成一个SID。一样的名字，每次生成的SID都有可能不一样。

数据库层面有“数据库用户”这个概念。每个数据库内部对象的安全性，例如表格的读写，是否能运行或修改存储过程等，都赋予在数据库用户上，保存在这个数据库内部。可以通过查询sys.database_principals了解用户信息。每个数据库用户除了名字，也有一个唯一的SID。

SQL Server登录账户必须要和某个数据库用户相对应后，才能被数据库接纳。这个对应，就是要使得用户数据库sys.database_principals里的SID和master数据库sys.server_principals里的SID匹配起来。一个登录账户和数据库用户的名字可以不一样，但是SID必须一样。

对于Windows登录账号，如果新的服务器和原先的服务器在同一个域里，那么同样的Windows登录账号会有一样的SID。用户数据库恢复在新服务器以后，只需将Windows登录账号加入SQLServer。因为两个SID值是一样的，sys.database_principals里的数据库用户和master数据库sys.server_principals里的登录账号可以自动匹配，Windows登录账号可以自动继续使用用户数据库。

对于SQL登录账号就有问题了。假设在原先的服务器上有一个SQL登录账号名叫“test”，它对应于用户数据库里的用户“test”。在sys.database_principals里的SID和master数据库sys.server_principals里的SID是相同的。当“test”登录SQLServer后，可以被映射到数据库用户“test”，拥有它上面所赋予的权限。

当用户数据库恢复到新的服务器上后，master数据库sys.server_principals里并没有这个账号。但是用户数据库里还是有“test”这个数据库用户。于是这个用户被“孤立”了。

若要检测孤立用户，可以执行下列Transact-SQL语句：

USE _name>;

GO;

sp_change_users_login @Action='Report';

GO;

输出中列出了当前数据库中未链接到任何SQLServer登录名的用户以及相应的安全标识符（SID）。

这时要首先在SQL Server服务里加入“test”这个SQL登录账号。可是，新服务器为这个SQL登录账号所生成的SID是随机产生的，和原先服务器上的值会不一样。这个“test”服务器登录账户还是不能使用数据库。管理员还要用以下命令重新链接“test”服务器登录账户与数据库用户“test”，将它们的SID改成一致。

USE _name>;

GO

sp_change_users_login@Action='update_one',@UserNamePattern='_user>',@LoginName='_name>';

GO

需要说明的是，sp_change_users_login只能重新链接SQL登录账号。对于数据库用户所对应的是Windows登录账号，如果SID不同，说明域也发生了变化。SQL Server认为这两个Windows登录账号不是同一个。Windows服务器登录账户和数据库用户是不能强制链接在一起的。

如果一个数据库里有成百上千的数据库用户，这样的手工操作会是个费时费力的过程。如果我们能将master数据库与用户数据库一起恢复在新服务器上，两边的SID就还能保持一致，管理员就无须做这些额外的事情。这也是强调要备份系统数据库的原因之一。

 

数据库备份与恢复 之五 系统数据库备份与恢复

分类： SQL Server 数据库管理2012-10-17 16:34 728人阅读 评论(2) 收藏 举报

目录(?)[+]

前面我们讲的备份与恢复，都是集中在用户数据库上。SQLServer还维护着一组系统级数据库（称为“系统数据库”），这些数据库对于服务器实例的运行至关重要。每次进行过系统更新后，都必须备份多个系统数据库。必须备份的系统数据库包括msdb、master和model。如果有任何数据库在服务器实例上使用了复制，则还必须备份distribution系统数据库。备份这些系统数据库，就可以在发生系统故障（例如硬盘丢失）时用来还原和恢复SQLServer系统。

数据库

master数据库记录着SQL Server系统的所有系统级信息，例如登录账户、系统配置设置、端点和凭据以及访问其他数据库服务器所需的信息。master数据库还记录启动服务器实例所需的初始化信息，每个其他数据库的主文件位置。master数据库是SQL Server启动的时候打开的第一个数据库。SQLServer是从这个数据库里找到其他数据库的信息的。如果master数据库有问题，整个SQLServer都无法正常启动。

master数据库本身不大，做一次备份很快。建议要经常做master的完整数据库备份，以充分保护您的数据。如果master数据库已损坏，可以通过还原master数据库的最近完整数据库备份，轻松地修复已损坏的数据库。

如果由于master数据库损坏严重到无法启动服务器实例，又没有备份，则必须重建。重建master数据库将使所有的系统数据库恢复到其原始状态。例如，重建master数据库会删除并重新创建msdb数据库。这将导致丢失所有计划信息以及备份和还原历史记录。所以重建master数据库后，SQLServer就像被重装过一样，所有用户记录都会丢失，用户数据库需要再次附加，SQL Server任务计划都要重建。这是一个很折腾的过程。重建master数据库是个万不得已的选择。

在执行任何语句或系统过程来更改master数据库中的信息以后（例如，更改服务器范围的配置选项以后），应备份master数据库。如果在更改master数据库后没有进行备份，则自上次备份以来的更改都将在还原备份时丢失。

建议不要在master数据库中创建用户对象。但是，如果确实在master数据库中创建了用户对象，则应频繁地执行备份计划，以便能够保护用户数据。

导致master数据库更新并要求进行备份的操作类型包括：

·        创建或删除用户数据库。

用户数据库自动增长以容纳新数据时，master数据库不受影响。

·        添加或删除文件和文件组。

·        添加登录或其他与登录安全相关的操作。

数据库层面的安全操作（如向数据库中添加用户）对master数据库没有影响。

·        更改服务器范围的配置选项或数据库配置选项。

·        创建或删除逻辑备份设备。

·        配置用于分布式查询和远程过程调用（RPC）的服务器，如添加链接服务器或远程登录。

恢复master数据库使用的还是RESTORE指令。可是还原master数据库后，SQL Server实例将自动停止。我们前面讲过，master里面记录了其他所有数据库主文件的地址。SQL Server通过这个地址来寻找这些数据库。如果我们将master数据库恢复到一台新的服务器上，难免文件地址会有所变化。这时SQL Server将找不到其他数据库。需要使用单用户模式启动SQLServer，将master数据库里的信息修改成新的地址。

如果决定以单用户模式重新启动服务器，应首先停止所有SQLServer服务（服务器实例本身除外），并停止所有SQL Server实用工具（如SQLServer代理，报表服务器等）。停止服务和实用工具可以防止它们尝试访问服务器实例。否则单用户启动后，它们会占用这个用户连接，管理员反而连不进去了。

后面会有一个实例，介绍怎么用单用户模式启动SQLServer，以及一整套系统数据库恢复的方法。

9.4.2  model数据库

创建用户数据库时，model数据库是SQL Server使用的模板。model数据库的全部内容（包括数据库选项）都会被复制到新的数据库。所以这个数据库是不推荐做任何修改的。除非有目的地要建一些模板，否则不要去改这个数据库，也不要把任何用户数据放在这个数据库里。

虽然这个数据库里的内容一般不发生变化，但是在SQLServer启动的时候，要使用model数据库的某些设置创建新的tempdb。如果没有tempdb，SQL Server将无法启动。因此model数据库必须始终存在于SQLServer系统中。这个数据库也要有备份。

还原model数据库与对用户数据库执行完整的数据库还原相同。

数据库

SQL Server、SQLServer Management Studio和SQL Server代理使用msdb数据库来存储数据，包括计划信息以及备份与还原历史记录信息。

SQL Server将在msdb数据库中自动维护一份完整的在线备份与还原历史记录。这些信息包括执行备份一方的名称、备份时间和用来存储备份的设备或文件。SQLServer Management Studio利用这些信息提出计划以还原数据库并应用事务日志备份。将会记录有关所有数据库的备份事件，即使它们是由自定义应用程序或第三方工具创建的。例如，如果使用调用SQLServer管理对象（SMO）的MicrosoftVisual Basic应用程序执行备份操作，则事件将记录在msdb系统表、Windows应用程序日志和SQLServer错误日志中。而SQL Server代理使用的所有计划信息，包括计划的定义、执行排程、历史日志等都放在msdb数据库里。

默认情况下，msdb数据库使用简单恢复模式。如果在恢复用户数据库时使用msdb数据库中的备份与还原历史记录信息，建议对msdb数据库使用完整恢复模式，并建议考虑将msdb数据库事务日志放置在容错存储设备中。

还原msdb数据库与对用户数据库执行完整的数据库还原相同。具体方法可参见9.7节的实例。

和资源数据库（ResourceDatabase）

SQL Server还有两个系统数据库比较特别，它们是tempdb和资源数据库。缺了这两个数据库SQL Server都无法正常启动，但是它们又都不能做数据库备份。

tempdb系统数据库是一个全局资源，可供连接到SQL Server实例的所有用户使用。每次启动SQLServer时都会重新创建tempdb数据库，从而在系统启动时总是保持一个干净的数据库副本。连接在断开以后，SQL Server会自动删除临时表和存储过程。因此tempdb数据库中不会有什么内容需要在服务关闭的时候保存下来。只要有干净的model数据库，SQL Server都能够创建出tempdb数据库。Tempdb数据库本身倒不用备份。SQL Server没有提供对tempdb数据库的备份和还原操作。保护好model数据库，就能保证在下次启动时，得到一个好的tempdb数据库。所以对tempdb数据库的保护，转为了对model数据库的保护。

Resource数据库是一个只读数据库，它包含了SQL Server中的所有系统对象。系统对象（例如sys.objects）在物理上存在于Resource数据库中，但在逻辑上，它们出现在每个数据库的sys架构中。Resource数据库不包含任何用户数据或用户元数据。对于同一个版本的SQLServer，它们的Resource数据库应该都是一样的。Resource数据库的物理文件名是mssqlsystemresource.mdf和mssqlsystemresource.ldf。每个SQL Server实例都具有一个（也是唯一的一个）关联的mssqlsystemresource.mdf文件，并且实例间不共享此文件。

因为Resource数据库从来不做修改，所以SQL Server理论上不用备份Resource数据库。但是管理员不但要考虑SQLServer层面的问题，还要考虑到整个系统层面的问题。难免物理磁盘损坏会发生，使得文件出现损坏。所以要通过将mssqlsystemresource.mdf文件作为二进制文件，做基于文件的备份或基于磁盘的备份。恢复时要将SQL Server服务停止，手动还原mssqlsystemresource.mdf的文件备份副本。并且必须谨慎，不同版本的SQL Server Resource数据库会不一样，不要使用过期版本或可能不安全的版本覆盖当前Resource数据库。

表9-5概述了所有的系统数据库。

表9-5  系统数据库备份策略

系统数据库	说　　明	是否支   持备份	恢复   模式	注　　释
master	记录SQL Server系统的所有系统级信息的数据库	支持	简单	必须经常备份master数据库，以便根据业务需要充分保护数据。建议使用定期备份计划，这样在大量更新之后可以补充更多的备份
model	在SQL Server实例上为所有数据库创建的模板	支持	用户 可配置	仅在业务需要时备份model数据库，例如自定义其数据库选项后立即备份。 最佳方法：建议您仅根据需要创建model数据库的完整数据库备份。由于model数据库较小而且很少更改，因此无须备份日志
msdb	SQL Server代理用来安排警报和作业以及记录操作员信息的数据库。msdb数据库还包含历史记录表，例如备份和还原历史记录表	支持	简单 （默认值）	更新时备份msdb数据库
Resource（RDB）	包含SQL Server附带的所有系统对象副本的只读数据库	不支持	—	Resource数据库位于mssqlsy- stemresource.mdf文件中，该文件仅包含代码。因此，SQL Server不能备份Resource数据库
tempdb	用于保存临时或中间结果集的工作空间。每次启动SQL  Server实例时都会重新创建此数据库。服务器实例关闭时，将永久删除tempdb数据库中的所有数据	不支持	简单	无法备份tempdb系统数据库
distribution	只有将服务器配置为复制分发服务器时才存在此数据库。此数据库存储元数据、各种复制的历史记录数据以及用于事务复制的事务	支持	简单	根据您具体的复制配置决定

 

 

数据库备份与恢复 之六 带有FILESTREAM功能的数据库备份和恢复

分类： SQL Server 数据库管理2012-10-17 16:36 604人阅读 评论(1) 收藏 举报

从SQL Server 2008开始，数据库引入了FILESTREAM这个功能。对于BLOB数据，如Images, Video, Word文档等等，可以存放在文件系统中，而不是在数据库文件里。这对数据库备份和恢复有什么影响呢。我们通过一个例子，来检查一下带有FILESTREAM功能的数据库备份和恢复方案。

 

第一步，在数据库服务级别，启动FILESTREAM，我们可以打开数据库的配置管理器，找到数据库服务，并右键点击属性。找到FILESTREAM的设置，启用FILESTREAM。然后执行下面的语句，随后重新启动数据库服务。

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

 

启用FILESTREAM

第二步，把们建一个数据库TESTFSDB。由于FILESTREAM使用一种特殊类型的文件组，所以必须至少为一个文件组指定CONTAINS FILESTREAM子句。

CREATE DATABASE TESTFSDB

GO

 

ALTER DATABASE TESTFSDB

ADD FILEGROUOP [FSGroup] CONTAINS FILESTREAM

GO

 

ALTER DATABASE TESTFSDB

ADD FILE (NAME=N’FSDATA’, FILENAME=N’D:\FSData’) TO FILEGROUPFSGroup

GO

 

执行上面语句后，filestream.hdr文件和$FSLOG文件夹会出现在D:\FSData目录下。Filestream.hdr文件是FILESTREAM容器的头文件。Filestream.hdr文件是重要的系统文件，包含FILESTREAM标头信息。请不要删除或者修改此文件。事实上，这个文件已经被数据库占用，在数据库服务启动状态下，是无法删除此文件的。

 

第三步，创建一个表，表名为FSTable。要使用FILESTREAM功能，我们必须要有一个字段，类型为VARBINARY(MAX)并指定FILESTREAM属性，而且还需要有另外一个字段是UNIQUEIDENTIFIER类型并指定ROWGUIDCOL属性。

use TESTFSDB

go

CREATE TABLE FSTable

(

[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,

[FileName] NVARCHAR(100) ,

[BlobData] VARBINARY(MAX) FILESTREAM NULL

)

 

第四步，插入相应的数据。

INSERT INTO FSTable

SELECT NEWID(), 'File1', * FROM

OPENROWSET(BULK N'D:\temp\Image.jpg', SINGLE_BLOB) AS Document

 

INSERT INTO FSTable

SELECT NEWID(), 'File2', * FROM

OPENROWSET(BULK N'D:\temp\Image.jpg', SINGLE_BLOB) AS Document

数据插入后，我们可以看到，在D:\FSData文件夹子目录9ead5f8f-4e8b-459d-9e9c-205dabbee0ff下，有两个文件生成。我们可以把这两个文件复制出来，重命名为.jpg格式，然后用图形软件打开它。

 

第五步，对数据库做一个全备份和日志备份。

BACKUP DATABASE [TESTFSDB] TO DISK='C:\temp\TESTFSDB.bak'

BACKUP LOG [TESTFSDB] TO DISK='C:\temp\TESTFSDB.bak'

 

第六步，我们关闭数据库服务，并且删除整个D:\FSData文件夹和TESTFSDB数据库。随后，重新启动数据库服务，并依据TESTFSDB.bak文件对数据库TESTFSDB进行恢复。我们会发现，D:\FSData会被重新创建。并且查询表FSTable，我们能得到正确的数据。

 

通过上面的实验，我们可以知道，对于有FILESTREAM功能的数据库，在进行备份和恢复时，会自动包含FILESTREAM数据。并不需要额外做文件级别的备份和恢复。

数据库备份与恢复 之七 应对由于备份损坏导致的还原错误

分类： SQL Server 数据库管理2012-10-18 11:07 4043人阅读 评论(1) 收藏 举报

目录(?)[+]

数据库管理员最大的梦魇，莫过于已经做了备份，但是在想恢复的时候，发现备份文件也是坏的。这将意味着数据库的丢失，后果非常可怕。发生这种情况的原因一般有3个：

·        备份文件和数据库放在同一个（或一组）物理硬盘上。硬盘出故障，备份也保不住。

·        备份介质损坏；或者做的是网络备份，数据在网络传输中发生了损坏。

·        数据库在做完整备份、文件备份或者文件组备份的时候，里面的内容就已经有了损坏。
SQL Server在做数据备份的时候为了节省时间，基本只是很简单地把数据页面拷贝下来，不会做一致性检查的。但是在恢复的时候，需要将数据库恢复（Recover）到事务一致的一个时间点。如果备份中的损坏妨碍了SQL Server的前滚后滚(Redo和Undo)，恢复动作就会遇到错误。

无论何种情况，您都可以：

·        修复硬件错误并重新尝试还原操作。

·        忽略错误，继续还原操作，并在还原完成后修复数据库。

·        放弃还原操作，改用备用还原计划。

                在现实环境里，能够通过重试解决的问题还是比较少的。硬件错误往往会永久地损坏备份文件里的内容。在先前的SQLServer版本里，管理员可能不得不尝试去寻找更早的备份。这往往意味着有很多天的数据丢失，损失是比较大的。

SQL Server 数据库恢复有一个“忽略错误”的功能，在这种为难的时刻可以发挥很大的作用。

忽略错误继续执行操作

CONTINUE_AFTER_ERROR是恢复命令（RESTORE）里面的一个选项。它将使还原操作跳过错误继续进行，并还原SQL Server现在所能还原的所有内容。数据还原结束后，可以应用后续事务日志备份，将数据库恢复。如果日志恢复时遇到错误，SQLServer会在日志中报告，并且不让用户访问和这些事务有关的页面。数据库将在尽可能的情况下联机。所以大部分情况下，数据库整体还是能恢复出来，只是部分数据有可能会丢失。

数据丢失量取决于遇到的错误。例如，一般数据页中的错误只会引起该页进入可疑状态，但数据库恢复还会继续。有问题的页面编号将被写入磁盘并记录到suspect_pages表和错误日志中，提醒管理员在恢复结束后继续处理它们。如果不设置CONTINUE_AFTER_ERROR，SQL Server只要遇到一个页面有问题，整个恢复动作都会停止。

如果错误发生在一些比较关键的地方，比如某个数据文件的文件头信息，那么恢复还是有可能完全失败，数据库无法恢复。所以这个方法只供救急之用。不能保证每次使用的效果。使用WITHCONTINUE_AFTER_ERROR还原数据后，要检查错误日志以了解有关错误的详细信息。

基本的RESTORE语法为：

RESTORE DATABASE database_name

FROM backup_deviceWITH CONTINUE_AFTER_ERROR, [NORECOVERY ]

管理员可以在忽略错误继续执行的还原顺序结束时，使用DBCCCHECKDB修复数据库。要使CHECKDB在使用RESTORECONTINUE_AFTER_ERROR后以最大的一致性运行，建议在DBCC CHECKDB命令中使用WITH TABLOCK选项。在极个别情况下，可能没有足够的信息来修复数据库，CHECKDB也没办法修好数据库，数据丢失将不可避免。不是说，有了RESTORE CONTINUE_AFTER_ERROR，备份坏掉也没关系的。

建立备用（Standby）服务器

CONTINUE_AFTER_ERROR只不过是命令SQLServer跳过一切它能够跳过的错误，将所有还能读出来的数据恢复出来，从而最大程度地挽回数据。但是有些对数据一致性要求比较高的系统，比如银行账户系统，用户可不接受“部分”数据恢复。对他们来讲，数据不一致可能就意味着钱已经从一个账户转走，但是没有进入另一个账户，这是不可接受的。所以他们宁可将数据库恢复到昨天的状态，把今天所有的操作重做一遍。

对于这样的系统，在建立备份和选择恢复策略的时候，就要考虑到最坏的情况，预先想好方案，将损失降到最低。

事先预备一台备用机，将做好的备份使用LogShipping或者其他类似的机制在备用服务器上预先恢复好，是一个值得推荐的方法。这样做的好处有：

（1）比起物理镜像之类的技术，这种方案比较经济。备用服务器的硬件要求不高，只要硬盘足够大。

（2）虽然SQL Server提供了若干备份校验机制，但是确保备份完整可靠的唯一办法是真正地去恢复它。

（3）提前恢复备份，使得在真正灾难发生时，只需要恢复最后一个日志备份即可，而不需要在火烧眉毛的时候，去等那个漫长的完整备份恢复，可以大大节约灾难恢复时间。

（4）备用机上的数据库虽然不能修改，但是可以使用STANDBY参数将数据库恢复到只读模式。可以将一些报表查询工作转移到备用机上，减轻生产服务器的负担。

总之，数据安全非常重要，灾难恢复时间要求很短的数据库，如果没有镜像技术的保障，备用服务器是非常必要的。