第九章 备份和还原

1. 第九章 备份和还原

2. 9．1 基本概念 • 数据库备份是制作数据库结构、对象和数据的贝,以便在数据库遭到破坏的时候能够修复数据库。数据库还原指将数据库备份加载到服务器中去。

3. 9．1．1 何时需要备份 • SQL Server提供了一套功能强大的数据备份和还原具。数据备份和还原可以用于保护数据库中的关键数据。在系统发生错误的时候，可以利用数据备份来还原数据库中的数据。在下述情况下需要使用数据库的备份还原： • ·存储媒体损坏。例如存放数据库的硬盘损坏。 • ·用户操作错误。例如非恶意地或恶意地修改或删除数据。 • ·整个服务器崩溃。例如操作系统被破坏，造成计算机无法启动。 • ·需要在不同的服务器之间移动数据库时。把一个服务器上的某个数据库备份下来，然后还原到另一个服务器中去。

4. 9．1．2 SQL Server 2000的数据库备份和还原的方法 • SQL Server 2000的数据库备份和还原可以创建数据库拷贝，该拷贝存放到安全的地方，当服务器崩溃或数据库被损坏时，该拷贝就可以用于还原数据库。 • 数据库备份的方法 • 在SQL Server2000中，备份数据库有以下四种方法： • ·全库备份。即制作数据库中所有内容的副本。它在备份过程中需要花费的时间和空间最多，不宜频繁进行。恢复时，仅需要恢复最后一次全库备份即可。该备份以后的修改都将丢失。 • ·差异备份。即只备份最后一次全库备份后被修改的数据页。备份的时间和空间较少。恢复时，先恢复最后一次全库备份，再恢复最后一次差异备份。

5. ·事务日志备份。即只备份最后一次日志备份后所有的事务日志记录下来。备份所用的时间和空间更少。利用日志备份进行恢复时，可以指定恢复到某一个事务，这是全库备份和差异备份所不能做到的。但利用日志备份进行恢复时，需要重新执行日志记录中的修改命令来恢复数据库中的数据，所以通常恢复的时间较长。建议每周进行一次全库备份，每天进行一次差异备份，每小时执行一次日志备份，再恢复最后一次差异备份，再顺次恢复最后一次差异备份以后进行所有事务日志备份。·事务日志备份。即只备份最后一次日志备份后所有的事务日志记录下来。备份所用的时间和空间更少。利用日志备份进行恢复时，可以指定恢复到某一个事务，这是全库备份和差异备份所不能做到的。但利用日志备份进行恢复时，需要重新执行日志记录中的修改命令来恢复数据库中的数据，所以通常恢复的时间较长。建议每周进行一次全库备份，每天进行一次差异备份，每小时执行一次日志备份，再恢复最后一次差异备份，再顺次恢复最后一次差异备份以后进行所有事务日志备份。 • ·文件或文件组备份。即备份某个数据库文件或数据库文件组。必须与事务日志备份结合才有意义。例如，某数据库中有两个数据文件，一次仅备份一个文件，而且在每个数据文件备份后，都要进行日志备份。在恢复时，使用事务日志使所有的数据文件恢复到同一个时间点。

6. 故障还原模型 • 在SQL Server2000中，有以下三种故障还原模型： • ·完全模型。任何对数据库的修改操作都记录到事务日志文件中。 • ·大容量日志记录模型。除对日志空间影响大的操作（如bull insert）外，其他修改操作都记录到事务日志文件中。 • ·简单模型。所有的修改操作都不记录到事务日志文件中。在该模式下，不能进行事务日志备份和文件/文件组备份。在数据库属性窗口“选项”选项卡中，可以选择它的故障还原模式。 • 例如，对某数据库的操作过程中在不同时刻对某数据库进行了不同的操作.

7. 9．2备份数据库 • 备份数据库可以使用备份向导或企业管理器来完成，也可以通过Transact-SQL语句来实现。下面以示例数据库Northwind来例来介绍备份数据库的方法和步骤。 • 9.2.1使用备份向导备份数据库 • 步骤 • 在企业管理器中展开服务器组，然后展开一个服务器。 • 展开“数据库”文件夹，然后单击要备份的数据库，例如Northwind。 • 从“工具”选单中选择“向导”命令，以打开如图9.1所示的“选择向导”对话框。

9. 单击“管理”节点，选择“备份向导”选项，出现“欢迎使用创建数据库备份向导”画面，然后单击“下一步”按钮，出现如图9.2所示的对话框。单击“管理”节点，选择“备份向导”选项，出现“欢迎使用创建数据库备份向导”画面，然后单击“下一步”按钮，出现如图9.2所示的对话框。

10. 选择所备份的数据库（例如Northwind），单击“下一步”按钮，出现“键入备份的名称和描述”对话框，在该对话框中输入备份的名称和描述信息，然后单击“下一步”按钮，出现如图9.3所示的对话框。选择所备份的数据库（例如Northwind），单击“下一步”按钮，出现“键入备份的名称和描述”对话框，在该对话框中输入备份的名称和描述信息，然后单击“下一步”按钮，出现如图9.3所示的对话框。

11. 选择下列备份方法之一。 • ·“数据库备份”：对整个数据库的数据进行备份。 • ·“差异数据库”：对新的或更改的数据库进行备份。 • ·“事务日志”：对数据库的所有更改的命令进行备份。 • 选择一种备份方法之后，单击“下一步”按钮，出现如图9.4所示的对话框。

12. 选择备份目的和操作。选择“备份设备”选项为“文件”方式，其中文件名和路径自定义：如果要将此次备份追加到原有备份数据的后面，可以选择“属性”选项中的“追加到备份媒体”选项；如果要用此次备份的数据覆盖原有备份数据，可以选择“重写现有媒体”选项。单击“下一步”按钮，出现如图9.5所示的对话框。选择备份目的和操作。选择“备份设备”选项为“文件”方式，其中文件名和路径自定义：如果要将此次备份追加到原有备份数据的后面，可以选择“属性”选项中的“追加到备份媒体”选项；如果要用此次备份的数据覆盖原有备份数据，可以选择“重写现有媒体”选项。单击“下一步”按钮，出现如图9.5所示的对话框。

13. 单击“更改”按钮，确定备份的计划，然后单击“下一步”按钮，出现备份向导的“完成”对话框，在核对话框中显示刚才所设置的各属性，单击“完成”按钮，则出现备份成功的对话框。此时，用向导完成了数据库的备份，并在相应的文件夹内产生了一个.BAK备份文件。单击“更改”按钮，确定备份的计划，然后单击“下一步”按钮，出现备份向导的“完成”对话框，在核对话框中显示刚才所设置的各属性，单击“完成”按钮，则出现备份成功的对话框。此时，用向导完成了数据库的备份，并在相应的文件夹内产生了一个.BAK备份文件。

14. 9.2.2 使用企业管理器备份数据库 • 下列以示例数据库Northwind为例介绍如何在企业管理器中备份数据库。 • 步骤 • (1)在企业管理器中，展开服务器组，然后展开一个服务器。

15. （2）展开“数据库”文件夹，单击要备份的数据库，然后选择“操作→所有任务→备份数据库”命令，如图9.6所示。（2）展开“数据库”文件夹，单击要备份的数据库，然后选择“操作→所有任务→备份数据库”命令，如图9.6所示。

16. （3）当出现如图9.7所示的“SQL Server备份”对话框时，在“名称”框中输入数据库备份集的名称。如果需要，也可以在“描述”框中输入一些说明文字，描述数据库备份集。

17. 在“备份”区域中选项备份的方法： • ·若要执行完整数据库备份，则选择“数据库一完全”选项。 • ·若要执行差异数据库备份，即仅备份自上次完整数据库备份以后对数据库数据所修改的数据页，则选择“数据库一差异”选项。 • ·若要备份事务日志，则选择“事务日志”选项。 • ·若只想备份数据库中的某个文件和文件组，则单击浏览按钮，以选择相应的文件或文件组。

18. 指定备份的目的。在“目的”区域中单击“添加”按钮，并在如图9.8所示的“选择备份目的”对话框中指定一个备份文件或备份设备，使止出现在图9.7所示的对话框中“备份到”下边的列表框中。在一次备份操作中可以指定多个目标设备或文件，这样可以将一个数据库备份到多个文件或设备中。指定备份的目的。在“目的”区域中单击“添加”按钮，并在如图9.8所示的“选择备份目的”对话框中指定一个备份文件或备份设备，使止出现在图9.7所示的对话框中“备份到”下边的列表框中。在一次备份操作中可以指定多个目标设备或文件，这样可以将一个数据库备份到多个文件或设备中。

19. 在“重写”区域中选择备份方式： • ·若要将此次备份追加在原有备份数据的后面，则选择“追加到媒体”选项。 • ·若要将此次备份的数据覆盖原有备份数据，则选择“重写现有媒体”选项。 • （7）在“调度”区域中制定备份日程。如果希望按照一定周期对数据库进行备份，可以选取“调度”复选框，然后单击按钮并在如图9.9所示的“编辑调度”对话框中安排备份数据库的执行时间。

20. 返回到“数据库备份”对话框以后，单击“确定”按钮，开始执行备份操作，此时出现相应的提示信息。返回到“数据库备份”对话框以后，单击“确定”按钮，开始执行备份操作，此时出现相应的提示信息。 • （9）当看到“备份操作已顺利完成”的提示信息时，单击“确定”按钮，结束备份操作。

21. 9.2.3使用Transact-SQL语句备份数据库 • 在查询分析器中，可以通过执行有关的系统存储过程的语句来完成数据库的备份操作，这个过程通常包含以下两个步骤。 • 用系统存储过程sp_addumpdevice创建一个备份设备 • sp_addumpdevice存储过程常用的语法格式为： • [EXECUTE] sp_addumpdevice‘设备类型’，‘逻辑名称’，‘物理名称’

22. 第一个参数用于指定备份设备的类型，备份设备即用来存放备份数据的物理设备，包括磁盘、磁带和命名管道，分别用‘disk’、‘pipe’和‘tape’表示。第一个参数用于指定备份设备的类型，备份设备即用来存放备份数据的物理设备，包括磁盘、磁带和命名管道，分别用‘disk’、‘pipe’和‘tape’表示。 • 当建立一个备份设备时，要给该设备分配一个逻辑备份名称和一个物理备份名称，物理备份名称是计算机操作系统所能识别的该设备所使用的名字，如果是一个磁盘设备，则物理名称是设备存储在本地或网络上的物理名称，例如“E:\Databackup\Market.bat”；逻辑名称是物理设备名称的一个别名，用于SQL Server管理备份设备，逻辑名称存储在SQL Server的系统表sysdevices中，使用逻辑名称的好处是比物理名称简单好记。

23. 用BACKUP语句执行备份操作 • 使用BACKUP语句可以对数据库进行全库备份、差异备份、日志备份或文件和文件组备份。 • ·全库备份。即制作数据库中所有内容的一个副本，从一个全库备份中就可以恢复整个数据库。其语法格式为： • BACKUP DATABASE数据库名 TO 备份设备名 • [WITH[NAME=‘备份的名称’][，INIT | NOINIT]] • 在上述语法格式中，备份设备名采用“备份设备类型=设备名称”的形式；INIT参数表示新备份的数据覆盖当前备份设备上的每一项内容，即原来在此设备上的数据信息都将不存在了；NOINIT参数表示新备份的数据添加到备份设备上已有内容的后面

24. ·差异备份。即从最近一次全库备份结束以来所有改变的数据备份到数据库。当数据库从上次备份以来，数据发生很少的变化时适合使用差异备份。其语法格式如下：·差异备份。即从最近一次全库备份结束以来所有改变的数据备份到数据库。当数据库从上次备份以来，数据发生很少的变化时适合使用差异备份。其语法格式如下： • BACKUP DATABASE数据库名 TO 备份设备名 • WITH DIFFERENTIAL[，NAME=‘设备的名称’][，INIT|NOINIT] • 在上述语法格式中，DIFFERENTIAL子句子的作用是，通过它可以指定只对在创建最新的数据库备份后数据库中发生变化的部分进行备份。

25. ·日志备份。即从最近一次日志备份以来所有事物日志备份到备份设备。通常情况下日期备份经常与全库备份和差异备份结合使用，其语法格式如下：·日志备份。即从最近一次日志备份以来所有事物日志备份到备份设备。通常情况下日期备份经常与全库备份和差异备份结合使用，其语法格式如下： • BACKUP LOG 数据库名 TO 备份设备名 • [WITH[NAME=‘备份的名称’][，INT|NOINIT]] • 这个语法格式中的参数与全库备份格式中的参数相同。

26. ·文件与文件组备份。当一个数据为很大时，对整个数据库进行备份可能花费很多时间，这时可以采用文件和文件组备份方式，即对数据库中的部分文件或文件组进行备份。其语法如下·文件与文件组备份。当一个数据为很大时，对整个数据库进行备份可能花费很多时间，这时可以采用文件和文件组备份方式，即对数据库中的部分文件或文件组进行备份。其语法如下 • BACKUP DATABASE 数据库名 • FILE=‘文件的逻辑名称’| FILEGROUP=‘文件组的逻辑名称’TO备份设备名 • [WITH[NAME=‘备份的名称’][，INT|NOINIT]] • 使用上述语法格式备份数据库时，如果备份的是文件，则写做“FILE=‘文件的逻辑名称’”的方式；如果备份的是文件组，则写做“FILEGROUP=‘文件组的逻辑名称’”的方式。

27. 9.3还原数据库 • 还原数据库就是把原来备份的数据恢复到备份前的状态。当还原数据库时，SQL Server会自动将备份文件中的数据库全部拷贝到数据库，并回滚任何未完成的事务，以保证数据库中数据的一致性。

28. 9.3.1 使用企业管理器还原数据库 • 下面使用企业管理器把前面章节中备份的数据库文件Northwind的备份文件“Northwind备份.BAK”恢复到原有状态。 • 步骤 • （1）在企业管理器中展开服务器组，然后展开一个服务器。 • （2）用鼠标右键单击“数据库”，在弹出的快捷选单中选择“所有任务”选项，再选择“还原数据库”选项，打开如图9.10所示的对话框。

29. （3）在“还原为数据库”下拉列表中选择要还原的目标数据库，该数据库可以是不同于备份数据库的另一个数据库，即可以将一个数据库的备份还原到另一个数据库中。

30. 可以从下拉列表中选择已存在的数据库，也可以输入一个新的数据库名称，SQL Server2000将自动新建一个数据库，并将数据库备份还原到新建的数据库中。 • （4）选择一种还原的方式，可以是“数据库”、“文件组或文件”或“从设备”方式。选择第一种方式可以很方便地还原数据库，但这种当时要求要还原的备份必须在msdb数据库中保存了备份历史记录，在其他服务器上创建的备份在msbd数据库中没有记录，则此时将一个服务器上做制作的数据库备份还原到另一个服务器上时，不能使用数据库的还原方式，而只能使用备份设备还原。

31. （5）当选择“数据库”方式时，在“参数”栏的“显示数据库备份”下拉列表框中选择要还原的数据库备份。在“还要还原的第一个备份”下拉列表框中列出了要还原的数据库在msdb中极力的所有备份历史，从中选择在某一日期下备份的数据库备份文件。（5）当选择“数据库”方式时，在“参数”栏的“显示数据库备份”下拉列表框中选择要还原的数据库备份。在“还要还原的第一个备份”下拉列表框中列出了要还原的数据库在msdb中极力的所有备份历史，从中选择在某一日期下备份的数据库备份文件。 • （6）在“常规”选项卡的下部。显示了一个备份的列表，表中列出了从“要还原的第一个备份”开始的对该数据库进行的所有备份，对每个备份都显示了该备份的类型、备份的时间、大小、备份的物理名称等信息。用户可以从中选择所要还原的备份，方法是选中备份前面的“还原”复选框。默认状态下，SQL Server会为用户选择最新的全库备份、最后一次差异备份以及最后一次差异备份之后进行的所有日志备份。

32. （7）如果在上一步中选择了日志备份，那么可以选中“时点还原”复选框，指定还原在某一时间以前的事务日志。（7）如果在上一步中选择了日志备份，那么可以选中“时点还原”复选框，指定还原在某一时间以前的事务日志。 • （8）在“选项”选项卡中设置还原的选项，如图9.11所示。“将数据库文件还原为”选项列表中给出了要还原的数据库文件的原文件和将要还原成的文件名，在默认状态下，将要还原成的文件名与原文件名相同，用户可以将其改为其他的名字。

33. （9）在“恢复完成状态”选项中选择下列单选按钮之一。（9）在“恢复完成状态”选项中选择下列单选按钮之一。 • ·选择第一个按钮，数据库恢复完成后数据库能继续运行，但无法还原其他事务日志。 • ·选择第二个按钮，数据库恢复完成后数据库不能再运行，但能还原其他事务日志。 • ·选择第三个按钮，数据库恢复完成后数据库自动为只读方式，不能对其进行修改，但只能还原其他事务日志。

34. （10）单击“确定”按钮，开始还原操作。 • 如果在如图9.10所示的对话框中选择的还原类型是“从设备”，则会出现如图9.12所示的对话框。

35. 单击“选择设备”按钮，然后在如图9.13所示的对话框中单击“添加”按钮，选择要还原的备份文件，再单击“确定”按钮，即可开始还原数据库。单击“选择设备”按钮，然后在如图9.13所示的对话框中单击“添加”按钮，选择要还原的备份文件，再单击“确定”按钮，即可开始还原数据库。

36. 9.3.2使用Transact-SQL语句还原数据库 1．恢复整个数据库 • 恢复整个数据库时，RESTORE语句的语法格式如下： • RESTORE DATABASE 数据库名FROM备份设备名 • [WITH[FILE=n][，NORECOVERY|RECOVERY][，REPLACE]] • 在上述语法格式中FILE=n指出从设备上的第几个备份中恢复。例如，数据库在同一备份设备上作了两次备份，若恢复第一个备份时使用“FILE=1”选项；若恢复第二个备份时使用“FILE=2”选项。

37. RECOVERY指定在数据库恢复完成后SQL Server回滚被恢复的数据库中所有未完成的事务，以保持数据库的一致性。在恢复后，用户就可以访问数据库了。所以RECOVERY选项用于最后一个备份的恢复。如果使用NORECOVERY选项，那么SQL Server不回滚所有未完成事务，在恢复结束后，用户不能访问数据库。所以当不是对要恢复的最后一个备份做恢复时，应使用NORECOVERY选项。例如，要恢复一个全库备份、一个差异备份和一个日志备份，那么应先用NORECOVERY选项恢复全库备份和差异备份，最后用RECOVERY选项恢复日志备份。默认选项为RECOVERY。

38. REPLACE指明SQL Server创建一个新的数据库，并将备份恢复到这个新数据库，如果服务器上已经存在一个同名的数据库，则原来的数据库被删除。 • 从全库备份中恢复数据和从差异备份中恢复数据都使用上述语法格式。 • 恢复事务日志 • 恢复事务日志所使用的语法格式如下： • RESTORE LOG数据库名FROM备份设备名 • [WITH[FILE=N][，NORECOVERY|RECOVERY]] • 在上述语法格式中，选项[FILE=N]，[NORECOVERY|RECOVERY]的含义与恢复整个数据库语法格式中的选项意义相同。

39. 恢复部分数据库 • SQL Server2000提供了恢复部分数据库的功能，可以将某一部分的备份恢复到相应的数据库文件中。例如，误删除了一个数据库中的某个表，可以只恢复包含该表的那一部分数据。部分恢复操作是以数据库文件还是以数据库文件组为单位工作，取决于备份时以文件为单位还是以文件组为单位，主文件和文件组总是需要被恢复另外还要恢复指定的文件和它对应的文件组。

40. 从整个数据库的备份中指定只恢复某几个文件，所以语法格式如下：从整个数据库的备份中指定只恢复某几个文件，所以语法格式如下： • RESTORE DATABASE 数据库名 FILE=文件名 | FILEGROUP=文件组名FROM备份设备名 • [WITH PARTIAN [，FILE=n][，NORECOVERY][，REPLACE]] • 其中PARTIAL表示此次恢复只恢复数据库的一部分。选项FILE=‘文件组’指定要恢复的数据库文件或文件组的名称，采用“FILE=文件名”或“FILEGROUP=文件组”的形式。NORECOVERY、REPLACE的含义与前面相同。

41. 9.4直接复制文件的备份和还原 • 数据库的备份和还原，除了在企业管理器中进行或使用Transact-SQL语句完成之外，还有直接复制拷贝文件的方法。在SQL Server中与一个数据库相对应的数据文件（.mdf）和日志文件（.ldf）都是Windows系统中的普通磁盘文件，用普通的方法直接进行文件复制操作就能够达到备份数据库的目的。

42. 不过，复制的数据库文件并不会自动地连接到SQL Server2000系统中，还必须通过执行系统存储过程sp_attach_db对所有制作的数据库副本进行还原，也就是将数据库附加到服务器上， • 系统存储过程sp_attach_db的语法格式为： • [EXECUTE] sp_attach_db‘数据库名称’，‘文件名’[,…16] • 在上述语法格式中，数据库名称为要连接到SQL Server中的数据库名，该名称必须是唯一的，如果指定的数据库已经存在，应当予以删除或者另行指定一个数据库名；文件名为包含路径在内的数据库文件名，可以是主要文件（.mdf）、非主要文件（.ndf）和事务日志文件（.ldf）,最多可以指定16个文件名。

43. 【例9．8】SQL Server2000中提供了一个示例数据库Northwind，下面以该数据库为例，说明如何使用系统存储过程sp_attach_db将数据库附加到服务器中。 • 步骤 • （1）打开Windows资源管理器，选取于SQL Server2000示例数据库Northwind相关的两个文件，即主要数据库文件Northwind.mdf和事务日志文件Northwind.ldf，然后在“编辑”选单中选择“复制”命令。 • （2）在本地硬盘或具有写权限的网络盘上选择一个目标文件夹，并在“编辑”选单中选择“粘贴”命令，执行文件复制操作，将上一步中所选取的两个文件复制到目标文件夹中，如E:\Databackup。如果因源文件正在使用而无法复制，可以首先停止SQL Server服务，等完成文件复制操作以后重新启动该服务。

44. （3）在查询分析器中，输入以下语句： • EXECUT sp_attach_db ‘Northwind’，‘E:\Databackup\Northwind.mdf’，‘E:\Databackup\Northwind.ldf’ • 执行上述语句之后，此时可以在查询分析器中看到，数据库Northwind已经被附加到SQL Server系统中。使用上述方法可以在现有数据文件和日志文件基础上创建新的数据库，不仅能够为一个现有的数据库制作出多个副本，也可以实现在不同SQL server服务器之间移动传递数据库的目的。