SQL灾难恢复宝典.docx

SQL灾难恢复宝典.docx

《SQL灾难恢复宝典.docx》由会员分享，可在线阅读，更多相关《SQL灾难恢复宝典.docx（19页珍藏版）》请在冰豆网上搜索。

SQL灾难恢复宝典

SQLSERVER的灾难恢复（转）

1制定计划 ，备份和恢复策略的计划编制过程是工作中至关重要的部分，不正确的决定就如同服务器丢失数据一样迅速地结束你的事业。

第一步实际上是一个商业问题，可接受的服务器故障时间有多长？

判定正常运行时间的常用标准是所谓的“9”。

这种分级机制限定了一年内可以接受的服务器故障时间，表7-1列出了如何将其转换为年故障时间。

表7-1可用性分级 

分级

正常运行时间百分比

每年的故障时间

1个9

 90%

 36.5天 

2个9

 99%

 3.7天

3个9

 99.9%

 8.8小时 

4个9

 99.99%

 53分钟

5个9 

 99.999%

 5分钟

如果没有集群解决方案，任何3个9以上的无故障率几乎不可能实现。

应切记数据库越大，恢复该数据库所花费的时间就越长。

你不是没有听说过，花18小时恢复一个大型数据库并于隔日完成一致性检查。

7.1.1第三方备份程序 

许多第三方供应商生产优秀的数据库备份和恢复软件，尽管其中某些软件包开始备份SQLServer，但是它们在试图备份数据库时将接收到一个共享错误。

惟一的解决办法是在备份系统前停止服务，或者购买插件代理备份已经打开的文件或SQLServer在线数据库。

这类代理将提高软件的价格，大约每一台服务器增加$800。

虽然笔者通过SQLServer代理执行备份的成功率已经较高，但是笔者发现这个成功率仍然低于使用SQLServer工具执行备份的成功率。

说明：

许多备份软件包在备份过程前后均可执行命令行程序（例如netstart）。

笔者首先使用这些软件包完成其擅长的工作，即备份文件，而不是数据库。

笔者喜欢通过SQLServer代理作业备份数据库，然后使用第三方软件获取备份文件。

如果公司坚持购买第三方代理备份SQLServer数据库，那么为了以防万一，还应使用SQLServer工具备份数据库。

警告：

宣称能够备份打开文件的程序可以备份这些文件，但却将SQLServer置于不可靠的状态，这是因为它们没有适当备份未完成的事务。

7.2备份类型 

本节讨论可用于SQLServer的备份类型，说明如何使用GUI和T-SQL。

笔者始终偏好使用T-SQL编写备份脚本，因为这样做可以复制过程，并且提供更多选择。

说明：

必须是sysadmin、db_owner或db_backupoperator角色的成员才可以创建数据库备份。

此外，还需要备份介质的口令。

7.2.1在EnterpriseManager中全备份 

数据库全备份最容易执行，而且这是惟一一种必须在主服务器上完成的备份。

数据库全备份保存数据库对象和关系的完整快照，同时备份用户及其权限。

当开始全备份时，将产生以下事件：

1.数据库备份开始并记录当前时间。

2.顺序备份每个数据文件的数据页。

3.自备份步骤1以来发生的事务。

举一个简单的例子。

首先使用完全数据库备份备份整个数据库。

假设备份开始于凌晨2:

00。

同时，电子商务系统中的事务依然继续进行，而且销售情况仍然被记录。

当数据页的备份于凌晨4:

00结束时，这两个小时内的事务也被备份。

这保证了备份与结束时间一致。

警告：

完全备份将备份用户，但不备份登录，即除非备份主数据库。

一旦将数据库恢复到不同的服务器上，用户和登录标识将不同步，本章稍后将介绍如何解决这个问题。

为了在EnterpriseManager中备份数据库，右击数据库后选择AllTasks|BackupDatabase。

在SQLServerBackup（SQLServer备份）窗口中（如图7-1所示），大部分重要的设置已经自动设定。

该窗口可用的选项因数据库所采用的恢复模式而异。

大部分选项一目了然，无须解释。

Backup选项可供选择所需执行的备份类型。

在本例中，笔者执行完全备份。

如果安装了磁带设备，则可以选择备份到磁带或备份到磁盘。

如果没有磁带设备，则只有磁盘选项可用。

如果第一次对数据库执行备份，则BackTo（备份到）框中不会有任何备份设备或文件。

如果是这种情况，则必须添加备份设备或文件。

备份设备应是一个可保存一定数量备份的预定义备份位置，这类似于SQLServer6.5中的数据设备，在其中可将很多数据库存储在一个设备内。

数据库文件也可以保存多个备份，但是直到真正执行备份时，才创建文件。

若添加新的备份设备或数据库文件，则单击Add按钮，然后在BackupDevice（备份设备）选项内指定位置。

通过单击Add按钮后指定位置，可以继续添加额外的备份设备或文件。

如果添加不止一个文件或设备，则信息均匀遍布在备份文件集内。

通过将备份分布给几个文件或设备，可加速大数据库的备份和恢复过程。

在将大数据库备份至磁带时，这尤其有用。

提示：

如果厌倦了更改备份目录，那么可通过修改HKEY_LOCAL_MACHINESoftwareMicrosoftMSSQLServer注册表键内的BackupDirectory数据项更改缺省的备份目录。

在Overwrite（覆盖）区域，AppendToMedia（添加到介质）选项将备份添加至文件结尾，而OverwriteExistingMedia（覆盖现有介质）选项删除旧备份后创建新备份。

该选项卡中的最后一个选项可用于创建执行备份的作业。

使用SQLServerAgent可以安排作业备份数据库。

通过单击“U”按钮，可采用以下选项安排备份：

·StartAutomaticallyWhenSQLServerAgentStarts（当SQLServerAgent启动时自动启动）这通常设置在SQLServer第一次启动时执行。

·StartWheneverCPU（s）BecomeIdle在CPU（s）达到在SQLServer（当CPU空闲时启动）当CPU达到SQLServerAgent属性中设定的某个级别时，启动备份。

第3章中讨论过这个属性。

·OneTime（按时）备份将按规定时间执行。

当在非高峰期执行大型作业时，这很方便。

·Recurring（重现）可以将备份的执行间隔配置为小到1分钟或者大到99个月。

说明：

如果使用备份文件，则在备份真正执行之前不保留存储空间。

如果选择AppendToMedia选项，那么备份文件将迅速膨胀。

在Option选项卡（如图7-2所示）中可以配置一些更高级的备份设置：

·VerifyBackupUponCompletion（结束时验证备份）这个选项将备份与处于运行状态的数据库进行比较。

如果选择该选项，则当此操作执行时，服务器的性能将大幅下降，但是增加了发现坏备份的可能性，从而避免出现问题。

·EjectTapeAfterBackup（备份之后退出磁带）该选项在完成备份后弹出介质。

如果在一个磁带上存储多重备份，选择该选项就是不明智的举动。

如果需要在备份完成时给出直观的提示，该选项很方便。

·RemoveInactiveEntriesFromTransactionLog（从事务日志中删除非活动项）该选项只有在执行事务日志备份时才可用，并且在备份完成后截断事务日志。

该选项缺省为选择，如果不希望事务日志备份文件越积越多，则应取消选择该选项。

·CheckMediaSetNameandBackupSetExpiration（检查介质集名称和备份集失效日期）该选项在覆盖文件之前确认介质的名称和介质的失效日期。

试图覆盖没有失效的备份介质将导致如图7-3所示的错误。

如果选择该选项，则应指定介质集的名称；如果没有指明介质集合的名称，则此处保持空白。

·BackupSetWillExpire（备份集将过期）在该选项下面，指定备份在被覆盖之前将保留的时间。

该选项允许忽略在DatabaseSetting（数据库设置）选项卡中的ServerProperties（服务器属性）对话框中设置的缺省保留时间。

·InitializeAndLabelMedia（初始化介质并加卷标）该选项删除备份文件或设备内所有现存备份的内容。

也可以使用MediaSetName和Description选项定位备份。

只有在General卡中选择Overwrite按钮，才可以使用该选项。

提示：

可以在SQLServerProperties对话框中设置缺省的备份过期时间，在DatabaseSetting选项卡中设置DefaultBackupMediaRetentionDay（s）选项。

缺省情况下，SQLServer配置为无限期保留备份。

自救 

    如果尝试使用不完整的多文件备份介质集来备份数据库，则收到以下错误：

解决此错误的惟一办法就是寻找额外的文件或设备，或者在General选项卡中选择Overwrite选项并且在Options选项卡中选择Initialize选项。

提示：

如果将数据库备份到网络驱动器，则可以使用完整的UNC路径（ComputerNameShareNamePathFilename.bak），SQLServer无法看见映射驱动器，同时应确认启动SQLServer和SQLServer代理的账户可以看到这个网络共享。

警告：

     存在备份崩溃数据库的可能，在执行备份命令之前，应使用DBCCCHECKDB检查数据库是否完好。

7.2.2在T-SQL中全备份 

使用T-SQL备份数据库可以提供EnterpriseManager不具备的选项，使用此方法备份数据库可以执行BACKUPDATABASE命令，其语法如下所示：

BACKUPDATABASE{database_name|@database_name_var} 

TO[,...n] 

[WITH

[BLOCKSIZE={blocksize|@blocksize_variable}]

[[,]DESCRIPTION={'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|@mediapassword_variable}]

[[,]NAME={backup_set_name|@backup_set_name_var}]

[[,]{NOSKIP|SKIP}]

[[,]{NOREWIND|REWIND}]

[[,]{NOUNLOAD|UNLOAD}]

[[,]RESTART]

[[,]STATS[=percentage]] 

]

详情请见联机帮助

提示：

在EnterpriseManager中，不能将数据库备份到可移动式磁盘驱动器（比如Zip驱动器）中。

但是只要驱动器已格式化，即可使用T-SQL写驱动器。

7.2.3使用T-SQLServer配置设备 

为了添加备份设备，使用名为sp_addumpdevice的系统存储过程。

例如，下面的语句在C驱动器上添加一个名为NorthwindDevice的备份设备：

EXECsp_addumpdevice’disk’,’NorthwindDevice’,’c:

ProgramfilesMicrosoftSQLServerMSSQLBACKUPNorthwindBakDevice.dat’备份到数据库设备很简单，所需要做的仅仅是将DISK选项换为设备名称：

BACKUPDATABASENorthwindTONorthwindDevice该命令将输出以下结果：

Processed352pagesfordatabase’Northwind’,file’Northwind’onfile1.Processed1pagesfordatabase’Northwind’,file’Northwind_log’onfile1.BACKUPDATABASEsuccessfullyprocessed353pagesin1.348seconds（2.139MB/sec）. 

7.2.4差异备份 

差异备份只记录自最后一次全备份以来改变的数据。

由于这种备份执行起来比全备份小许多，而且快很多，所以可以更加频繁地执行这种备份，并且将其加入备份工具库。

为了在EnterpriseManager中执行差异备份，只需在创建备份时选择Differential选项。

为了在T-SQL中执行差异备份，使用BACKUP命令，但是应如下所示选择DIFFERENTIAL选项：

BACKUPDATABASENorthwindTODISK=N’C:

ProgramFilesMicrosoftSQLServerServerMSSQLBACKUPNorthwindDiff.bak’WITHNOINIT,NOUNLOAD,DIFFERENTIAL,NAME=N’Northwindbackup’,STATS=10,NOFORMAT 

这个命令的输出的结果类似全备份的输出结果。

如果数据库没有太多的变化，则应缩短STAT参数作为补偿。

7.2.5事务日志备份 

事务日志备份类似增量Windows备份，这种备份类型备份数据库中已完成的事务，并且可以选择清除此后的事务日志。

事务日志备份可以实现真正灵活的时间点恢复，创建事务日志备份只需在EnterpriseManager中选择TransactionLog（事务日志）选项。

为了执行事务日志备份，必须保证数据库处于Full或Bulk-LoggedRecovery模式，本章后面的章节将讨论如何调整为这些恢复模式。

如果数据库未处于这些模式中的一种，那么在Backup（备份）对话框中TransactionLog选项不可用。

如果试图备份处于SimpleRecovery模式的数据库，将收到以下错误信息：

Server:

Msg4208,Level16,State1,Line1TheStatementBACKUPLOGisnotallowedwhiletherecoverymodelisSIMPLE.UseBACKUPDATABASEorchangetherecoverymodelusingALTERDATABASE.Server:

Msg3013,level16,State1,line1BACKUPLOGisterminatingabnormally. 

7.2.6在T-SQLServer中备份事务日志 

为了在T-SQL中执行事务日志备份，应使用BACKUPLOG命令，而非BACKUPDATABASE命令，其余的语法类似于BACKUPDATABASE命令。

BACKUPLOG命令有两个新增选项：

·NO_LOG|TRUNCATE_ONLY这两个同义选项在不执行备份时截断事务日志。

·NO_TRUNCATE该选项允许备份事务日志，而且完成后不截断备份日志。

这便于备份由于物理驱动器失败而导致数据库损坏的事务日志。

千万不要指定该选项而任由日志长时间增长。

为了备份Northwind数据库的事务日志，执行以下语句：

BACKUPLOGNorthwindTODISK=N’C:

ProgramFilesMicrosoftSQLServerMSSQLBACKUPNorthwindTran.bak’WITHNOINIT,NOUNLOAD,NAME=N’Northwindbackup’,STATS=10,NOFORMAT 

输出以下结果：

80percentbackedup. 

100percentbackedup. 

Processed10pagesfordatabase’Northwind’,file’Northwind_log’onfile5.BACKUPLOGsuccessfullyprocessed10pagesin0.089seconds（0.920MB/sec）. 

如果事务日志中没有需要备份的数据，将会收到以下消息：

Thereisnocurrentdatabasebackup.Thislogbackupcannotbeusedtorollforwardaprecedingdatabasebackup.100percentbackedup.Processed1pagesfordatabase’Northwind’,file’Northwind_log’onfile3.BACKUPLOGsuccessfullyprocessed1pagesin0.137seconds（0.007MB/sec）. 

如果只希望截断事务日志，执行以下语句：

BACKUPLOGNorthwindWITHNO_LOG

7.2.7备份单独的文件或文件组 

既可以备份单个文件，也可以备份文件组。

这对VLDB（特大型数据库）极其适合，因为VLDB所具有的多个文件组需要数小时方可完成备份。

这种方式允许将备份分摊在各个时间段，从而避免服务器的性能下降。

应切记的主要事项是，当执行文件或文件组备份时监视数据库的设计。

在恢复一个文件组时，肯定不希望该文件组中存在Customers表，但是不存在Customer_Details表。

如果这种情况发生，那么数据将不同步。

为了在EnterpriseManager中备份文件或文件组，只需在DatabaseBackup（数据库备份）窗口中选择FileAndFilegroup（文件和文件组）选项，并选择待备份的文件或文件组。

为了使用T-SQL备份文件组，只需加上FILEGROUP参数并指定文件组的名字，如下所示：

BACKUPDATABASE[Northwind]FILEGROUP=N’PRIMSARY’TODISK=N’C:

ProgramFilesMicrosoftSQLServerMSSQLBACKUPNorthwind.bak’WITHNOINIT,NAME=N’PRIMARYFileGroupBackup’,NOSKIP,STATS=10 

7.2.8backupset表 

在msdb数据库中，可以用来确定备份信息的表是backupset表。

backupset表提供备份开始和结束时间的详细信息，以及何时执行最后一次备份和备份哪个数据库，同时提供备份执行者和备份类型的信息。

使用下面的查询获取backupset表的简略信息，包括执行备份花费的时间：

SELECTDatabase_name,position,backup_size,DATEIFF（second,backup_start_date,backup_finish_date）asTime,backup_finish_date,aslast_BackupFROMbackupset 

该查询输出以下结果：

database_namepositionbackup_sizeTimeLast_Backup 

-------------------------------------------------- 

Ecommerce187091212001-02-1216:

23:

45.000 

Admin9111106649612001-03-0415:

28:

47.000 

Admin9112106496012001-03-0415:

30:

58.000 

Northwind1296806422001-03-0415:

35:

00.000 

Northwind1298598432001-03-0416:

09:

01.000 

Northwind2298291232001-03-0416:

14:

20.000 

Northwind3298291232001-03-0416:

50:

50.000 

（7row（s）affected） 

7.2.9优化备份和恢复的性能 

一个大型数据库完成备份需要花费数小时，使用下面的技巧可以缩短备份和恢复数据库的时间：

·在服务器本地备份文件，随后将其移到分离的网络或磁带驱动器上，速度将比较快。

·在非高峰时段执行完全备份。

在某些机器上备份的I/O操作相当密集，并且使服务器的性能显著降低。

·将文件备份到RAID1或10驱动器，这是因为在备份期间将出现大量的写操作。

·在备份大型数据库时，使用存储局域网络（SAN）驱动器。

·并行地将大型数据库备份到多个备份驱动器，SQLServer为每个备份设备创建一个备份。

·如果备份到磁带驱动器，尝试备份到并行磁带驱动器。

7.3数据库恢复模式 

SQLServer2000新增的优秀功能之一是数据库恢复模式，这种模式允许用户控制事务日志备份的大小及其速度，并可以简化灾难恢复。

旧的选项，例如selectinto/bulkcopy和trunc.logoncheckpoint选项已经被Simple、Full和Bulked-loggedRecovery模式取代，每种模式具有不同程度的可接受恢复时间和速度。

说明：

selectinto/bulkcopy和trunc.logoncheckpoint选项为了向后兼容性而保留在SQLServer2000中，这些选项只能通过T-SQL命令访问，例如sp_dboption存储过程。

如