Sql-server备份与恢复.ppt

1、第9章 备份与恢复,9.1 备份和恢复概述,9.2 数据库备份,9.3 数据库恢复,9.4 附加数据库和数据库复制,9.1 备份和恢复概述,9.1.1 数据库备份概念 1备份内容 数据库中数据的重要程度决定了数据恢复的必要性与重要性，也就决定了数据是否及如何备份。数据库需备份的内容可分为数据文件（包括主要数据文件和次要数据文件）、日志文件两部分。 2由谁做备份 在SQL Server 2014中，具有下列角色的成员可以做备份操作： （1）固定的服务器角色sysadmin（系统管理员）。 （2）固定的数据库角色db_owner（数据库所有者）。 （3）固定的数据库角色db_backupopera

2、tor（允许进行数据库备份的用户）。,9.1.1 数据库备份概念,3备份介质 （1）硬盘：最常用的备份介质，可以用于备份本地文件，也可以用于备份网络文件。 （2）磁带：大容量的备份介质，磁带仅可用于备份本地文件。 4何时备份 （1）系统数据库。当系统数据库master、msdb和model中的任何一个被修改以后，都要将其备份。 master数据库包含了SQL Server 2014系统有关数据库的全部信息，删除master数据库，SQL Server 2014可能无法启动，并且用户数据库可能无效。 （2）用户数据库。当创建数据库或加载数据库时，应备份数据库。当为数据库创建索引时，应备份数据库，

3、以便恢复时能够大大节省时间。 5限制的操作 SQL Server 2014在执行数据库备份的过程中，允许用户对数据库继续操作，但不允许用户在备份时执行下列操作：创建或删除数据库文件；创建索引；不记日志的命令。,9.1.1 数据库备份概念,6备份方法 （1）完全数据库备份。这种方法按常规定期备份整个数据库，包括事务日志。当系统出现故障时，可以恢复到最近一次数据库备份时的状态，但自该备份后所提交的事务都将丢失。 （2）数据库和事务日志备份。这种方法不需很频繁地定期进行数据库备份，而是在两次完全数据库备份期间，进行事务日志备份，所备份的事务日志记录了两次数据库备份之间所有的数据库活动记录。当系统出现

4、故障后，能够恢复所有备份的事务，而只丢失未提交或提交但未执行完的事务。 （3）差异备份。差异备份只备份自上次数据库备份后发生更改的部分数据库，它用来扩充完全数据库备份或数据库和事务日志备份方法。对于一个经常修改的数据库，采用差异备份策略可以减少备份和恢复时间。差异备份比全量备份工作量小而且备份速度快，对正在运行的系统影响也较小，因此可以更经常地备份。经常备份将减少丢失数据的危险。 （4）数据库文件或文件组备份。这种方法只备份特定的数据库文件或文件组，同时还要定期备份事务日志，这样在恢复时可以只还原已损坏的文件，而不用还原数据库的其余部分，从而加快了恢复速度。,9.1.2 数据库恢复概念,1准备

5、工作 当系统发现出现了以下情况时，恢复操作将不进行： （1）指定要恢复的数据库已存在，但在备份文件中记录的数据库与其不同。 （2）服务器上数据库文件集与备份中的数据库文件集不一致。 （3）未提供恢复数据库所需的所有文件或文件组。 安全性检查是系统在执行恢复操作时自动进行的。恢复数据库时，要确保数据库的备份是有效的，即要验证备份介质，得到数据库备份的信息。这些信息包括： 备份文件或备份集名及描述信息。 所使用的备份介质类型（磁带或磁盘等）。 所使用的备份方法。 执行备份的日期和时间。 备份集的大小。 数据库文件及日志文件的逻辑和物理文件名。 备份文件的大小。 2执行恢复数据库的操作 可以提供使用

6、图形向导方式或T-SQL语句执行恢复数据库的操作。,9.2 数据库备份,9.2.1 创建备份设备 1创建永久备份设备 1）执行系统存储过程 （1）执行系统存储过程创建备份设备。 语法格式： sp_addumpdevice devtype = 设备类型 , logicalname = 逻辑名 , physicalname = 物理名 【例9.1】 在本地硬盘上创建一个备份设备。 USE pxscj GO EXEC sp_addumpdevice DISK, myBK1,d:Server2014myBKfile1.bak 所创建的备份设备的逻辑名是myBK1；所创建的备份设备的物理名是d:Serv

7、er2014myBKfile1.bak。 （2）执行系统存储过程删除备份设备。 当所创建的备份设备不再需要时，可以用系统存储过程sp_dropdevice删除它。例如： EXEC sp_dropdevice myBK1, DELFILE,9.2.1 创建备份设备,2）使用SMSS（“对象资源管理器”）创建永久备份设备 在“对象资源管理器”中展开“服务器对象”，选择“备份设备”。在“备份设备”窗口中显示已经创建的备份设备。右击鼠标，在弹出的快捷菜单中选择“新建备份设备”菜单项。 在打开的“备份设备”窗口（如图9.1所示）中分别输入备份设备的名称和完整的物理路径名，单击“确定”按钮，完成备份设备的

8、创建。,9.2.1 创建备份设备,2使用多个备份设备 SQL Server可以同时向多个备份设备写入数据，即进行并行的备份。并行备份将需备份的数据分别备份在多个设备上，这多个备份设备构成了备份集。如图9.2所示为在多个备份设备上进行备份以及由备份的各组成部分形成备份集。,9.2.2 以命令方式备份数据库：BACKUP DATABASE,1备份整个数据库 1）备份命令 语法格式： BACKUP DATABASE 数据库名 /*被备份的数据库名*/TO , . /*指出备份目标设备*/ MIRROR TO WITH INIT | NOINIT WITH NAME = 名称 ,9.2.2 以命令方式

9、备份数据库：BACKUP DATABASE,2）备份举例 【例9.2】 将数据库pxscj完全备份到myBK1逻辑设备中。 BACKUP DATABASE pxscj TO myBK1 执行结果如图9.3所示。,9.2.2 以命令方式备份数据库：BACKUP DATABASE,将数据库pxscj完全备份到备份设备myBK1，并覆盖该设备中原有的内容： BACKUP DATABASE pxscj TO myBK1 WITH INIT 将数据库pxscj完全备份到备份设备myBK1，执行追加的完全数据库备份，该设备上原有的备份内容都被保存： BACKUP DATABASE pxscj TO myB

10、K1 WITH NOINIT 执行结果如图9.4所示。,9.2.2 以命令方式备份数据库：BACKUP DATABASE,【例9.3】 将数据库pxscj备份到多个备份设备。 USE pxscj GO EXEC sp_addumpdevice DISK, myBK2, d:Server2014mybackupfile2.bak EXEC sp_addumpdevice DISK, myBK3, d:Server2014mybackupfile3.bak BACKUP DATABASE pxscj TO myBK2, myBK3 WITH NAME = pxscjbk 3）查看备份设备的内容 在

11、“对象资源管理器”中展开“服务器对象”“备份设备”，选定要查看的备份设备，右击鼠标，在弹出的快捷菜单中选择“属性”菜单项，在打开的“备份设备”窗口中显示所要查看的备份设备的内容。,9.2.2 以命令方式备份数据库：BACKUP DATABASE,2差异备份数据库 在进行差异备份时，SQL Server将备份从最近的完全数据库备份后数据库发生了变化的部分。 语法格式： BACKUP DATABASE 数据库名 READ_WRITE_FILEGROUPS , FILEGROUP = 文件组名 TO , MIRROR TO WITH DIFFERENTIAL 【例9.4】 创建临时备份设备并在所创建

12、的临时备份设备上进行差异备份pxscj数据库。 BACKUP DATABASE pxscj TO DISK =D:Server2014myBKDIF1.bak WITH DIFFERENTIAL,9.2.2 以命令方式备份数据库：BACKUP DATABASE,3备份数据库文件或文件组 当数据库非常大时，可以进行数据库文件或文件组的备份。 语法格式： BACKUP DATABASE 数据库名 , . /*指定文件或文件组名*/ TO , . MIRROR TO 其中： := FILE = 逻辑文件名 | FILEGROUP = 逻辑文件组名 ,9.2.2 以命令方式备份数据库：BACKUP D

13、ATABASE,【例9.5】 将test2数据库的数据文件test2_data1备份到test2_data1.bak中，将PRIMARY文件组备份到test2_gp.bak中。 EXEC sp_addumpdevice DISK, t2data1, d:Server2014test2_data1.bak EXEC sp_addumpdevice DISK, t2gp, d:Server2014test2_GP.bak GO BACKUP DATABASE test2FILE =test2_data1 TO t2data1 BACKUP DATABASE test2 FILEGROUP=PRIM

14、ARY TO t2gpGO,9.2.2 以命令方式备份数据库：BACKUP DATABASE,4事务日志备份 当进行事务日志备份时，系统进行下列操作： （1）将事务日志中从前一次成功备份结束位置开始，到当前事务日志结尾处的内容进行备份。 （2）标识事务日志中活动部分的开始，所谓事务日志的活动部分指从最近的检查点或最早的打开位置开始至事务日志的结尾处。 进行事务日志备份使用BACKUP LOG语句。语法格式： BACKUP LOG /*其余选项与数据库的完全备份BACKUP相同*/ WITH NORECOVERY | STANDBY = 撤销文件名, NO_TRUNCATE 【例9.6】 创建一

15、个命名的备份设备myLOGBK，并备份pxscj数据库的事务日志。 USE pxscj GO EXEC sp_addumpdevice DISK , myBKlog , d:Server2014myBKlog.bak BACKUP LOG pxscj TO myBKlog,9.2.3 以界面方式备份数据库,【例9.7】 以pxscj数据库备份到myBK1备份设备为例说明。 步骤如下： （1）在“对象资源管理器”中选择“服务器对象”“备份设备”“myBK1”，右击鼠标，在弹出的快捷菜单上选择“备份数据库”菜单项，进入“备份数据库-master”窗口。 （2）选择备份参数。 选择要备份的数据库名p

16、xscj。 “备份类型”栏保持“完整”。 “备份组件”栏保持“数据库”；如果选择“文件或文件组”，则可以在弹出的“选择文件或文件组”窗口中选择需要备份的文件或文件组。,9.2.3 以界面方式备份数据库,“备份到”保持“磁盘”，myBK1作为备份设备，如图9.5所示。,9.2.3 以界面方式备份数据库,（3）选择“备份设备”“myBK1”，按右键，选择“属性”，单击“介质内容”，系统显示如图9.6所示。,9.3 数据库恢复,9.3.1 以命令方式恢复数据库：RESTORE DATABASE SQL Server进行数据库恢复时，将自动执行下列操作以确保数据库迅速而完整地还原。 （1）进行安全检查

17、。安全检查是系统的内部机制，是数据库恢复时的必要操作，它可以防止由于偶然的误操作而使用了不完整的信息或其他数据库备份来覆盖现有的数据库。 当出现以下几种情况时，系统将不能恢复数据库： 使用与被恢复的数据库名称不同的数据库名去恢复数据库。 服务器上的数据库文件组与备份的数据库文件组不同。 需恢复的数据库名或文件名与备份的数据库名或文件名不同，那么SQL Server将拒绝此恢复过程。 （2）重建数据库。当从完全数据库备份中恢复数据库时，SQL Server将重建数据库文件，并把所重建的数据库文件置于备份数据库时这些文件所在的位置，所有的数据库对象都将自动重建，用户无须重建数据库的结构。,9.3.

18、1 以命令方式恢复数据库：RESTORE DATABASE,1恢复数据库的准备 在“对象资源管理器”中展开“服务器对象”，在其中的“备份设备”中选择欲查看的备份介质，右击鼠标，在弹出的快捷菜单中选择“属性”菜单项。在打开的“备份设备”窗口中单击“媒体内容”选项页，将显示所选备份介质的有关信息，如备份介质所在的服务器名、备份数据库名、备份类型、备份日期、到期日及大小等信息。 2恢复整个数据库命令 当存储数据库的物理介质被破坏，或整个数据库被误删除或被破坏时，就要恢复整个数据库。在恢复整个数据库时，SQL Server系统将重新创建数据库及与数据库相关的所有文件，并将文件存放在原来的位置。 语法格

19、式： RESTORE DATABASE 数据库名 /*指定被还原的目标数据库*/ FROM , . /*指定备份设备*/ WITH RECOVERY | NORECOVERY | STANDBY = 备用文件名 ,9.3.1 以命令方式恢复数据库：RESTORE DATABASE,【例9.8】 使用RESTORE语句从一个已存在的命名备份介质myBK1中恢复整个数据库pxscj。 为了检验效果，在恢复数据库之前，用户可以对pxscj数据库做一些修改，如删除其中一个表，以便确认是否恢复了数据库。 恢复数据库的命令如下： RESTORE DATABASE pxscjFROM myBK1WITH F

20、ILE=3, REPLACE 执行结果如图9.7所示。,9.3.1 以命令方式恢复数据库：RESTORE DATABASE,3恢复数据库的部分内容命令 应用程序或用户的误操作，如无效更新或误删表格等，往往只影响到数据库的某些相对独立的部分。在这些情况下，SQL Server提供了将数据库的部分内容还原到另一个位置的机制，以使损坏或丢失的数据可复制回原始数据库。 4恢复特定的文件或文件组 若某个或某些文件被破坏或被误删除，则可以从文件或文件组备份中进行恢复，而不必进行整个数据库的恢复。,9.3.1 以命令方式恢复数据库：RESTORE DATABASE,5恢复事务日志 使用事务日志恢复，可将数据

21、库恢复到指定的时间点。 语法格式： RESTORE LOG 数据库名 | 数据库名变量 , . FROM , . WITH RECOVERY | NORECOVERY | STANDBY = 备用文件名 | 备用文件名变量 | , 执行事务日志恢复必须在进行完全数据库恢复之后。以下语句是从备份介质pxscjBK1进行完全恢复数据库pxscj后，再进行事务日志事务恢复，假设已经将pxscj数据库的事务日志备份到备份设备pxscjLOGBK1中。 RESTORE DATABASE pxscjFROM pxscjBK1WITH NORECOVERY, REPLACE GO RESTORE LOG p

22、xscjFROM pxscjLOGBK1,9.3.1 以命令方式恢复数据库：RESTORE DATABASE,6恢复到数据库快照 可以使用RESTORE语句将数据库恢复到创建数据库快照时的状态。此时，恢复的数据库会覆盖原来的数据库。 语法格式： RESTORE DATABASE 数据库名 FROM DATABASE_SNAPSHOT = 数据库快照名,9.3.1 以命令方式恢复数据库：RESTORE DATABASE,【例9.9】 创建pxscj数据库的快照，并将数据库恢复到创建该快照时的状态。 首先创建pxscj数据库的快照： CREATE DATABASE pxscj_1ON(NAME=p

23、xscj,FILENAME=d:Server2014pxscj_1.mdf) AS SNAPSHOT OF pxscj GO 然后，对数据库做一些修改，以确定数据库是否恢复。恢复数据库的语句如下： USE pxscj GO RESTORE DATABASE pxscj FROM DATABASE_SNAPSHOT=pxscj_1,9.3.2 以界面方式恢复数据库,【例9.10】 以界面方式恢复pxscj数据库。 在“对象资源管理器”选择“数据库”，右击鼠标，在弹出的快捷菜单中选择“还原数据库”菜单项，进入“还原数据库-pxscj”窗口。选择参数后，系统显示如图9.8所示。,9.3.2 以界面方

24、式恢复数据库,选择参数的方法如下： 在“源”中，选择“设备”，然后在“设备”行后单击“”，系统显示“选择备份设备”对话框，如图9.9所示。,9.4 附加数据库和数据库复制,9.4.1 附加数据库 在“对象资源管理器”中右击“数据库”，选择“附加”选项，进入“附加数据库”窗口，如图9.10所示。,9.4.2 数据库复制,在SQL Server中，可以使用“复制数据库向导”将数据库复制或转移到另一个服务器中。使用“复制数据库向导”前需要启动SQL Server代理服务，可以使用SQL Server配置管理器来完成。进入SQL Server配置管理器后，双击SQL Server代理服务，弹出“SQL Server代理属性”对话框，单击“启动”按钮，启动该服务后就可以使用“复制数据库向导”了。,