数据技术应用 11

项目12备份和恢复数据库POWERPOINTDESIGN目录项目描述与学习目标01任务12.1设置故障恢复策略02任务12.2备份数据库03CONTENTS04任务12.3恢复数据库05任务12.4批量导入和导出数据06任务12.5新技术拓展与行业趋势项目描述与学习目标对数据库服务器来说，安全性是至关重要的。任何系统都会不可避免地出现各种形式的故障，而一些故障可能会导致数据库灾难性的损坏，因此，提高数据的安全性和数据恢复能力一直是用户关注的焦点。备份是恢复数据最常用最有效的方法之一。为了保证数据的安全性，SQLServer提供了强大的备份和恢复功能，也支持将数据转换到其他结构的数据库管理系统或文件中，用来进行数据交换。学生成绩管理数据库在交付以后，数据库管理员或运维人员需要持续不断地监控并管理系统安全稳定运行。数据库系统的运行过程中可能会发生很多意想不到的事情，从而影响数据库的安全和数据信息的完整。因此需要定期对数据库进行备份，以保证系统一旦发生故障，数据库管理员能在尽可能减少数据损失的情况下，将数据库恢复到正常状态。项目描述学习目标1.了解数据库故障种类以及数据库恢复技术；2.理解数据库备份与恢复的概念以及意义；3.掌握备份和恢复数据库的方法。知识目标1.能基于数据库系统运行需求制定备份计划；2.能根据备份计划进行数据库备份；3.在发生故障时能使用备份及时恢复数据库到正常状态；4.会执行数据的导入与导出操作。技能目标1.通过模拟故障环境，在故障排查、故障分析和定位的同时，培养逻辑思考能力，提高问题解决和故障排除能力；2.通过严谨规范的数据备份与恢复实践，帮助企业解决实际问题，增强服务意识和责任意识，为未来的职业发展奠定基础。素质目标设置故障恢复策略12.1任务-POWERPOINTDESIGN尽管数据库系统中采取了各种保护措施来防止数据库的安全性和完整性遭到破坏，保证并发操作都能正确执行，但是计算机系统中的硬件故障、软件错误、操作员失误及恶意破坏仍然是不可避免的，这些故障轻则造成运行中的事务非正常中断，影响数据库中数据的正确性，重则破坏数据库，使数据库中部分或全部数据丢失。SGMS数据库的数据量上限预计约120GB，主要使用时间为工作日的上午9时至晚上9点。特殊时间段（如开学前排课、期末考试前后、各类考试报名等）会在上午8时到24时频繁使用。数据库备份需要结合系统的数据规模、使用情况来设计备份的策略，尽量减少对用户使用的影响，同时确保在系统发生故障的时候减少数据损失。本任务针对学生成绩管理系统的运行状况设计故障恢复策略，同时制定好备份计划，在发生故障时最大限度地防止数据丢失。【任务描述】事务故障系统故障介质故障事务在运行过程中由于种种原因，如输入数据的错误、运算溢出、违反了某些完整性限制、某些应用程序的错误，以及并发事务发生死锁等，未运行至正常终止点就被撤销，这种情况称为事务故障。系统在运行过程中，由于某种原因致使事务非正常停止，而需要重新启动，这种情况称为系统故障。例如，操作系统故障、硬件故障（如CPU故障）、突然停电等。发生系统故障时内存中数据库缓冲区的数据会丢失，但存储在外存储设备上的数据不会受影响。在系统的运行过程中，某种硬件故障，如磁盘损坏、磁头碰撞，或操作系统的某种潜在错误、瞬时强磁场干扰等，会使存储在外存上的数据部分或全部损失，这种情况称为介质故障。这类故障发生的可能性虽然比前两类故障小得多，但破坏性却最大。知识储备计算机病毒计算机病毒是一种人为破坏计算机正常工作的特殊程序。通过读写染有病毒的计算机系统中的程序与数据，这些病毒可以迅速传播，危害计算机系统和数据库。目前大多数病毒是在PC和其兼容机上传播的。有的病毒一旦侵入系统就马上摧毁系统，有的病毒有较长的潜伏期，有的病毒则只在特定的日期发生破坏作用，有的病毒感染系统所有的程序和数据，有的只影响特定的程序和数据。完整备份差异备份事务日志备份完整备份是指包含一个或多个数据文件的完整映像的任何备份。数据库完整备份会备份所有数据和足够的日志，以便恢复数据。完整备份可以对全部或部分数据库、一个或多个文件进行数据备份。差异备份基于数据库完整备份，称为差异的基准备份。基准备份是差异备份所对应的最近完整或部分备份。差异备份仅包含基准备份之后更改的数据。在还原差异备份之前，必须先还原其基准备份。事务日志备份（也称日志备份）包括了在前一个日志备份中没有备份的所有日志记录。只有在完整恢复模式和大容量日志恢复模式下才可以进行事务日志备份。知识储备完整恢复模式大容量日志恢复模式简单恢复模式完整恢复模式完整地记录了所有的事务，并保留所有事务的完整日志记录，直到将它们备份。完整恢复模式能使数据库恢复到任意时间点，基本不会丢失数据。在完整恢复模式下，用户可以进行“完整”“差异”“事务日志”及“结尾日志”备份类型的操作。在大容量日志恢复模式下，进行大容量操作时不是将每一项事务都记录到日志中，而是只对这些操作进行开始和结果等基础信息的记录，不记录实际操作事务的过程。大容量日志恢复模式只能将数据库恢复到日志备份的结尾，而不能恢复到某个时间点或日志备份中某个标记的事务。简单恢复模式可以理解为没有事务日志的备份，其处理过程比较简单。简单恢复模式只能将数据库恢复到备份时刻，而且会丢失数据库备份后的所有操作。在简单恢复模式下，用户只可以进行“完整”“差异”备份类型的操作。知识储备例12-1：模拟SGMS数据库中断访问，直接使用数据和日志文件恢复数据库。操作步骤：（1）使用sp_detach_db存储过程来分离SGMS数据库。在SSMS中可以看到对象资源浏览器中SGMS数据库已经被分离，此时数据库与服务器的连接已中断。USEmasterGOEXECsp_detach_db'SGMS’GO（2）在文件未损坏的情况下，可以使用利用sp_attach_db存储过程来附加数据库到服务器。USEmasterGOEXECsp_attach_db'SGMS','D:\data\sgms.mdf','D:\data\sgms_log.ldf’GO例12-2：SGMS数据库的数据量上限预计约120GB，主要使用时间为工作日的上午9时至晚上9点，特殊时间段（如开学前排课、期末考试前后、各类考试报名等）会在上午8时到24时频繁使用。请结合系统数据规模、使用情况为SGMS数据库设计一个备份计划，并使用SSMS完成数据库备份计划的自动执行。操作步骤：（1）完整备份：每个月1日凌晨1时执行一次完整备份。每次的备份内容覆盖之前的备份，节约备份设备存储空间；（2）差异备份：每周日凌晨3时执行一次差异备份；（3）日志备份：每天凌晨6时执行一次日志备份。任务实施：文件方式备份和恢复数据库（演示操作）备份数据库12.2任务-POWERPOINTDESIGN使用T-SQL脚本完成备份BACKUP{DATABASE|LOG}数据库名TO<备份目标>[WITH[DIFFERENTIAL][[,]{FORMAT|NOFORMAT}][[,]{INIT|NOINIT}][[,]NAME={备份名称}]]（1）DIFFERENTIAL：用于执行差异备份，指定数据库备份或文件备份只包含上次完整备份后更改的数据库或文件部分。差异备份一般会比完整备份占用更少的空间。（2）FORMAT|NOFORMAT：FORMAT用于指定创建新的媒体集，将在用于此备份操作的所有卷上写入新的媒体标头，覆盖任何现有的媒体标头和备份集。FORMAT选项会格式化一个备份设备或媒体，将使全部现有的媒体内容失效，所以需要谨慎使用。NOFORMAT指定不将媒体标头写入用于此备份操作的所有卷，是默认选项。（3）INIT|NOINIT：INIT指定应覆盖所有备份集，但是保留媒体标头。NOINIT是默认选项，表示将备份集追加到指定的媒体集上，以保留现有的备份集。（4）NAME={备份名称}：指定备份的名称，以便在恢复时查看，如果未指定，它将为空。SQLServer备份设备物理备份设备是指磁带机或操作系统提供的磁盘文件。物理备份设备的名称包括物理路径和文件名，如“C:\Backup\SGMS.bak”；逻辑备份设备是用户给物理设备定义的一个别名，这个别名保存在SQLServer数据库的系统表中。逻辑设备的优点是可以简单地使用逻辑设备名称而不用给出复杂的物理设备路径，使用逻辑设备也便于用户管理备份信息。知识储备例12-3：创建逻辑备份设备：在磁盘上新建一个名称为“SGMSBACKUP”的逻辑备份设备，用于容纳SGMS数据库的备份。操作步骤：（1）使用具有足够权限的账户登录服务器，在SSMS的“对象资源管理器”中展开“服务器对象”节点，右击“备份设备”，选择“新建备份设备”命令，打开“备份设备”窗口；（2）在“设备名称”文本框输入“SGMSBACKUP”。如果需要重新确定备份存储位置，就在“文件”文本框中输入目标路径及文件名或单击“…”按钮选择目标路径；（3）单击“确定”按钮完成备份设备的创建。此时，新建的备份设备并未实际在存储设备上创建文件，在备份过程中第一次使用到的时候会自动创建相关文件。例12-4：使用可视化界面对完整恢复模式的SGMS数据库进行完整备份。操作步骤：（1）使用具有足够权限的账户登录服务器，在“对象资源管理器”中展开“数据库”节点，右击SGMS数据库，选择“任务”→“备份”命令，打开“备份数据库-SGMS”窗口，在“备份类型”下拉列表框中选择“完整”；（2）选择备份目标。默认情况下备份位置在SQLServer目录的Backup子目录下，如果要修改备份的位置，可以按照如下步骤操作：单击“删除”按钮删除现有的备份目标，再单击“添加”按钮，会弹出“选择备份目标”对话框，在对话框中的“备份设备”单选按钮的下拉列表框中选择“SGMSBACKUP”，单击“确定”按钮，返回“备份数据库-SGMS”窗口；（3）单击“确定”按钮，系统将进行数据库的完整备份，完成后会弹出提示窗口。例12-5：查看“SGMSBACKUP”备份设备的信息。操作步骤：（1）使用具有足够权限的账户登录服务器，在“对象资源管理器”中依次展开“服务器对象”→“备份设备”节点，双击“SGMSBACKUP”，打开“备份设备-SGMSBACKUP”窗口；（2）在窗口左侧选择“介质内容”选择页，可以看到备份设备中已经存储的备份信息，包括备份的名称、类型、数据库名称及时间点等信息。任务实施：使用可视化界面完成备份（演示操作）例12-6：使用T-SQL脚本对SGMS数据库进行完整备份。USEmasterGOBACKUPDATABASESGMSTOSGMSBACKUPWITHNOFORMAT,NOINIT,NAME='SGMS-完整数据库备份'GO例12-7：使用T-SQL语句对SGMS数据库进行差异备份。USEmasterGOBACKUPDATABASESGMSTOSGMSBACKUPWITHDIFFERENTIAL,NAME='SGMS-数据库差异备份'GO例12-8：使用T-SQL脚本对SGMS数据库进行事务日志备份。USEmasterGOBACKUPLOGSGMSTOSGMSBACKUPWITHNAME='SGMS-事务日志备份'GO任务实施：使用T-SQL脚本完成备份（演示操作）例12-9：使用SQLServer代理实现【例12-2】的备份计划。操作步骤：（1）作业的自动运行依赖SQLServer代理服务，所以代理服务需要设置为自动启动，否则所有的作业和自动化任务都不会自动执行。SQLServerAgent服务可以在SQLServer配置管理器中启动，并将服务启动模式修改为“自动”；（2）使用具有足够操作权限的用户登录SSMS，在对象资源管理器中依次展开“管理→维护计划”，右击“维护计划”节点选择“维护计划向导”命令，打开“维护计划向导”窗口，点击下一步按钮，进入“选择计划属性”窗口，输入计划名称“SGMS备份计划”，选择“每项任务单独计划”，并填写相关说明；（3）单击下一步，进入“选择维护任务”窗口，在这里选择需要在维护计划中执行的任务，勾选“备份数据库（完整）”“备份数据库（差异）”和“备份数据库（事务日志）”，如图12-10所示。单击下一步，进入“选择维护任务顺序”窗口，默认显示的就是完整、差异和事务日志，需要分别进行任务的设置；（4）单击下一步进入定义“备份数据库（完整）”任务，在“常规”选项卡下展开并选择数据库“SGMS”；（5）在“目标选项卡”选择“跨一个或多个文件备份数据库”，单击“添加”按钮，选择备份设备“SGMSBACKUP”；（6）单击窗口下方“更改”按钮，进入“新建作业计划”窗口，如图12-13所示。选择计划类型为“重复执行”，勾选“启用”。在频率区域设置为每月1日执行一次，凌晨1时执行。下方说明框内会显示设置的计划详情；（7）单击“下一步”，按照上述步骤设置“定义备份数据库（差异）任务”、“定义备份数据库（日志）任务”，分别设置为每周一次、每日一次；（8）单击“下一步”按钮，进入选择报告选项，这里可以将这个维护计划的执行报告写入文本文件中，也可以将报告通过电子邮件发送给管理员；（9）单击“下一步”按钮，进入“完成该向导”的界面，系统列出了向导要完成的工作，如此便完成指定备份计划的任务；（10）单击“完成”结束维护向导。此计划任务需要依赖SQLServer代理服务才能执行，因此，要确保该服务始终处于运行状态。任务实施：使用SQLServer代理实现自动备份（演示操作）恢复数据库12.3任务-POWERPOINTDESIGN在SQLServer中可以使用SSMS的可视化界面来完成数据库的故障恢复，也可以使用T-SQL提供的RESTOREDATABASE和RESTORELOG语句进行数据库和事务日志的恢复，语法格式为：RESTORE{DATABASE|LOG}数据库名[FROM<备份设备>][WITH[[,]FILE=备份编号][[,]{RECOVERY|NORECOVERY][[,]STOPAT=指定时间点]]在上面的语法描述中，各参数说明如下：（1）FROM<备份设备>：用于指定要从哪些备份设备恢复备份。（2）FILE=备份编号：指定用于数据库恢复的备份文件在备份设备中的编号，这个编号可以在数据库备份设备中查询到。（3）RECOVERY：用于最后一个的待恢复的备份，即此次RESTORE语句执行结束后即可使用数据库。（4）NORECOVERY：用于除最后一个备份以外的所有待恢复备份，即此次RESTORE语句执行后仍有其他备份需要恢复，此刻数据库仍不允许使用。（5）STOPAT：用于将数据库恢复到其在指定日期和时间时的状态。从上面的语法可以看出，在进行恢复操作时，需要多个备份按顺序组合，才能将数据库恢复到指定的状态，不像使用可视化界面那样一次就能完成所有操作。在进行最后一次恢复操作的时候才使用RECOVERY选项，说明恢复操作结束，数据库才可以正常使用。知识储备例12-10：使用可视化界面恢复数据库SGMS。操作步骤：（1）使用具有足够权限的账户登录服务器，在对象资源管理器中展开“数据库”节点，右击选择“还原数据库”命令，弹出“还原数据库”窗口；（2）选中“设备”单选按钮，单击右侧的“…”按钮，打开“选择备份设备”窗口；（3）选择备份设备后，SQLServer会自动识别设备上的备份集，筛选有用的备份并列在“要还原的备份集”列表框中，默认选择到最新的状态；（4）单击“确定”按钮，SQLServer将按照设置情况完成数据库的恢复。任务实施：使用可视化界面恢复数据库（演示操作）例12-11：使用T-SQL脚本将SGMS数据库恢复到最近的一个完整备份时的状态。USEmasterGORESTOREDATABASESGMSFROMSGMSBACKUPWITHFILE=1,NORECOVERYGO例12-12：使用T-SQL脚本将SGMS数据库恢复到最近的一个差异备份时的状态。USEmasterGORESTOREDATABASESGMSFROMSGMSBACKUPWITHFILE=2,NORECOVERYGO例12-13：使用T-SQL脚本将SGMS数据库恢复到最近的事务日志备份时的状态，并完成全部恢复工作。USEmasterGORESTORELOGSGMSFROMSGMSBACKUPWITHFILE=3,RECOVERYGO任务实施：使用T-SQL脚本恢复数据库（演示操作）批量导入和导出数据12.4任务-POWERPOINTDESIGN在数据库的日常管理过程中经常需要将一种格式的数据传输或转换到另一种数据环境中，这就是数据的导入与导出。SQLServer支持各种数据库管理系统、Excel数据文件、文本文件等格式的数据进行相互转换，提高数据录入的效率。SQLServer支持多种导入数据的方式，较为常用的是“数据导入与导出向导”（以下简称DTS）。SQLServer导入和导出向导是一个图形化工具，用于在SQLServer和其他数据源之间移动数据。它支持多种数据源格式，如SQLServer数据库、Excel、Access、ODBC数据源等。向导可以创建简单的包，以便导入和导出数据，还可以创建目标数据库和目标表。在使用DTS进行数据导入导出的过程中还可以完成数据的检验和格式转换，非常实用。本项目中，系统初始化期间、学生及成绩信息导出打印等需求都需要导入导出功能。在日常管理中通常需要将当前系统中的数据导出到其他系统中，或者转换为其他格式的数据供用户使用。例如，将数据库SGMS中学生表的部分数据文件转换为Excel表格，供学生管理人员使用；将招生系统的新生数据导出到SGMS数据库的学生表中；将学生数据导出到财务收费系统等等。此类需求都可以使用数据转换向导来完成。【任务描述】例12-10：将下图所示的Excel文件数据导入SGMS数据库的student表中。任务实施：使用可视化界面批量导入和导出数据（演示操作）操作步骤：在使用DTS导入数据时，可以导入到新表，也可以导入到已有表。在导入已有表格时，数据源的数据结构必须与目标表格数据结构兼容，否则容易导入失败，因此建议在导入之前对数据进行初步的处理。在导入过程中，如果数据存在不匹配的地方，可以进行相应的设置、适当地转换格式，使之与目标表格匹配。实现本任务的步骤如下：（1）使用具有足够权限的账户登录服务器，在SSMS的“对象资源管理器”中展开“数据库”节点，右击SGMS数据库，依次选择“任务→导入数据”命令，打开“SQLServer导入和导出向导”窗口，如下图所示。任务实施：使用可视化界面批量导入和导出数据（演示操作）（2）单击Next按钮，进入“选择数据源”窗口，如下图所示。在“数据源”下拉列表框中选择“MicrosoftExcel”，单击“文件路径”右下方的“浏览”按钮，选择要导入的文件，并勾选“首行包含列名称”复选框。任务实施：使用可视化界面批量导入和导出数据（演示操作）（3）单击Next按钮，进入“选择目标”窗口，如下图所示。在目标列表中选择“MicrosoftOLEDBProviderforSQLServer”，选择服务器并登录，选择目标数据库。（4）单击Next，进入“指定表复制或查询”窗口。任务实施：使用可视化界面批量导入和导出数据（演示操作）（5）选中“复制一个或多个表或视图的数据”单选按钮。单击Next按钮，进入“选择源表和源视图”界面。（6）在“表和视图”列表框中选中要导入的表，单击“预览”按钮可以查看其数据。任务实施：使用可视化界面批量导入和导出数据（演示操作）（7）如果数据源和目标之间的数据结构（列名称、数据类型等）不一致，需要通过单击“编辑映射”按钮在弹出的“列映射”对话框中进行手动匹配。在“列映射”对话框中，数据源为源表格中选中数据表名称。目标为目标视图或表的名称。如果目标表不存在，可以选择“创建目标表”，也可以直接编辑SQL脚本来修改表格结构。默认情况下，导入的数据以追加的方式导入到目标表当中。如果已有目标表中的数据如果已经过时或者源表数据已经覆盖目标表数据，也可以选择“删除目标表中的行”。（8）完成编辑映射后返回“选择源表和源视图”界面，单击Next，进入“查看数据类型映射”界面，确认数据源和目标之间数据列及数据类型的匹配情况，并选择在导入过程中发生错误时的处置方案（默认情况下均为失败，可以选择忽略错误继续数据导入或导出）。任务实施：使用可视化界面批量导入和导出数据（演示操作）（9）确认无误后单击Next按钮，进入“保存并运行包”界面，此时可以选择立即执行，也可以将导入任务保存到SSIS包。SSIS包（SQLServerIntegrationServices包）是Microsoft提供的ETL工具，用于构建高性能数据集成解决方案，保存为SSIS包之后，可以通过SQLServer代理定期自动调度，实现数据仓库的数据提取和集成。（10）选择“立即执行”，单击Next按钮，显示“完成该向导”。单击Finish按钮，启动数据导入的过程，系统将显示执行的步骤和相关信息。（11）数据导入过程结束后将显示结果详情，如已经完成传输的数据量、导入的时长等等。如果导入的过程中发生了错误，也将以消息的形式显示在这里，可以查看错误详情并做