2022年数据库的备份还原.ppt

*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,本章内容,12.1 数据库的备份,12.2 数据库的恢复,12.3 数据导入导出,12.4 分离与附加用户数据库,第12章 数据库的备份与恢复,12.1 数据库的备份,12.1.1 数据库备份概述,12.1.2 创建和删除备份设备,12.1.3 备份数据库,第12章 数据库的备份与恢复,12.1.1 数据库备份概述,备份是对SQL Server数据库或事务日志进行的复制，数据库备份记录了在进行备份操作时数据库中所有数据的状态，如果数据库因意外而损坏，这些备份文件将在数据库恢复时被用来恢复数据库。,12.1 数据库的备份,1.备份内容,数据库需备份的内容可分为:,系统数据库主要包括master、msdb和model数据库，它们记录了重要的系统信息，是确保系统正常运行的重要依据，必须完全备份。,用户数据库是存储用户数据的存储空间集，通常用户数据库中的数据依其重要性可分为关键数据和非关键数据。对于关键数据则是用户的重要数据，不易甚至不能重新创建，必须进行完全备份。,事务日志记录了用户对数据的各种操作，平时系统会自动管理和维护所有的数据库事务日志。相对于数据库备份，事务日志备份所需要的时间较少，但恢复需要的时间比较长。,12.1.1 数据库备份概述,2.备份设备,备份设备是用来存储数据库、事务日志或文件和文件组备份的存储介质。备份设备可以是硬盘、磁带或命名管道(逻辑通道)。,本地主机硬盘和远程主机的硬盘可作为备份设备，备份设备在硬盘中是以文件的方式存储的。,SQL Server使用物理设备名称或逻辑设备名称来标识备份设备。,物理备份设备是操作系统用来标识备份设备的名称。这类备份设备称为临时备份设备，其名称没有记录在系统设备表中，只能使用一次。,逻辑备份设备是用来标识物理备份设备的别名或公用名称，以简化物理设备的名称。这类备份设备称为永久备份设备，其名称永久地存储在系统表中，可以多次使用。,12.1.1 数据库备份概述,3.备份频率,数据库备份频率一般取决于修改数据库的频繁程度以及一旦出现意外，丢失的工作量的大小，还有发生意外丢失数据的可能性大小。,在正常使用阶段，对系统数据库的修改不会十分频繁，所以对系统数据库的备份也不需要十分频繁，只要在执行某些语句或存储过程导致SQL Server对系统数据库进行了修改的时候备份。,如果在用户数据库中执行了添加数据、创建索引等操作，则应该对用户数据库进行备份。如果清除了事务日志，也应该备份数据库。,12.1.1 数据库备份概述,数据导入导出工具用于在不同的SQL Server服务器之间传递数据，也用于在SQL Server 与其他数据库管理系统（如Access、Visual FoxPro、Oracle等）或其他数据格式（如电子表格或文本文件）之间交换数据。,由于恢复数据库与备份数据库之间往往存在较长的时间差，难以记住备份设备和备份文件及其所备份的数据库，需要对这些信息进行查看。,sp_addumpdevice的语法格式为：,完全恢复模型的优点是可以恢复到任意即时点，这样数据文件的丢失和损坏不会导致工作损失，但是如果事务日志损坏，则必须重新做最新的日志备份后进行的修改。,打开SQL Server管理平台，在对象资源管理器窗口中选择要分离的数据库，如Sales数据库，单击鼠标右键，在弹出的快捷菜单中选择“任务/分离”菜单项。,BLOCKSIZE=blocksize|blocksize_var,STATS=percentage,MEDIAPASSWORD=mediapassword|mediapassword_var,提供了3种数据库恢复模型：简单恢复、完全恢复、大容量日志记录恢复。,2 创建和删除备份设备,如果在用户数据库中执行了添加数据、创建索引等操作，则应该对用户数据库进行备份。,sp_dropdevice语句的语法格式为：,数据导入即从外部将数据导入到SQL Server某个数据表中。,RESTORE LOG Sales,KEEP_REPLICATION,4.数据库备份的类型,完全备份。完全备份将备份整个数据库，包括用户表、系统表、索引、视图和存储过程等所有数据库对象。适用于数据更新缓慢的数据库。,事务日志备份。事务日志记录数据库的改变，备份的时候只复制自上次备份事务日志后对数据库执行的所有事务的一系列记录。,差异备份。差异备份只记录自上次数据库备份后发生更改的数据，差异备份一般会比完全备份占用更少的空间。,文件和文件组备份。当数据库非常庞大时，可执行数据库文件或文件组备份。这种备份策略使用户只恢复已损坏的文件或文件组。而不用恢复数据库的其余部分，所以，文件和文件组的备份及恢复是一种相对较完善的备份和恢复过程。,12.1.1 数据库备份概述,12.1.2 创建和删除备份设备,进行数据库备份时，必须创建用来存储备份的备份设备。,创建和删除备份设备可以使用企业管理器和系统存储过程sp_addumpdevice、sp_dropdevice实现。,12.1 数据库的备份,12.1.2 创建和删除备份设备,1使用SQL Server管理平台创建备份设备,(1)在SQL Server管理平台的“对象资源管理器”中，展开服务器树，选择“服务器对象”节点并展开，在其下的“备份设备”节点上单击鼠标右键，从弹出的快捷菜单上选择“新建备份设备”。,12.1.2 创建和删除备份设备,（2）在出现的“备份设备”窗口，“设备名称”文本框中输入新设备的逻辑名称，如Sales_d。在下面的“文件”文本框中显示的是一个默认的文件名及其路径，用户可以对它进行修改。,（3）设置好后，单击“确定”按钮，即可创建备份设备。所创建的备份设备可在“备份设备”节点下看到。,12.1.2 创建和删除备份设备,2.使用sp_addumpdevice创建备份设备,sp_addumpdevice的语法格式为：,sp_addumpdevice,devtype,=device_type,logincalname,=logincal_name,physicalname,=physical_name,cntrltype,=controller_type|,devstatus,=device_status,12.1.2 创建和删除备份设备,例12-1 使用系统存储过程创建备份设备,test_backup。,USE Sales,GO,EXEC sp_addumpdevice DISK,test_backup,d:test_backup.bak,本例添加一个逻辑名称为test_backup的磁盘备份设备，物理名称为d:test_backup.bak。,12.1.2 创建和删除备份设备,例12-2 添加网络磁盘备份设备。,USE Sales,GO,EXEC sp_addumpdevice DISK,netdevice,servernamesharenamefilename.ext,本例添加一个远程磁盘备份设备，并命名其逻辑名称为netdevice。,12.1.2 创建和删除备份设备,3使用SQL Server管理平台删除备份设备,使用SQL Server管理平台删除备份设备的操作步骤如下：,（1）打开SQL Server管理平台，在“对象资源管理器”中展开“数据库服务器”“服务器对象”“备份设备”。,（2）在“备份设备”节点下，选择要删除的设备，鼠标右键单击该设备，从弹出菜单中选择“删除”命令即完成删除操作。,12.1.2 创建和删除备份设备,4.使用sp_dropdevice删除备份设备,sp_dropdevice语句的语法格式为：,sp_dropdevice logicalname=device,delfile=delfile,各选项含义如下：,(1)logicalname=device：数据库设备或备份设备的逻辑名称，该名称存储在系统表中。,(2)delfile=delfile：指出是否应该删除物理备份设备文件。如果将其指定为DELFILE，则表示删除物理备份设备的磁盘文件。,12.1.2 创建和删除备份设备,例12-3 使用系统存储过程删除例12-1创建的备份设备test_backup。,USE Sales,GO,EXEC sp_dropdevice test_backup,12.1.2 创建和删除备份设备,12.1.3 备份数据库,1使用SQL Server管理平台备份数据库,（1）打开SQL Server管理平台，在对象资源管理器中，展开“服务器树”“数据库”，在需要备份的数据库名称上点击鼠标右键，在弹出的快捷菜单上选择“任务/备份”命令，打开“备份数据库”窗口。,（2）在“备份数据库”窗口的“常规”选项卡上，“数据库”下拉列表框中可以更改待备份的数据库；选择备份的类型，如果是第一次备份，应该选择“完全”备份；在“备份集”名称文本框中可设置此备份的名称；“备份集过期时间”0表示永远过期；“目标”中可添加或删除备份设备。,（3）设置完成后，单击“确定”按钮开始备份。,12.1 数据库的备份,12.1.3 备份数据库,2使用Transact-SQL语句BACKUP备份数据库,使用Transact-SQL语句BACKUP可以对整个数据库、数据库文件及文件组、事务日志进行备份。,12.1.3 备份数据库,3.使用BACKUP备份数据库,BACKUP语句的语法格式为：,BACKUP DATABASE|LOG,database_name|database_name_var,n ,TO,n,WITH,BLOCKSIZE=blocksize|blocksize_var,DESCRIPTION=text|text_var,DIFFERENTIAL,EXPIREDATE=date|date_var|RETAINDAYS=days|days_var,PASSWORD=password|password_var,INIT|NOINIT,MEDIADESCRIPTION=text|text_var,MEDIANAME=media_name|media_name_var,MEDIAPASSWORD=mediapassword|mediapassword_var,NAME=backup_set_name|backup_set_name_var,NO_TRUNCATE,NORECOVERY|STANDBY=undo_file_name,NOSKIP|SKIP,RESTART,STATS=percentage,12.1.3 备份数据库,例12-4 使用Transact-SQL语句备份数据库,12.1.3 备份数据库,(1)数据库完全备份,将数据库Sales备份到一个磁盘文件上，备份设备为物理设备。,BACKUP DATABASE Sales TO DISK=D:Sales.BAK,将数据库Sales完全备份到逻辑备份设备back1上。,BACKUP DATABASE Sales TO back1,若将Sales数据库分别备份到back2、back3上，可使用“，”将备份设备分隔。,BACKUP DATABASE Sales TO back2,back3,(2)数据库差异备份,在BACKUP DATABASE语句中使用WITH DIFFERENTIAL项以实现数据库差异备份。,将Sales数据库差异备份到一个磁盘文件上。,BACKUP DATABASE Sales TO Disk=D:Salesbk.bakWITH DIFFERENTIAL,将Sales数据库差异备份到备份设备back4上。,BACKUP DATABASE Sales TO back4 WITH DIFFERENTIAL,12.1.3 备份数据库,(3)事务处理日志备份,将Sales数据库的事务日志备份到备份设备back4上。,BACKUP LOG Sales TO back4,注意：,当数据库被损坏时，应使用WITH NO_TRUNCATE选项备份数据库。该选项可以备份最近的所有数据库活动，SQL Server将保存整个事务日志。,(4)备份数据文件和文件组,在BACKUP DATABASE语句中使用“FILE=逻辑文件”或“FILEGROUP=逻辑文件组名”来备份文件和文件组。,12.1.3 备份数据库,例11-5 将数据库Sales的数据文件和文件组备份到备份设备back4中,12.1.3 备份数据库,BACKUP DATABASE Sales,FILE=Sales_data1,FILEGROUP=fg1,FILE=Sales_data2,FILEGROUP=fg2,TO back4,BACKUP LOG Sales TO back4,本例将数据库Sales的数据文件Sales_data1、Sales_data2及文件组fg1、fg2备份到备份设备back4中。,12.2 数据库的恢复,12.2.1 数据库恢复模型,12.2.2 查看备份信息,12.2.3 恢复数据库,第12章 数据库的备份与恢复,12.2.1 数据库恢复模型,根据保存数据的需要和对存储介质使用的考虑，SQL Server提供了3种数据库恢复模型：简单恢复、完全恢复、大容量日志记录恢复。,12.2 数据库的恢复,(1)简单恢复模型,简单恢复模型可以将数据库恢复到上次备份处，但是无法将数据库还原到故障点或待定的即时点。它常用于恢复最新的完整数据库备份、差异备份。,简单恢复模型的优点是允许高性能大容量复制操作，以及可以回收日志空间。但是必须重组最新的数据库或者差异备份后的更改。,12.2.1 数据库恢复模型,(2)完全恢复模型,完全恢复模型使用数据库备份和事务日志备份提供将数据库恢复到故障点或特定即时点的能力。为保证这种恢复程度，包括大容量操作(如SELECT INTO、CREATE INDEX和大容量装载数据)在内的所有操作都将完整地记入日志。,完全恢复模型的优点是可以恢复到任意即时点，这样数据文件的丢失和损坏不会导致工作损失，但是如果事务日志损坏，则必须重新做最新的日志备份后进行的修改。,12.2.1 数据库恢复模型,(3)大容量日志记录恢复模型,12.2.1 数据库恢复模型,大容量日志记录恢复模型为某些大规模或大容量复制操作提供最佳性能和最少日志使用空间。在这种模型中，大容量复制操作的数据丢失程度要比完全恢复模型严重，因为在这种模型下，只记录操作的最小日志，无法逐个控制这些操作。它只允许数据库恢复到事务日志备份的结尾处，不支持即时点恢复。,大容量日志记录恢复模型的优点是可以节省日志空间，但是如果日志损坏或者日志备份后发生了大容量操作，则必须重做自上次备份后所做的更改。,12.2.2 查看备份信息,由于恢复数据库与备份数据库之间往往存在较长的时间差，难以记住备份设备和备份文件及其所备份的数据库，需要对这些信息进行查看。,需要查看的信息通常包括：备份集内的数据和日志文件、备份首部信息、介质首部信息。可以使用企业管理器和Transact-SQL语句查看这些信息。,12.2 数据库的恢复,（2）在“备份设备”属性窗口选择“媒体内容”选项卡，打开如图12-4所示的“媒体内容”窗口，在列表框中列出所选备份介质的有关信息。,12.2.2 查看备份信息,RESTORE HEADERONLY语句的格式为：,RESTORE HEADERONLY,FROM,WITH NOUNLOAD|UNLOAD,FILE=file_number,PASSWORD=password|password_var,MEDIAPASSWORD=mediapassword|mediapassword_var,:=,logical_backup_device_name|logical_backup_device_name_var,|DISK|TAPE=physical_backup_device_name|physical_backup_name_var,12.2.2 查看备份信息,2.使用Transact-SQL语句查看备份信息,12.2 数据库的恢复,12.2.3 恢复数据库,1使用SQL Server管理平台恢复数据库,（1）在SQL Server管理平台的“对象资源管理器”中，展开数据库文件夹，右击要进行还原的数据库图标，这里以Sales数据库为例，从弹出的快捷菜单中选择“任务/还原/数据库”选项，打开如图所示的“还原数据库”对话框。,12.2.3 恢复数据库,（2）在“还原数据库”对话框的“常规”选项卡中，“目标数据库”下拉列表框用于选择要还原的数据库；“目标时间点”文本框用于设置还原时间点，可以保留默认值，也可以通过单击旁边的浏览按钮打开“时点还原”对话框，选择具体的日期和时间，对于完整数据库备份恢复，只能恢复到完全备份完成的时间点；“还原的源”区域中的“源数据库”下拉列表框用于选择要还原的备份的数据库存的名称；“源设备”文本框用于设置还原的备份设备的位置；“选择用于还原的备份集”网格用于选择还原的备份。,12.2.3 恢复数据库,（3）选择“选项”选项卡，在其中进行还原选项和恢复状态的设置。其中，“覆盖现有数据库”复选框被选中表示恢复操作覆盖所有现有数据库及相关文件；“保留复制设置”复选框被选中表示将已发布的数据库还原到创建该数据库的服务器之外的服务器时，保留复制设置；“还原每个备份之前进行提示”复选框被选中表示在还原每个备份设置之前要求用户确认；“限制访问还原的数据库”复选框被选中表示还原后的数据库仅供db_owner、dbcreator或sysadmin的成员使用；“将数据库文件还原为”区域可选择数据文件和日志文件的路径。,12.2.3 恢复数据库,2.使用RESTORE恢复数据库,RESTORE语句的语法格式为：,RESTORE DATABASE|LOG,database_name|database_name_var,n,FROM ,n,WITH,RESTRICTED_USER,FILE=file_number|file_number,PASSWORD=password|password_var,MEDIANAME=media_name|media_name_var,MEDIAPASSWORD=mediapassword|mediapassword_var,MOVE logical_file_name TO operating_system_file_name,n,KEEP_REPLICATION,NORECOVERY|RECOVERY|STANDBY=undo_file_name,REPLACE,RESTART,STATS=percentage,STOPAT=date_time|date_time_var,|,STOPATMARK=mark_name AFTER datetime,|,STOPBEFOREMARK=mark_name AFTER datetime,12.2.3 恢复数据库,例12-8 从磁盘上的备份文件“D:Sales_back.bak”中恢复数据库Sales。,RESTORE DATABASE Sales FROM DISK=D:Sales_back.bak,12.2.3 恢复数据库,例12-9 将一个数据库备份和一个事务日志进行数据库的恢复操作。,RESTORE DATABASE Sales,FROM back1 WITH NORECOVERY,RESTORE LOG Sales,FROM back1 WITH NORECOVERY,12.2.3 恢复数据库,例12-10 恢复数据库Sales中指定数据文件Sales_data1。,RESTORE DATABASE Sales,FILE=Sales_data1,FROM back4,WITH NORECOVERY,12.3 数据导入导出,12.3.1 导入数据,12.3.2 导出数据,第12章 数据库的备份与恢复,12.3 数据导入导出,数据导入导出是指SQL Server数据库系统与外部系统之间进行数据交换的操作。,导入数据是从外部数据源中查询或指定数据，并将其插入到SQL Server的数据表中的过程。,导出数据是将SQL Server数据库中的数据转换为用户指定格式的数据过程，即将数据从SQL Server数据库中引到其他系统中去。,数据导入导出工具用于在不同的SQL Server服务器之间传递数据，也用于在SQL Server 与其他数据库管理系统（如Access、Visual FoxPro、Oracle等）或其他数据格式（如电子表格或文本文件）之间交换数据。,第12章 数据库的备份与恢复,12.3.1 导入数据,数据导入即从外部将数据导入到,SQL Server,某个数据表中。,需要指定外部数据类型，数据所在的地址和文件名或数据库中的哪个表，将要导入到,SQL Server 2005,中的哪个数据库中，用什么表来存储数据等内容。,12.3.1 导入数据,上机实验：将一个,Access,数据库中（,C:,中的,supplier,表）的数据导入到,Sales,数据库中的,supplier,表,第12章 数据库的备份与恢复,12.3.2 导出数据,数据导出是指从,SQL Server,数据库中导出数据到其他数据源中。,导出数据时需指定要导出的数据位于,SQL Server,哪个数据库的哪些表，给出将要导出到外部数据源名称和位置等信息。,12.3.2 导出数据,上机实验：将,SQL Server,数据库中,supplier,表（,sales,数据库）导出数据到,EXCEL,中。,12.4 分离与附加用户数据库,12.4.1 分离用户数据库,12.5.2 附加用户数据库,第12章 数据库的备份与恢复,12.4 分离与附加用户数据库,若数据库创建在C盘上，而C磁盘空间越来越满，需要将数据库移到别的驱动器上，或者希望将数据库从一台较慢的服务器移到另一台更快的服务器上，通过对数据库进行分离和附加操作，可以很快的完成这项任务。,在进行分离和附加数据库操作时，应注意以下几点：,（1）不能进行更新，不能运行任务，用户也不能连接在数据库上。,（2）在移动数据库前，为数据库做一个完整的备份。,（3）确保数据库要移动的目标位置及将来数据增长能有足够的空间。,（4）分离数据库并没有将其从磁盘上真正的删除。如果需要，可以对数据库的组成文件进行移动、复制或删除。,第12章 数据库的备份与恢复,12.4.1 分离用户数据库,1使用SQL Server管理平台分离用户数据库,打开SQL Server管理平台，在对象资源管理器窗口中选择要分离的数据库，如Sales数据库，单击鼠标右键，在弹出的快捷菜单中选择“任务/分离”菜单项。,第12章 数据库的备份与恢复,2使用Transact-SQL语句分离用户数据库,系统存储过程sp_detach_db可以分离数据库，其语法格式如下：,sp_detach_db dbname=dbname,skipchecks=skipchecks,KeepFulltextIndexFile=KeepFulltextIndexFile,第12章 数据库的备份与恢复,例12-11 用系统存储过程sp_detach_db分离Sales数据库。,sp_detach_db Sales,第12章 数据库的备份与恢复,12.4.2 附加用户数据库,1使用SQL Server管理平台附加用户数据库,（1）打开SQL Server管理平台，在对象资源管理器中右键单击“数据库”节点，从弹出的快捷菜单中选择“附加”命令，打开的“附加数据库”对话框。,（2）想要添加数据库，在“附加数据库”对话框单击“添加”按钮，打开“定位数据库文件”对话框，找到数据库的MDF文件并选择它，单击“确定”按钮。,（3）单击“确定”按钮，以重新附加数据库。移到到对象资源管理器中，此时可以看到数据库在列表的底部。,第12章 数据库的备份与恢复,1使用SQL Server管理平台附加用户数据库,第12章 数据库的备份与恢复,2使用Transact-SQL语句附加用户数据库,附加数据库的语法格式如下：,CREATE DATABASE database_name,ON ,n,FOR ATTACH WITH,|ATTACH_REBUILD_LOG,第12章 数据库的备份与恢复,2使用Transact-SQL语句附加用户数据库,例12-12 附加Sales数据库。,CREATE DATABASE Sales,ON(FILENAME=D:SQLServerMSSQL.1MSSQLDataSales.mdf),FOR ATTACH,第12章 数据库的备份与恢复,本章小结,（1）数据库备份和恢复是两个相对应的操作。备份是对数据库或事务日志进行复制，恢复是将数据库备份重新加载到系统中的过程。,（2）备份设备是指数据库备份到的目标载体，即备份到何处。在SQL Server 2005中允许使用两种类型的备份设备，分别为硬盘和磁带。,（3）SQL Server支持完全备份、事务日志备份、差异备份、文件和文件组备份4种数据库备份类型。提供了3种数据库恢复模型：简单恢复、完全恢复、大容量日志记录恢复。恢复模型决定总体备份策略。,（4）数据导入导出是把数据库中的数据引出到数据库之外的数据源或把数据库之外的数据源中的数据引入到数据库中。也是把数据从一个地方转移到另外一个地方，把一种类型的数据转换成另外一种类型的数据的技术。在SQL Server管理平台中使用导入导出向导可以实现数据的导入导出。,（5）对已建立好的数据库从服务器中分离出来，保证数据库的完整性和一致性；也可将分离出来的数据库重新附加到服务器中进行管理。,第12章 数据库的备份与恢复,