数据库的备份与恢复.ppt

第10章 数据库的备份与恢复,主要内容,10.1 数据库备份与还原 10.2 数据库分离与附加 10.3 数据库导入导出 10.4 数据库快照 10.4.1 数据库快照概述 10.4.2 数据库快照的使用 10.4.3 利用数据库快照进行数据库恢复,数据库备份 备份概述 备份类型 创建备份设备 数据库备份的执行 数据库还原 恢复（还原）概述 恢复（还原）数据库概述 恢复（还原）数据库,10.1 数据库备份与还原,备份概述：,microsoft sql server 2005提供了高性能的备份和还原机制。数据库备份可以创建备份完成时数据库内存在的数据的副本，这个副本能在遇到故障时恢复数据库。 这些故障包括：媒体故障，硬件故障，用户操作错误，自然灾害等。 数据库备份对于例行的工作（例如，将数据库从一台服务器复制到另一台服务器、设置数据库镜像）也很有用。,备份概述：,对sql server数据库或事务日志进行备份时，数据库备份记录了在进行备份这一操作时数据库中所有数据的状态，以便在数据库遭到破坏时能够及时地将其恢复。 sql server备份数据库是动态的，在进行数据库备份时，sql server允许其他用户继续对数据库进行操作。,数据库备份的几个基本概念,1、备份内容 数据库中数据的重要程度决定了数据恢复的必要和重要性，也就决定了数据是否以及如何备份。 备份内容分为：数据文件（主、次数据文件）、日志文件。 数据文件中所存储的系统数据库是确保sql server2005系统正常运行的重要依据，无疑系统数据库必须完全备份。,数据库备份的几个基本概念,2、由谁备份 下列角色可以做备份操作： （1）固定的服务器角色sysadmin（系统管理员） （2）固定的数据库角色db_owner（数据库所有者） （3）固定的数据库角色db_backupopererator（允许进行数据库备份的用户） 另外，还可以通过授权允许其他角色进行数据库备份。,数据库备份的几个基本概念,3、备份介质 备份介质是数据库备份到得目标载体，即备份到何处。这里允许两种类型的备份介质： （1）硬盘：最常用的备份介质。硬盘可以用于备份本地文件，也可以用于备份网络文件 （2）磁带：大容量的备份介质，磁带仅可用于备份本地文件。,数据库备份的几个基本概念,4、何时备份 对于系统数据库和用户数据库，其备份时机是不同的。 （1）系统数据库:当系统数据库master、msdb、model中的任何一个被修改过后，都要将其备份。 master：“数据库的数据库”，如若损坏，sql server 2005可能无法启动，并且用户数据库也可能无效，只能重装解决。 msdb、model:修改对其备份，以便在系统出现故障时恢复作业以及用户创建的数据库信息。 tempdb:不需要备份，它仅包含临时数据。,数据库备份的几个基本概念,4、何时备份 对于系统数据库和用户数据库，其备份时机是不同的。 （2）用户数据库。当创建数据库或加载数据库时，应备份数据库，当为数据库创建索引时，应备份数据库，以便恢复时节省时间。 当清理日志或执行不记日志的t-sql命令时，应备份数据库，这是因为若日志记录被清除或命令未记录在事务日志中，日志中将不包含数据库的活动记录，因此不能通过日志恢复数据库,数据库备份的几个基本概念,5、限制的操作 执行数据库备份时，允许用户对数据库继续操作，但不允许备份时做以下操作： 创建或删除数据库文件、创建索引、不计入日志的命令,数据库备份的几个基本概念,6、备份的方法 （1）只备份数据库 （2）备份数据库和事务日志 形成一下四种备份方法： 完整备份 事务日志备份 差异备份 文件或文件组备份,备份类型：,恢复模式中包含的备份类型：,以下情况应采用完整数据库备份 : 数据库较小 数据库具有很少的数据修改操作或是只读数据库时 如果采用完整数据库备份，则要定期清除事务日志,星期日,星期一,星期二,完整数据库备份策略：,使用差异备份策略的场合: 数据库频繁修改 需要进行最小化备份时 单独备份事务日志,星期一,星期二,差异备份策略：,事务日志文件用来记录数据库中的数据变化，备份了前一个日志备份中没有备份的所有日志记录，该备份可以恢复到故障点。 使用事务日志备份策略的场合: 修改频繁的数据库 完整备份耗时太长,星期日,星期一,事务日志备份策略：,数据库备份和差异数据库备份，统称数据库备份 （1）均包含还原数据库所需的日志记录，但是一般不使用，而是使用事务日志备份。 （2）包含的日志不影响事务日志，常将数据库备份和事务日志备份一起使用。,使用文件或文件组备份策略的场合: 超大型数据库 完整备份时间太长 单独备份事务日志 可能管理比较复杂,星期日,星期一,星期二,星期三,数据文件 1,数据文件 2,数据文件3,文件或文件组备份策略：,创建备份设备：,备份或还原操作中使用的磁带机或磁盘驱动器称为“备份设备”。 在创建备份时，必须选择要将数据写入的备份设备。 microsoft sql server 2005 可以将数据库、事务日志和文件备份到磁盘和磁带设备上。,（1）使用sql server 管理平台创建备份设备： 在sql server 管理平台中，选择想要创建备份设备的服务器，打开服务器对象文件夹。,（1）创建备份设备,（2）备份数据库,（2）使用系统存储过程创建备份设备： 在sql server 中，可以使用 sp_addumpdevice 语句创建备份设备，其语法形式如下： sp_addumpdevice device_type ,logical_name,physical_name,controller_type|device_status,device_type： 备份设备的类型 disk：磁盘文件作为备份设备，tape：磁带设备 logical_name： 在backup和restore语句中使用的备份设备的逻辑名称 physical_name： 备份设备的物理名称，必须包含完整路径。,（2）使用系统存储过程创建备份设备： 在sql server 中，可以使用 sp_addumpdevice 语句创建备份设备，其语法形式如下： sp_addumpdevice device_type ,logical_name,physical_name,controller_type|device_status,sp_addumpdevice ：将一个备份设备添加到sys.backup_devices目录视图中，可以通过该视图查看系统中的可用设备。,（2）使用系统存储过程创建备份设备：,例1、在d盘创建一个文件夹d:sqlserverback，创建一个名为mydiskdump的磁盘备份文件，其物理名称为d:sqlserverbackdump1.bak,（2）使用系统存储过程创建备份设备：,例2、创建一个名为tapedump1的磁带备份设备，其物理名称为.tape0,（3）使用系统存储过程删除备份设备：,当设备无用时，用存储过程sp_dropdevice可以删除设备 sp_dropdevice device,delfile,device:是数据库设备或备份设备的逻辑名称 delfile：指定是否删除物理备份设备文件，如果指定为delfile，则删除物理设备设备磁盘文件。,（3）使用系统存储过程删除备份设备：,例3、删除名为test_backup的磁盘备份设备,自己测试delfile选项，执行后到操作系统文件查看物理文件是否同时删除,（4）数据库备份的执行,t-sql实现数据库备份 1、数据库完整备份、差异备份的执行 2、文件或文件组备份的执行 3、事务日志备份的执行 使用向导实现数据库备份,1、数据库完整备份、差异备份的执行 backup语句的语法形式如下： backup database database_name to ,n with description=text ,diffeential , init | noinit ,name=backupsetname := logicalbackupdevicename|disk|tape=physicalbackupdevicename ,2、备份文件或文件组 backup语句的语法形式如下： backup database database_name ,f to ,n with description=text ,diffeential , init | noinit ,name=backupsetname := file=logicalfilename|filegroup=logicalfilegroup ,3、备份事务日志 backup语句的语法形式如下： backup log database_name to ,n with description=text , init | noinit , name=backupsetname , no_truncate , norecovery | standby=undofilename ,解释： database_name：指定备份的数据库名称 backup_device：备份设备 logicalbackupdevicename：逻辑设备名称 disk|tape=physicalbackupdevicename：允许在磁盘或 磁带设备上创建备份，允许backup前此名称可不存储。 description=text：备份描述文本 diffeential：差异备份 init ：覆盖所有备份集 noinit：本次备份追加到指定媒体集上，以保留现有备份。默认设置 name=backupsetname ：备份的名称，不指定为空,解释： no_truncate：不考虑数据库的状态立即执行备份 no_truncate :备份日志的尾部并使数据库处于正在恢复的状态。 standby=undofilename：备份日志的尾部并使数据库处于只读和备用状态。 fileorfilegroup：指定包含在数据库备份中的文件或文件组的逻辑名。可指定多个文件或文件组。 file=logicalfilename：给一个或多个包含在数据库备 份中的文件命名。 filegroup=logicalfilegroup：给一个或多个包含在 数据库备份中的文件组命名。在简单恢复模式下，只允 许对只读文件组执行文件组备份。,例10-5 将数据库blueskydb中的文件组usergroup2和usergroup3设为只读状态，然后对数据库blueskydb进行完全备份，将备份存储到名为mydiskdump的备份设备上，并覆盖所有的备份集。,做修改只是便于还原后观察状态,例10-6 将数据库blueskydb进行差异备份，将备份存储到名为mydiskdump的备份设备上，并将本次备份追加到指定的媒体集上。将日志备份到名为磁盘d:sqlserverbackdumplog.bak文件上，并覆盖所有的备份集。,进行差异备份必须设置数据库为完整恢复模式,恢复模式设置与查看,设置恢复模式 alter database set recovery full|simple| bulk_logged 查看恢复模式 select name, recovery_model_desc from sys.databases where name = blueskydb go,例10-7 将数据库blueskydb中的文件组usergroup1进行备份，将备份存储到d:sqlserverbackdump2.bak文件上，并且覆盖所有的备份集。将日志备份到名为mydiskdumplog,其物理名称为：d:sqlserverbackdumlog1.bak文件上，并覆盖所有的备份集。,4、使用备份向导 在sql server管理平台中，点击菜单中的模板资源管理器。展开backup，再双击backup database。 在连接到数据库引擎对话框中，填写连接信息，再单击连接。此时将打开一个新查询编辑器窗口，其中包含备份数据库模板的内容，如图所示。,数据库备份 备份概述 备份类型 创建备份设备 数据库备份的执行 数据库还原 恢复（还原）概述 恢复（还原）数据库概述 恢复（还原）数据库,10.1 数据库备份与还原,恢复（还原）概述：,场景： 你所在的企业的数据库库拥有一套完整的备份策略：每周日晚上执行完整备份，每天晚上执行差异备份，每天10点、12点、14点和16点执行事务日志备份。 某个周三的下午13点，用户报告说不能访问数据库。作为dba，你检查时发现数据库发生了严重的故障，存储数据库文件的硬盘无法工作。这时你将会如何处理？,解决办法： 你为服务器更换了新硬盘，使用存储在另外一个盘上的备份文件进行故障恢复。 先使用上周日晚的完整备份文件进行还原；再使用周二晚上的差异备份文件进行还原；最后使用当天中午12点的事务日志备份进行了还原。,恢复（还原）数据库概述：,数据库备份后，一旦系统发生崩溃或者执行了错误的数据库操作，就可以从备份文件中还原数据库。 数据库还原是指将数据库备份加载到系统中的过程。系统在还原数据库的过程中，自动执行安全性检查、重建数据库结构以及完成填写数据库内容。 sql server还原数据库时，根据数据库备份文件自动创建数据库结构，并且还原数据库中的数据。,恢复模式 （1）简单恢复模式 简略记录大多数事务，但没有日志备份，故只能恢复到上次数据库备份处，不能恢复到指定故障点或时刻。 （2）完整恢复模式 记录所有事务并保留所有的事务日志记录，直到将他们备份。可恢复到指定故障点或时刻。 （3）大容量日志恢复模式 简略记录大多数大容量操作，如创建索引或大容量加载，完整地记录其他事务。不能恢复到指定时刻，只允许恢复到事务的日志备份的结尾处。,由于数据库的还原操作是静态的，所以在还原数据库时，必须限制用户对该数据库进行其他操作，因而在还原数据库之前，首先要设置数据库访问属性。如图所示。,（1） 使用sql server管理平台还原数据库： 打开 sql server 管理平台，在数据库上单击鼠标右键，从弹出的快捷菜单中选择任务选项，再选择还原数据库命令，弹出还原数据库对话框，如图所示。,恢复（还原）数据库：,选中选项页框，进行其他选项的设置，如图所示,（2）使用 t-sql 语句还原数据库： 1、还原整个数据库 ，其语法形式如下： restore database databasename from with ,file=file_number ,move logical_file_name to operating_system_file_name ,replace ,norecovery|recovery|standby=undo_file_name := backup_device_name|disk|tape|pipe=temp_backup_device,（2）使用 t-sql 语句还原数据库： 2、还原部分数据库，其语法形式如下： restore database databasename from with partial ,file=file_number ,move logical_file_name to operating_system_file_name ,replace ,norecovery ,（2）使用 t-sql 语句还原数据库： 3、还原文件或文件组，其语法形式如下： restore database databasename ,.f from with ,file=file_number ,move logical_file_name to operating_system_file_name ,replace ,norecovery := file=logicalfilename | filegroup=logicalfilegroupname,（2）使用 t-sql 语句还原数据库： 4、还原事务日志，其语法形式如下： restore logdatabasename from with ,file=file_number ,move logical_file_name to operating_system_file_name ,replace ,norecovery|recovery|standby=undo_file_name ,说明： file=file_number:标示要还原的备份集。file_number=n指备份媒介中的第n个备份集. move logical_file_name to operating_system_file_name:将数据库还原到与备份时不同的位置。 norecovery：指定还原操作不回滚任何未提交的事务，这时数据库处于恢复状态，用户不能使用该数据库。 recovery：指定还原操作回滚任何未提交的事务，在恢复进程中可随时使用数据库，该参数默认。 standby=undo_file_name：指定一个备用文件，其位置存储在数据库的日志中，如果某个现有文件使用了指定的名称，该文件被覆盖，否则数据库会创建该文件。,说明： replace：指定即使存在另一个具有相同名称的数据库，sql也应该创建指定的数据库及其相关文件，将删除现有数据库，如没指定，则会进行安全检查，以防意外覆盖。 partial：指定还原主文件组和指定的次文件组的部分还原操作，默认为主文件组，其他需显示指定。,例10-8 利用备份文件d:sqlserverbackdump2.bak，恢复数据库blueskydb中的usergroup1，同时利用备份mydiskdumplog进行数据库blueskydb的日志恢复。,例10-9 利用备份设备mydiskdump中的”blueskydb完整备份”进行数据完全恢复，恢复后的数据库名为blueskydb。 利用备份设备mydiskdump中的”blueskydb差异备份”进行数据差异恢复，恢复后的数据库名为blueskydb。同时利用磁盘d:sqlserverbackdumplog.bak文件进行数据库blueskydb的日志恢复。,还原文件和文件组的方法：,从文件或文件组备份还原 当从文件或文件组进行还原时，必须应用创建文件备份后创建所有的事务日志； 若表和相关索引位于两个不同的文件组中，应将这两个文件组备份作为一个整体进行还原。,单独还原文件或文件组： 减少还原一个非常大的数据库所要的时间 当特定文件被意外删除或是损坏时恢复数据,10.4 数据库快照,数据库快照是 sql server 数据库（源数据库）的只读静态视图。 自创建快照那刻起，数据库快照在事务上与源数据库一致。 数据库快照始终与其源数据库位于同一服务器实例上。 当源数据库更新时，数据库快照也将更新。 因此，数据库快照存在的时间越长，就越有可能用完其可用磁盘空间。 给定源数据库中可以存在多个快照。 在数据库所有者显式删除每个数据库快照之前，该快照将一直保留。,10.4.1数据库快照概述,数据库快照在数据页级运行。在第一次修改源数据库页之前，先将原始页从源数据库复制到快照。快照将存储原始页，保留它们在创建快照时的数据记录。对要进行第一次修改的每一页重复此过程。对于用户而言，数据库快照似乎始终保持不变，因为对数据库快照的读操作始终访问原始数据页，而与页驻留的位置无关。 为了存储复制的原始页，快照使用一个或多个“稀疏文件”。最初，稀疏文件实质上是空文件，不包含用户数据并且未被分配存储用户数据的磁盘空间。随着源数据库中更新的页越来越多，文件的大小也不断增长。下图说明了两种相对的更新模式对快照大小的影响。更新模式 a 反映的是在快照使用期限内仅有 30% 的原始页更新的环境。更新模式 b 反映的是在快照使用期限内有 80% 的原始页更新的环境。,数据库快照 (database snapshot) 一个数据库（源数据库）的事务一致的只读静态视图。 源数据库 (source database) 对于数据库快照，指的是在其上创建快照的数据库。数据库快照与源数据库相关。数据库快照必须与数据库在同一服务器实例上。此外，如果数据库因某种原因而不可用，则它的所有数据库快照也将不可用。 稀疏文件 (sparse file) ntfs 文件系统提供的文件，需要的磁盘空间要比其他文件格式少很多。稀疏文件用于存储复制到数据库快照的页面。首次创建稀疏文件时，稀疏文件占用的磁盘空间非常少。随着数据写入数据库快照，ntfs 会将磁盘空间逐渐分配给相应的稀疏文件。,优点与缺点：,优点 1.数据库快照最大的优点就在于它可以作为一个报告数据库。因为数据库快照是主数据库的一个只读副本，对一个数据库快照执行报告能够大大的减少加载时间。 2.数据库快照只需要几个特征值就可以恢复源数据库。 缺点 1.数据库快照最主要的缺点就是它只能在sql server企业版上使用。我们都知道，企业版的成本很高，因此不是每一个人都能够使用这一项功能。 2.数据库快照是依附于主数据库，因此不能单独使用。 3.数据库快照中不支持全文检索。,10.4.2数据库快照的使用,create database database_snapshot_name on ( name = logical_file_name, filename = os_file_name ) ,.n as snapshot of source_database_name,说明：,source_database_name 是源数据库 logical_file_name 是引用该文件时在 sql server 中使用的逻辑名称 os_file_name 是创建该文件时操作系统使用的路径和文件名 data