1、SQL Server20053:451第第12章章 数据库的备份和恢复数据库的备份和恢复 23:45第第1212章章 数据库的备份和恢复数据库的备份和恢复 学习要点：学习要点：备份的概念备份的概念 备份的类型备份的类型 数据库备份的方法数据库备份的方法 恢复数据库的方法恢复数据库的方法 数据的导入和导出数据的导入和导出33:45第第1212章章 数据库的备份和恢复数据库的备份和恢复 备份概述备份概述 1 备份操作备份操作 2 恢复操作恢复操作 3 恢复数据库的其他方法恢复数据库的其他方法4 数据的导入和导出数据的导入和导出543:4512.112.1备份概述备份概述12.1.112.1.1为什
2、么以及何时备份为什么以及何时备份 备份是指数据库管理员定期或不定期地将数据库部分或备份是指数据库管理员定期或不定期地将数据库部分或全部内容复制到磁带或磁盘上保存起来的过程。全部内容复制到磁带或磁盘上保存起来的过程。当数据当数据库遭到破坏时，可以利用备份进行数据库的恢复库遭到破坏时，可以利用备份进行数据库的恢复。备份的目的就是当数据库发生意外时，备份的目的就是当数据库发生意外时，尽可能的减少数尽可能的减少数据的丢失据的丢失。何时进行备份，取决于所能承受数据损失的大小和数据何时进行备份，取决于所能承受数据损失的大小和数据变化的程度。变化的程度。执行备份操作执行备份操作必须拥有数据库备份的权限必须拥
3、有数据库备份的权限系统管理系统管理员、数据库所有者、数据库备份执行者员、数据库所有者、数据库备份执行者。53:4512.1.2备份类型 SQL Server 2005支持的备份类型有六种支持的备份类型有六种：1.1.完整数据库备份完整数据库备份2.2.差异数据库备份差异数据库备份 3.3.部分备份部分备份4.4.部分差异备份部分差异备份5.5.事务日志备份事务日志备份 6.6.文件和文件组备份文件和文件组备份 63:451完整数据库备份完整数据库备份备份数据库中当前所有的数据，包括事务日志。与差异备份数据库中当前所有的数据，包括事务日志。与差异备份和事务日志备份相比，完整数据库备份备份和事务日
4、志备份相比，完整数据库备份使用的存储空间使用的存储空间多，完成备份操作需要的时间长，多，完成备份操作需要的时间长，所以完整数据库备份的创所以完整数据库备份的创建频率通常比差异备份或事务日志备份低。建频率通常比差异备份或事务日志备份低。完整数据库备份完整数据库备份适用备份容量较小或数据库中数据的修适用备份容量较小或数据库中数据的修改较少的数据库改较少的数据库。完整数据库备份时差异备份和事务日志备。完整数据库备份时差异备份和事务日志备份的基准。份的基准。2差异数据库备份差异数据库备份备份自上次完整数据库备份以来备份自上次完整数据库备份以来更改的数据更改的数据。差异数据。差异数据库备份比完整数据库库
5、备份比完整数据库备份小而且备份速度快备份小而且备份速度快，因此可以经常，因此可以经常地备份；地备份；经常备份将减少丢失数据的危险。差异数据库备份经常备份将减少丢失数据的危险。差异数据库备份适合于修改频繁的数据库适合于修改频繁的数据库。73:453部分备份部分备份部分备份与完整数据库备份类似，但是部分备份部分备份与完整数据库备份类似，但是部分备份不包含不包含所有文件组所有文件组。部分备份。部分备份只备份主文件组、所有读写文件组以只备份主文件组、所有读写文件组以及任何选择指定的只读文件或文件组中的所有完整数据及任何选择指定的只读文件或文件组中的所有完整数据。只。只读数据库的部分备份仅包含主文件组。
6、读数据库的部分备份仅包含主文件组。部分备份通常应用于在最后一次完整数据库备份后，拥部分备份通常应用于在最后一次完整数据库备份后，拥有一个或多个只读文件组的情况，对包含一些只读文件组的有一个或多个只读文件组的情况，对包含一些只读文件组的数据库的备份工作提供了灵活性。数据库的备份工作提供了灵活性。4部分差异备份部分差异备份部分差异备份部分差异备份仅备份自上一次部分备份以来文件组中发仅备份自上一次部分备份以来文件组中发生更改的数据生更改的数据。部分差异备份比部分备份小而且备份速度快。部分差异备份比部分备份小而且备份速度快。部分差异备份只与部分备份一起使用。部分差异备份只与部分备份一起使用。83:45
7、5事务日志备份事务日志备份备份备份自上次备份以来数据变化的过程自上次备份以来数据变化的过程，即事务日志文件的信息。，即事务日志文件的信息。其中的上次备份可以是完整数据库备份、差异数据库备份或事务其中的上次备份可以是完整数据库备份、差异数据库备份或事务日志备份。每个事务日志备份都包括创建备份时处于活动状态的日志备份。每个事务日志备份都包括创建备份时处于活动状态的部分事务日志，以及先前事务日志备份中未备份的所有日志记录。部分事务日志，以及先前事务日志备份中未备份的所有日志记录。可以使用事务日志备份将数据库恢复到特定的即时点（如输入多可以使用事务日志备份将数据库恢复到特定的即时点（如输入多余数据前的
8、那一点）或恢复到故障点余数据前的那一点）或恢复到故障点。6文件和文件组备份文件和文件组备份适合于特大型数据库，因为一个很大的数据库要进行完全数据适合于特大型数据库，因为一个很大的数据库要进行完全数据库备份需要很长的时间，那么可以将数据库的文件和文件组分别库备份需要很长的时间，那么可以将数据库的文件和文件组分别进行备份。使用文件和文件组备份进行备份。使用文件和文件组备份可以只还原损坏的文件，而不可以只还原损坏的文件，而不用还原数据库的其余部分，从而加快了恢复速度用还原数据库的其余部分，从而加快了恢复速度。文件和文件组。文件和文件组的备份又可以分为完整文件和文件组备份以及差异文件和文件组的备份又可
9、以分为完整文件和文件组备份以及差异文件和文件组备份。备份。93:4512.1.312.1.3数据恢复数据恢复及恢复模型及恢复模型 备份后如果数据库发生了意外，一般应遵循如下的步骤进备份后如果数据库发生了意外，一般应遵循如下的步骤进行恢复：行恢复：1）如果当前日志没有损坏，首先备份事务日志。）如果当前日志没有损坏，首先备份事务日志。2）恢复最近的完整数据库备份。）恢复最近的完整数据库备份。3）恢复最近的差异备份（如果进行过差异备份）。）恢复最近的差异备份（如果进行过差异备份）。4）依次恢复自差异备份以后的所有事务日志备份（按备份）依次恢复自差异备份以后的所有事务日志备份（按备份的先后顺序恢复）。
10、的先后顺序恢复）。103:45SQL Server 2005 提供了三种恢复模型，以确定如何备份提供了三种恢复模型，以确定如何备份数据以及能承受何种程度的数据丢失。数据以及能承受何种程度的数据丢失。简单恢复简单恢复 完全恢复完全恢复 大容量日志记录恢复大容量日志记录恢复113:451简单恢复模式简单恢复模式 简单恢复模式可最大程度地减少事务日志的管理开销，简单恢复模式可最大程度地减少事务日志的管理开销，事务日志自动截断，在此模式下不能进行事务日志备份。事务日志自动截断，在此模式下不能进行事务日志备份。因此，使用简单恢复模式只能将数据库恢复到最后一次备因此，使用简单恢复模式只能将数据库恢复到最后
11、一次备份时的状态，无法将数据库还原到故障点或特定的即时点。份时的状态，无法将数据库还原到故障点或特定的即时点。在简单恢复模式下只能进行完整备份和差异备份在简单恢复模式下只能进行完整备份和差异备份。123:452完全恢复模式完全恢复模式 完全恢复模式完整记录所有事务，因此能提供将数据库完全恢复模式完整记录所有事务，因此能提供将数据库恢复到故障点或特定即时点的能力。恢复到故障点或特定即时点的能力。在完全恢复模式下可以进行各种备份在完全恢复模式下可以进行各种备份。3大容量日志恢复模式大容量日志恢复模式 大容量日志恢复模式简单的记录大容量操作的日志（如大容量日志恢复模式简单的记录大容量操作的日志（如索
12、引创建和大容量加载），完整地记录其他日志。索引创建和大容量加载），完整地记录其他日志。大容量日志恢复模式提高了大容量操作的能力，常大容量日志恢复模式提高了大容量操作的能力，常作为作为完全恢复模式的补充完全恢复模式的补充。133:4512.1.4备份设备创建备份时，必须选择存放备份数据的备份设备，即存放创建备份时，必须选择存放备份数据的备份设备，即存放备份的存储介质。备份的存储介质。备份设备可以是备份设备可以是磁盘或磁带磁盘或磁带。当建立一个备份设备时要分。当建立一个备份设备时要分配一个逻辑名称和一个物理名称。配一个逻辑名称和一个物理名称。物理名称物理名称是操作系统用来标识备份设备的名称；是操作
13、系统用来标识备份设备的名称；逻辑名称逻辑名称是用户定义的，用来标识物理备份设备的别名。是用户定义的，用来标识物理备份设备的别名。1使用使用SQL Server Management Studio创建备份设备创建备份设备143:452使用系统存储过程使用系统存储过程sp_addumpdevice创建备份设备创建备份设备 sp_addumpdevice device_type,logical_name,physical_name 其中：其中：device_type：是指备份设备类型，磁盘：是指备份设备类型，磁盘：disk;磁带：磁带：tape。logical_name：备份设备的逻辑名称。：备份设
14、备的逻辑名称。physical_name：备份设备的逻辑名称，包含完整路径。：备份设备的逻辑名称，包含完整路径。153:45备份设备添加实例备份设备添加实例例例12.1 本示例添加一个名为本示例添加一个名为sales_backup 的磁盘备份设备，的磁盘备份设备，其物理名称为其物理名称为D:sales_backup.bak。EXEC sp_addumpdevice disk,sales_backup,d:sales_backup.bak 可以使用系统存储过程可以使用系统存储过程sp_dropdevice删除备份设备删除备份设备。例例12.2 下面的示例删除下面的示例删除12.1创建的备份设备。
15、创建的备份设备。EXEC sp_dropdevice sales_backup 163:4512.212.2备份操作备份操作12.2.1使用使用SQL Server Management Studio备份数据库备份数据库173:4512.2.212.2.2使用使用Transact-SQL Transact-SQL 语句备份数据库语句备份数据库 1使用使用BACKUP DATABASE 命令进行完整数据库备份和差异数据库备份命令进行完整数据库备份和差异数据库备份 BACKUP DATABASE database_name TO WITH INIT|NOINIT ,DIFFERENTIAL DIF
16、FERENTIAL：该选项表示进行差异数据库备份。：该选项表示进行差异数据库备份。backup_device：指定用于备份操作的备份设备，可以是逻辑名称或物理名称。：指定用于备份操作的备份设备，可以是逻辑名称或物理名称。如果是物理名称，要输入完整的路径和文件名。如：如果是物理名称，要输入完整的路径和文件名。如：DISK=d:BACKUPmybackup.bak。INIT：该选项表示重写备份集的数据。：该选项表示重写备份集的数据。NOINIT：该选项表示备份数据将追加在原有的内容之后，：该选项表示备份数据将追加在原有的内容之后，NOINIT是默认设是默认设置。置。183:4512.212.2备份
17、操作备份操作例例12.3 本例为本例为 sales数据库创建一个完全数据库备份和一数据库创建一个完全数据库备份和一个差异数据库备份，将备份保存到个差异数据库备份，将备份保存到sales_backup备份设备上。备份设备上。BACKUP DATABASE sales TO sales_backup With init GO BACKUP DATABASE sales TO sales_backup with differential GO193:452使用使用BACKUP LOG命令进行事务日志备份命令进行事务日志备份 BACKUP LOG database_name TO backup_dev
18、ice例例12.4 本例为本例为sales数据库创建一个事务日志备份。数据库创建一个事务日志备份。BACKUP LOG sales TO sales_backup203:4512.312.3恢复操作恢复操作 恢复数据库是加载备份并应用事务日志重建数据库的过程。恢复数据库是加载备份并应用事务日志重建数据库的过程。在数据库的恢复过程中，用户不能进入数据库，即数据库是在数据库的恢复过程中，用户不能进入数据库，即数据库是不能使用的。不能使用的。恢复数据库时，恢复数据库时，SQL Server自动执行安全性检查，防止用户自动执行安全性检查，防止用户从不完整、不正确的备份或其他数据库备份恢复数据库。从不完
19、整、不正确的备份或其他数据库备份恢复数据库。在恢复数据库之前，必须保证备份文件是正确的。在恢复数据库之前，必须保证备份文件是正确的。在在SQL Server中，可以使用图形化界面和中，可以使用图形化界面和Transact-SQL 语句语句进行数据库恢复。进行数据库恢复。12.3.1使用使用SQL Server Management Studio恢复数据库恢复数据库213:4512.3.212.3.2使用使用Transact-SQL Transact-SQL 语句恢复数据库语句恢复数据库RESTORE DATABASE database_name FROM backup_device WITH
20、FILE=file_number ，NORECOVERY|RECOVERY|STANDBY=undo_file_name FILE=file_number：标识要还原的备份集。例如，：标识要还原的备份集。例如，file_number 为为1表表示备份媒体上的第一个备份集，示备份媒体上的第一个备份集，file_number为为2表示第二个备份集。表示第二个备份集。NORECOVERY：表示还原操作不回滚任何未提交的事务。如果需要恢：表示还原操作不回滚任何未提交的事务。如果需要恢复另一个事务日志，则必须指定复另一个事务日志，则必须指定NORECOVERY或或STANDBY选项。如选项。如果果NOR
21、ECOVERY、RECOVERY 和和 STANDBY均未指定，则默认为均未指定，则默认为RECOVERY。当还原数据库备份和多个事务日志时，或在需要多个。当还原数据库备份和多个事务日志时，或在需要多个RESTORE语句时（例如在完整数据库备份后进行差异数据库备份），语句时（例如在完整数据库备份后进行差异数据库备份），SQL Server要求在除最后的要求在除最后的RESTORE语句外的所有其他语句上使用语句外的所有其他语句上使用 WITH NORECOVERY 选项。选项。RECOVERY：表示还原操作回滚任何未提交的事务，在恢复后即可使：表示还原操作回滚任何未提交的事务，在恢复后即可使用数
22、据库。只有在最后一步恢复时使用。用数据库。只有在最后一步恢复时使用。223:4512.312.3恢复操作恢复操作例例12.5：从：从sales_backup备份设备中还原完全数据库备份后还原差异数据备份设备中还原完全数据库备份后还原差异数据库备份。库备份。RESTORE DATABASE sales FROM sales_backup WITH NORECOVERY Go RESTORE DATABASE sales FROM sales_buckup WITH FILE=2 Go233:4512.412.4脱机与联机数据库脱机与联机数据库 如果需要暂时关闭某个数据库的服务，用户可以通过如果需
23、要暂时关闭某个数据库的服务，用户可以通过选择脱机的方式来实现。选择脱机的方式来实现。脱机后，在需要时可以对暂时关闭的数据库通过联机脱机后，在需要时可以对暂时关闭的数据库通过联机操作的方式重新启动服务。操作的方式重新启动服务。下面分别介绍如何实现数据库的脱机与联机操作。下面分别介绍如何实现数据库的脱机与联机操作。243:45 12.4.112.4.1脱机数据库脱机数据库选选择择脱脱机机数数据据库库 253:45使数据库脱机使数据库脱机263:45 12.4.212.4.2联机数据库联机数据库选择联机数据库选择联机数据库 273:45使数据库联机使数据库联机283:4512.512.5分离和附加数
24、据库分离和附加数据库 分离和附加数据库的操作可以将数据库从一台计算机分离和附加数据库的操作可以将数据库从一台计算机移到另一台计算机，而不必重新创建数据库。移到另一台计算机，而不必重新创建数据库。293:45 除了系统数据库以外，其他数据库都可以从服务器除了系统数据库以外，其他数据库都可以从服务器的管理中分离出来，脱离服务器管理的同时保持数据文的管理中分离出来，脱离服务器管理的同时保持数据文件和日志文件的完整性和一致性。件和日志文件的完整性和一致性。分离后的数据库又可以根据需要重新附加到数据库服分离后的数据库又可以根据需要重新附加到数据库服务器中。务器中。303:45 12.5.112.5.1分
25、离数据库分离数据库 分离数据库不是删除数据库，它只是将数据库从服分离数据库不是删除数据库，它只是将数据库从服务器中分离出去。务器中分离出去。313:45分分离离数数据据库库 323:45分离数据库分离数据库333:4512.5.212.5.2附加数据库附加数据库 与分离操作相对应的就是附加操作，它可以将分离的与分离操作相对应的就是附加操作，它可以将分离的数据库重新附加到服务器中，也可以附加其他服务器组数据库重新附加到服务器中，也可以附加其他服务器组中分离的数据库。中分离的数据库。但在附加数据库时必须指定但在附加数据库时必须指定主数据文件（主数据文件（MDF文件）文件）的名称和物理位置的名称和物
26、理位置。343:45附加数据库附加数据库353:45附加数据库附加数据库363:4512.612.6导入和导出数据表导入和导出数据表 SQL Server 2005提供了强大的数据导入导出功能，提供了强大的数据导入导出功能，它可以在多种常用数据格式（数据库、电子表格和文本它可以在多种常用数据格式（数据库、电子表格和文本文件）之间导入和导出数据，为不同数据源间的数据转文件）之间导入和导出数据，为不同数据源间的数据转换提供了方便。换提供了方便。373:4512.712.7脚脚 本本 脚本是存储在文件中的一系列脚本是存储在文件中的一系列SQL语句，是可再用语句，是可再用的模块化代码。的模块化代码。用
27、户通过用户通过“SQL Server Management Studio”工具可工具可以对指定文件中的脚本进行修改、分析和执行。以对指定文件中的脚本进行修改、分析和执行。383:45 12.7.112.7.1将数据库生成脚本将数据库生成脚本 数据库在生成脚本文件后，可以在不同的计算机之数据库在生成脚本文件后，可以在不同的计算机之间传送。间传送。393:45编写脚本模式编写脚本模式 403:45生成脚本生成脚本413:45 12.7.212.7.2将数据表生成脚本将数据表生成脚本 除了将数据库生成脚本文件以外，用户还可以根据除了将数据库生成脚本文件以外，用户还可以根据需要将指定的数据表生成脚本文
28、件。需要将指定的数据表生成脚本文件。423:45编写脚本模式编写脚本模式 433:45生成脚本生成脚本443:4512.7.312.7.3执行脚本执行脚本 脚本文件生成以后，用户可以通过脚本文件生成以后，用户可以通过“SQL Server Management Studio”工具对指定的脚本文件进行修改，工具对指定的脚本文件进行修改，然后执行该脚本文件。然后执行该脚本文件。453:45脚本文件脚本文件463:45小结 SQL Server2005的备份和恢复的相关知识；的备份和恢复的相关知识；备份类型、备份设备的创建、通过图形界面和备份类型、备份设备的创建、通过图形界面和Transact-SQ
29、L语句进行备份和恢复的具体操作的方语句进行备份和恢复的具体操作的方法，法，数据的导入和导出等内容。数据的导入和导出等内容。脚本的生成、执行脚本的生成、执行473:45483:45493:45503:45习题1为什么要进行备份？为什么要进行备份？2SQL Server中备份分为哪几种类型，它们之间有什么区别？中备份分为哪几种类型，它们之间有什么区别？3恢复模式的设置对备份有什么影响？恢复模式的设置对备份有什么影响？4如何创建备份设备？如何创建备份设备？5在在BACKUP命令中命令中INIT和和NOINIT参数有什么作用？参数有什么作用？6在在RESTORE命令中命令中RECOVERY和和NORE
30、COVERY参数有什么作用？参数有什么作用？7哪些系统数据库必须定期的进行备份？哪些系统数据库必须定期的进行备份？513:458下面是某数据库进行备份的时间表，晚上下面是某数据库进行备份的时间表，晚上10：00数据库数据库失败，简述怎样恢复数据库才能尽量减少数据的损失。失败，简述怎样恢复数据库才能尽量减少数据的损失。时间时间 事件事件 上午上午8：00 备份数据库备份数据库 中午中午 备份日志文件备份日志文件 下午下午4：00 备份日志文件备份日志文件 下午下午6：00 备份数据库备份数据库 晚上晚上8：00 备份日志文件备份日志文件 晚上晚上10：00 数据库失败数据库失败SQL Server20053:4552
