SQL灾难恢复宝典.docx

1、SQL灾难恢复宝典SQL SERVER的灾难恢复（转）1 制定计划，备份和恢复策略的计划编制过程是工作中至关重要的部分，不正确的决定就如同服务器丢失数据一样迅速地结束你的事业。第一步实际上是一个商业问题，可接受的服务器故障时间有多长？判定正常运行时间的常用标准是所谓的“ 9”。这种分级机制限定了一年内可以接受的服务器故障时间，表 7-1 列出了如何将其转换为年故障时间。表 7-1 可用性分级分 级正常运行时间百分比每年的故障时间1 个 990%36.5 天2 个 999%3.7 天3 个 999.9%8.8 小时4 个 999.99%53 分钟5 个 999.999%5 分钟如果没有集群解决方

2、案，任何 3 个 9 以上的无故障率几乎不可能实现。应切记数据库越大，恢复该数据库所花费的时间就越长。你不是没有听说过，花 18 小时恢复一个大型数据库并于隔日完成一致性检查。7.1.1 第三方备份程序许多第三方供应商生产优秀的数据库备份和恢复软件，尽管其中某些软件包开始备份 SQL Server，但是它们在试图备份数据库时将接收到一个共享错误。惟一的解决办法是在备份系统前停止服务，或者购买插件代理备份已经打开的文件或 SQL Server在线数据库。这类代理将提高软件的价格，大约每一台服务器增加$800。虽然笔者通过 SQL Server 代理执行备份的成功率已经较高，但是笔者发现这个成功率

3、仍然低于使用 SQL Server 工具执行备份的成功率。说明： 许多备份软件包在备份过程前后均可执行命令行程序(例如 net start)。笔者首先使用这些软件包完成其擅长的工作，即备份文件，而不是数据库。笔者喜欢通过 SQL Server 代理作业备份数据库，然后使用第三方软件获取备份文件。如果公司坚持购买第三方代理备份 SQL Server 数据库，那么为了以防万一，还应使用 SQL Server 工具备份数据库。警告： 宣称能够备份打开文件的程序可以备份这些文件，但却将 SQL Server 置于不可靠的状态，这是因为它们没有适当备份未完成的事务。7.2 备份类型本节讨论可用于 SQL

4、 Server 的备份类型，说明如何使用 GUI 和 T-SQL。笔者始终偏好使用 T-SQL 编写备份脚本，因为这样做可以复制过程，并且提供更多选择。说明： 必须是 sysadmin、db_owner 或 db_backupoperator 角色的成员才可以创建数据库备份。此外，还需要备份介质的口令。7.2.1 在 Enterprise Manager 中全备份数据库全备份最容易执行，而且这是惟一一种必须在主服务器上完成的备份。数据库全备份保存数据库对象和关系的完整快照，同时备份用户及其权限。当开始全备份时，将产生以下事件：1. 数据库备份开始并记录当前时间。2. 顺序备份每个数据文件的数据

5、页。3. 自备份步骤 1 以来发生的事务。举一个简单的例子。首先使用完全数据库备份备份整个数据库。假设备份开始于凌晨 2:00。同时，电子商务系统中的事务依然继续进行，而且销售情况仍然被记录。当数据页的备份于凌晨 4:00 结束时，这两个小时内的事务也被备份。这保证了备份与结束时间一致。警告： 完全备份将备份用户，但不备份登录，即除非备份主数据库。一旦将数据库恢复到不同的服务器上，用户和登录标识将不同步，本章稍后将介绍如何解决这个问题。为了在 Enterprise Manager 中备份数据库，右击数据库后选择 All Tasks | Backup Database。在 SQL Server

6、Backup(SQL Server 备份)窗口中(如图 7-1 所示)，大部分重要的设置已经自动设定。该窗口可用的选项因数据库所采用的恢复模式而异。 大部分选项一目了然，无须解释。Backup 选项可供选择所需执行的备份类型。在本例中，笔者执行完全备份。如果安装了磁带设备，则可以选择备份到磁带或备份到磁盘。如果没有磁带设备，则只有磁盘选项可用。 如果第一次对数据库执行备份，则 Back To(备份到)框中不会有任何备份设备或文件。如果是这种情况，则必须添加备份设备或文件。备份设备应是一个可保存一定数量备份的预定义备份位置，这类似于 SQL Server 6.5 中的数据设备，在其中可将很多数据

7、库存储在一个设备内。数据库文件也可以保存多个备份，但是直到真正执行备份时，才创建文件。若添加新的备份设备或数据库文件，则单击 Add 按钮，然后在 Backup Device(备份设备)选项内指定位置。 通过单击 Add 按钮后指定位置，可以继续添加额外的备份设备或文件。如果添加不止一个文件或设备，则信息均匀遍布在备份文件集内。通过将备份分布给几个文件或设备，可加速大数据库的备份和恢复过程。在将大数据库备份至磁带时，这尤其有用。提示：如果厌倦了更改备份目录，那么可通过修改 HKEY_ LOCAL_MACHINESoftwareMicrosoftMSSQLServer注册表键内的 BackupD

8、irectory 数据项更改缺省的备份目录。在 Overwrite(覆盖)区域，Append To Media(添加到介质)选项将备份添加至文件结尾，而 Overwrite Existing Media(覆盖现有介质)选项删除旧备份后创建新备份。该选项卡中的最后一个选项可用于创建执行备份的作业。使用 SQL Server Agent 可以安排作业备份数据库。通过单击“ U”按钮，可采用以下选项安排备份： Start Automatically When SQL Server Agent Starts(当 SQLServerAgent 启动时自动启动) 这通常设置在 SQL Server 第一次

9、启动时执行。 Start Whenever CPU(s) Become Idle 在 CPU(s)达到在 SQL Server(当 CPU 空闲时启动) 当 CPU 达到 SQL Server Agent 属性中设定的某个级别时，启动备份。第 3章中讨论过这个属性。 One Time(按时) 备份将按规定时间执行。当在非高峰期执行大型作业时，这很方便。 Recurring(重现) 可以将备份的执行间隔配置为小到 1 分钟或者大到 99 个月。说明： 如果使用备份文件，则在备份真正执行之前不保留存储空间。如果选择 Append To Media 选项，那么备份文件将迅速膨胀。在 Option 选

10、项卡(如图 7-2 所示)中可以配置一些更高级的备份设置： Verify Backup Upon Completion(结束时验证备份) 这个选项将备份与处于运行状态的数据库进行比较。如果选择该选项，则当此操作执行时，服务器的性能将大幅下降，但是增加了发现坏备份的可能性，从而避免出现问题。 Eject Tape After Backup(备份之后退出磁带) 该选项在完成备份后弹出介质。如果在一个磁带上存储多重备份，选择该选项就是不明智的举动。如果需要在备份完成时给出直观的提示，该选项很方便。 Remove Inactive Entries From Transaction Log(从事务日志中

11、删除非活动项) 该选项只有在执行事务日志备份时才可用，并且在备份完成后截断事务日志。该选项缺省为选择，如果不希望事务日志备份文件越积越多，则应取消选择该选项。 Check Media Set Name and Backup Set Expiration(检查介质集名称和备份集失效日期) 该选项在覆盖文件之前确认介质的名称和介质的失效日期。试图覆盖没有失效的备份介质将导致如图 7-3 所示的错误。如果选择该选项，则应指定介质集的名称；如果没有指明介质集合的名称，则此处保持空白。 Backup Set Will Expire(备份集将过期) 在该选项下面，指定备份在被覆盖之前将保留的时间。该选项允

12、许忽略在 Database Setting(数据库设置)选项卡中的 Server Properties(服务器属性)对话框中设置的缺省保留时间。 Initialize And Label Media(初始化介质并加卷标) 该选项删除备份文件或设备内所有现存备份的内容。也可以使用 Media Set Name 和 Description 选项定位备份。只有在 General 卡中选择 Overwrite 按钮，才可以使用该选项。 提示：可以在 SQL Server Properties 对话框中设置缺省的备份过期时间，在 Database Setting 选项卡中设置 Default Backu

13、p Media Retention Day(s)选项。缺省情况下，SQL Server 配置为无限期保留备份。自 救 如果尝试使用不完整的多文件备份介质集来备份数据库，则收到以下错误： 解决此错误的惟一办法就是寻找额外的文件或设备，或者在General 选项卡中选择 Overwrite 选项并且在 Options 选项卡中选择Initialize 选项。提示：如果将数据库备份到网络驱动器，则可以使用完整的 UNC路 径 (ComputerNameShareNamePathFilename.bak) ，SQL Server 无法看见映射驱动器，同时应确认启动 SQL Server 和 SQL S

14、erver 代理的账户可以看到这个网络共享。警告： 存在备份崩溃数据库的可能，在执行备份命令之前，应使用 DBCC CHECKDB 检查数据库是否完好。7.2.2 在 T-SQL 中全备份使用 T-SQL 备份数据库可以提供 Enterprise Manager 不具备的选项，使用此方法备份数据库可以执行 BACKUP DATABASE 命令，其语法如下所示：BACKUP DATABASE database_name | database_name_var TO ,.n WITH BLOCKSIZE = blocksize | blocksize_variable , DESCRIPTION

15、= text | text_variable , DIFFERENTIAL , EXPIREDATE = date | date_var | RETAINDAYS = days | days_var , PASSWORD = password | password_variable , FORMAT | NOFORMAT , INIT | NOINIT , MEDIADESCRIPTION = text | text_variable , MEDIANAME = media_name | media_name_variable , MEDIAPASSWORD = mediapassword |

16、 mediapassword_variable , NAME = backup_set_name | backup_set_name_var , NOSKIP | SKIP , NOREWIND | REWIND , NOUNLOAD | UNLOAD , RESTART , STATS = percentage 详情请见联机帮助提示：在 Enterprise Manager 中，不能将数据库备份到可移动式磁盘驱动器(比如 Zip 驱动器)中。但是只要驱动器已格式化，即可使用 T-SQL 写驱动器。7.2.3 使用 T-SQL Server 配置设备为了添加备份设备，使用名为 sp_addum

17、pdevice 的系统存储过程。例如，下面的语句在 C 驱动器上添加一个名为 NorthwindDevice 的备份设备：EXEC sp_addumpdevice disk, NorthwindDevice, c:Program filesMicrosoft SQL ServerMSSQLBACKUP NorthwindBakDevice.dat 备份到数据库设备很简单，所需要做的仅仅是将 DISK 选项换为设备名称：BACKUP DATABASE Northwind TO NorthwindDevice 该命令将输出以下结果：Processed 352 pages for database

18、Northwind, file Northwind on file 1. Processed 1 pages for database Northwind, file Northwind_log on file 1. BACKUP DATABASE successfully processed 353 pages in 1.348 seconds (2.139MB/sec).7.2.4 差异备份差异备份只记录自最后一次全备份以来改变的数据。由于这种备份执行起来比全备份小许多，而且快很多，所以可以更加频繁地执行这种备份，并且将其加入备份工具库。为了在 Enterprise Manager 中执行

19、差异备份，只需在创建备份时选择 Differential选项。 为了在 T-SQL 中执行差异备份，使用 BACKUP 命令，但是应如下所示选择DIFFERENTIAL 选项：BACKUP DATABASE Northwind TO DISK = NC:Program FilesMicrosoft SQL Server ServerMSSQLBACKUPNorthwindDiff.bak WITH NOINIT, NOUNLOAD, DIFFERENTIAL, NAME= NNorthwind backup, STATS=10, NOFORMAT这个命令的输出的结果类似全备份的输出结果。如果数

20、据库没有太多的变化，则应缩短 STAT 参数作为补偿。7.2.5 事务日志备份事务日志备份类似增量 Windows 备份，这种备份类型备份数据库中已完成的事务，并且可以选择清除此后的事务日志。事务日志备份可以实现真正灵活的时间点恢复，创建事务日志备份只需在 Enterprise Manager 中选择 Transaction Log(事务日志)选项。 为了执行事务日志备份，必须保证数据库处于 Full 或 Bulk-Logged Recovery 模式，本章后面的章节将讨论如何调整为这些恢复模式。如果数据库未处于这些模式中的一种，那么在 Backup(备份)对话框中 Transaction L

21、og 选项不可用。如果试图备份处于Simple Recovery 模式的数据库，将收到以下错误信息：Server: Msg 4208, Level 16, State 1, Line 1 The Statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTERDATABASE. Server: Msg 3013, level 16, State 1, line 1 BACKUP LOG is te

22、rminating abnormally.7.2.6 在 T-SQL Server 中备份事务日志为了在 T-SQL 中执行事务日志备份，应使用 BACKUP LOG 命令，而非 BACKUP DATABASE 命令，其余的语法类似于 BACKUP DATABASE 命令。BACKUP LOG 命令有两个新增选项： NO_LOG | TRUNCATE_ONLY 这两个同义选项在不执行备份时截断事务日志。 NO_TRUNCATE 该选项允许备份事务日志，而且完成后不截断备份日志。这便于备份由于物理驱动器失败而导致数据库损坏的事务日志。千万不要指定该选项而任由日志长时间增长。 为了备份 North

23、wind 数据库的事务日志，执行以下语句：BACKUP LOG Northwind TO DISK = NC:Program FilesMicrosoft SQL ServerMSSQLBACKUP NorthwindTran.bak WITH NOINIT, NOUNLOAD, NAME= NNorthwind backup, STATS=10, NOFORMAT输出以下结果：80 percent backed up.100 percent backed up.Processed 10 pages for database Northwind, file Northwind _log on

24、file 5. BACKUP LOG successfully processed 10 pages in 0.089 seconds (0.920MB/sec).如果事务日志中没有需要备份的数据，将会收到以下消息：There is no current database backup. This log backup cannot be used to roll forward a preceding database backup. 100 percent backed up. Processed 1 pages for database Northwind, file Northwind

25、_log on file 3. BACKUP LOG successfully processed 1 pages in 0.137 seconds (0.007MB/sec).如果只希望截断事务日志，执行以下语句：BACKUP LOG Northwind WITH NO_LOG7.2.7 备份单独的文件或文件组既可以备份单个文件，也可以备份文件组。这对 VLDB(特大型数据库)极其适合，因为 VLDB 所具有的多个文件组需要数小时方可完成备份。这种方式允许将备份分摊在各个时间段，从而避免服务器的性能下降。应切记的主要事项是，当执行文件或文件组备份时监视数据库的设计。在恢复一个文件组时，肯定不

26、希望该文件组中存在Customers 表，但是不存在 Customer_Details 表。如果这种情况发生，那么数据将不同步。 为了在 Enterprise Manager 中备份文件或文件组，只需在 Database Backup(数据库备份)窗口中选择 File And Filegroup(文件和文件组)选项，并选择待备份的文件或文件组。为了使用 T-SQL 备份文件组，只需加上 FILEGROUP 参数并指定文件组的名字，如下所示：BACKUP DATABASE Northwind FILEGROUP = NPRIMSARY TO DISK = NC:Program FilesMicr

27、osoft SQL ServerMSSQL BACKUPNorthwind.bak WITH NOINIT, NAME= NPRIMARY FileGroup Backup, NOSKIP, STATS = 107.2.8 backupset 表在 msdb 数据库中，可以用来确定备份信息的表是 backupset 表。backupset 表提供备份开始和结束时间的详细信息，以及何时执行最后一次备份和备份哪个数据库，同时提供备份执行者和备份类型的信息。使用下面的查询获取 backupset 表的简略信息，包括执行备份花费的时间：SELECT Database_name, position, b

28、ackup_size, DATEIFF(second, backup_start_date, backup_finish_date) as Time, backup_finish_date, as last_Backup FROM backupset该查询输出以下结果：database_name position backup_size Time Last_Backup- - - -Ecommerce 1 870912 1 2001-02-12 16:23:45.000Admin911 1 1066496 1 2001-03-04 15:28:47.000Admin911 2 1064960

29、1 2001-03-04 15:30:58.000Northwind 1 2968064 2 2001-03-04 15:35:00.000Northwind 1 2985984 3 2001-03-04 16:09:01.000Northwind 2 2982912 3 2001-03-04 16:14:20.000Northwind 3 2982912 3 2001-03-04 16:50:50.000(7 row(s) affected)7.2.9 优化备份和恢复的性能一个大型数据库完成备份需要花费数小时，使用下面的技巧可以缩短备份和恢复数据库的时间： 在服务器本地备份文件，随后将其移到

30、分离的网络或磁带驱动器上，速度将比较快。 在非高峰时段执行完全备份。在某些机器上备份的 I/O 操作相当密集，并且使服务器的性能显著降低。 将文件备份到 RAID 1 或 10 驱动器，这是因为在备份期间将出现大量的写操作。 在备份大型数据库时，使用存储局域网络(SAN)驱动器。 并行地将大型数据库备份到多个备份驱动器，SQL Server 为每个备份设备创建一个备份。 如果备份到磁带驱动器，尝试备份到并行磁带驱动器。7.3 数据库恢复模式SQL Server 2000 新增的优秀功能之一是数据库恢复模式，这种模式允许用户控制事 务 日 志 备 份 的 大 小 及 其 速 度 ， 并 可 以 简 化 灾 难 恢 复 。 旧 的 选 项 ， 例 如 select into/bulkcopy 和 trunc.log on checkpoint 选项已经被 Simple、Full 和 Bulked-logged Recovery 模式取代，每种模式具有不同程度的可接受恢复时间和速度。说明： select into/bulkcopy 和 trunc.log on checkpoint 选项为了向后兼容性而保留在 SQL Server 2000 中，这些选项只能通过 T-SQL 命令访问，例如 sp_dboption 存储过程。如