第16章 备份和恢复数据库.ppt

第16章备份和恢复数据库 内容 16 1备份数据库16 2恢复数据库 16 1备份数据库 为什么要进行数据备份 备份内容谁来做备份工作 什么时候需要备份 如何进行备份 SQLServer的备份类型备份策略的规划 为什么要进行数据备份 为保证数据库系统连续正常运转 以及从故障状态尽快恢复 防止数据丢失 错误的更新 病毒 物理损害 自然灾害 一旦出现问题 能够根据备份进行恢复 数据转移的一种方式 备份内容 用户数据库系统数据库事务日志 谁来做备份工作 Sysadmin服务器角色成员db owner数据库角色成员db backupoperator数据库角色成员 什么时候需要备份 系统数据库 修改之后立即备份 当修改了master数据库时当修改了msdb数据库时当修改了model数据库时用户数据库 一般是周期性做数据库创建之后 马上备份日志被清空之后 马上备份执行了不记入日志的操作之后 马上备份 系统数据库master Master数据库中存储着SQLServer服务器配置参数 用户登录帐户 系统存储过程等重要内容 所以需要备份 执行任何影响master数据库的操作后 都要备份该数据库 这些操作包括 1 数据库操作类 CREATEDATABASE ALTERDATABASE DROPDATABASE 2 服务器管理类 sp addserver sp addlinkedserver sp dropserver sp configure sp serveroption 3 服务器登录帐户管理类 sp addlogin sp addremotelogin sp droplogin sp dropremotelogin sp grantlogin sp password 4 备份设备管理 sp addumpdevice sp dropdevice 5 数据库配置类 sp dboption sp renamedb Msdb数据库支持SQLServer代理 为任务调度提供存储空间 所以要定期备份 用户数据库中包含了用户加载的数据信息 是数据库应用程序操作的主体 所以要定期备份 事务日志记录用户对数据库的更改操作 利用日志可以精确地恢复数据库 所以要定期备份 其他 如何进行备份 备份到备份设备上设备类型 TapeDisk如果备份到磁盘上 则可以备份到备份设备上 永久备份设备 需事先创建 也可以直接备份到磁盘上 备份设备 永久备份设备 在备份之前需要预先建立可以利用企业管理器创建永久备份设备 16 1创建备份设备第一步 16 2创建备份设备第二步 利用T SQL创建备份设备 永久备份设备也可由sp addumpdevice创建 已创建的永久备份设备记录在master数据库的sysdevices表中 格式 sp addumpdevice disk tape logical name physical name logical name 备份设备逻辑名physical name 备份设备物理名 文件路径名 disk磁盘设备 tape磁带设备在系统表sysdevices中没有相应记录的备份设备称为临时备份设备 是一个磁盘文件 例子 下面语句创建一个磁盘备份设备 USEmasterGOEXECsp addumpdevice disk 教学管理 FULL d server mssql backup 教学管理备份设备 dat GO 备份类型 完全备份 海量备份 差异备份日志备份 完全备份 海量备份 对数据库中的全部信息进行备份 备份不影响数据库的活动 动态备份 同时备份 在备份过程中发生的一切活动未提交事务的执行信息 差异备份 备份 上次完全备份之后发生变化的数据 差异备份过程中发生的活动未提交事务的执行信息差异备份比完全备份节约时间 日志备份 备份从上次日志备份之后的日志记录默认时 日志备份完成后要截断日志 解决日志文件越来越大的问题 须将数据库的还原模型设置为 完全方式 或 大容量日志记录 的方式 才能备份日志 在数据库选项中设置故障还原模式 使用企业管理器进行备份 下面我们简单介绍一下用企业管理器进行备份 备份到设备或磁盘文件 使用企业管理器进行备份 使用企业管理器进行备份 使用企业管理器进行备份 使用企业管理器进行备份 使用T SQL命令进行备份 1 数据库备份BACKUPDATABASE 格式为 BACKUPDATABASE dbname dbname var TOdump device1 dump device2 dump device32 WITHoption n dbname dbname var指定要备份的数据库 dump device1 dump device2 dump device32 指定逻辑备份设备名 options指定一些备份时使用的选项 例如 DESCRIPTION text text var 给出备份描述文本 最长255个字符 DIFFERENTIAL 增量备份方式 PASSWORD password password var 设置密码 恢复时须提供正确密码 INIT NOINIT INIT 重写媒体 NOINIT 追加媒体 NAME backup set name backup set name var 说明备份集名称 使用T SQL命令进行备份 SQLServer采用在线备份方式进行备份 在备份期间 用户不能执行下列操作 数据文件操作 如在ALTERDATABASE中使用ADDFILE或REMOVEFILE子句 改变数据库或数据文件长度 如 DBCCSHRINKFILE DBCCSHRINKDATABASE 执行CREATEINDEX语句 执行批拷贝 SELECTINTO WRITETEXT或UPDATETEXT等非日志操作语句 使用T SQL命令进行备份 例如 USEmasterGOEXECsp addumpdevice disk 教学管理 FULL d server mssql backup 教学管理备份设备 dat GOBACKUPDATABASE教学管理to教学管理 FULLGO 使用T SQL命令进行备份 2 备份数据库文件或文件组备份文件或文件组时 需要将数据库还原模式设置为完全模式 BACKUP语句的语法格式为 BACKUPDATABASE dbname dbname var FILE 逻辑文件名 逻辑文件名变量 FILEGROUP 逻辑文件组名 逻辑文件组名变量 TOdump device1 dump device2 dump device32 WITHoption n 使用T SQL命令进行备份 例如 下面语句把pubs数据库中的数据文件pubs备份到文件E backup pubs dat中 将数据库还原模式设置为完全模式ALTERDATABASEpubsSETRECOVERYFULLGOBACKUPDATABASEpubsFILE pubs TODISK E backup pubs dat 使用T SQL命令进行备份 3 备份事务日志备份事务日志时 BACKUP语句的语法格式为 BACKUPLOG dbname dbname var WITH NO LOG TRUNCATE ONLY TOdump device1 dump device2 dump device32 WITHoption n WITHNO LOG或WITHTRUNCATE ONLY 说明不做日志备份 只删除事务日志中的无效部分 并且截断日志 释放它们所占用的磁盘空间 这时不需指定备份设备 因为并不保存事务日志 使用T SQL命令进行备份 在数据库备份期间不能备份事务日志 但在数据文件备份的同时可以备份事务日志 例如 pubs数据文件备份正在执行 BACKUPLOGpubsTODISK e backup pubs log dat 在以下几种情况下 应禁止备份数据库日志 1 在数据库完整备份以前 因为SQLServer在完成事务日志备份时将自动截断事务日志 2 截断了事务日志 但没有再次执行数据库备份 3 向数据库添加文件或从中删除文件之后没有再次备份数据库 使用T SQL命令进行备份 备份策略 制定备份策略时 应考虑以下内容 备份内容和方式备份频率备份数据存储介质备份内容和方式组合可以如下 FullDBFullDB logFullDB DifferentialDB Log 备份频率 确定备份频率要考虑两个因素 1 允许丢失的工作量 越频繁 可能丢失的工作量越小 2 数据库的事务量 太频繁则会影响事务的执行 具体可考虑以下问题 1 如果没有数据库 企业的业务能支持多久 如果数据库对每天的事务处理都至关重要 那么就必须经常备份 2 对不变的历史数据可以只备份一次 但要多复制几个备份 以免备份介质损坏而造成数据丢失 3 执行恢复需要多长时间 是海量备份的恢复省时 还是增量备份的恢复省时 如果是长期的增量备份 显然是海量备份恢复省时 如果只有一 两次增量备份 一般是增量备份恢复省时 所以 可以考虑按周做海量备份 每天做增量备份等 4 如果数据库的使用频率和更新频率非常高 可以考虑每天做一次海量备份 做几次增量备份 相反 如果数据库的更新频率不太高 只需要每周 甚至每月做一次海量备份 备份频率 选择备份介质 备份介质类型包括 磁盘 磁带等 磁盘 容量小 价格高 存取快磁带 容量大 价格低 存取慢 数据一致性检查 在执行备份前应该检查数据库中数据的一致性 这样才能保证所备份数据是正确的 1 检查点机制检查点机制是自动把已完成事务对数据库的修改从缓冲区写入数据库文件的一种手段 每次启动检查点进程时 它把所有脏页 缓冲区中被修改的页面 数据写进数据库文件 自动检查点进程执行的时间间隔是SQLServer根据系统活动情况和recoveryinterval 最长恢复时间 配置选项计算出来的 可用CHECKPOINT语句强制执行一个检查点检查 CHECKPOINT语句的权限属于sysadmin db owner或db backupoperator角色的成员 且不能授予他人 2 DBCC 检查数据库的一致性在执行备份前 应该执行DBCC语句检查数据库的一致性 从而排除错误 用于检查数据一致性的DBCC语句包括 1 DBCCCHECKDB 检查数据库中数据的一致性 2 DBCCCHECKALLOC或DBCCNEWALLOC 检查数据库页面分配的正确性 3 DBCCCHECKCATALOG 检查系统表的一致性 4 DBCCCHECKCONSTRAINTS 检查约束的一致性 5 DBCCCHECKFILEGROUP 检查文件组的一致性 6 DBCCCHECKIDENT 检查标识值的一致性 7 DBCCCHECKTABLE 检查表的一致性 数据一致性检查 使用完全备份的例子 创建数据库 实施完全备份 实施完全备份 实施完全备份 每天12 00进行完全备份 出错时间 Tuesday上午11 50 丢失1天工作量 Sunday Monday Tuesday 使用全库 日志备份例子 每天12 30进行完全备份 每天11 45 12 00 12 15进行日志备份 出错时间 Monday11 50 丢失5分钟工作量 使用完全 差异 日志备份例子 Monday Tuesday 完全备份 差异备份 差异备份 Monday上午11 30进行完全备份 以后每15分钟进行一次日志备份 每1小时进行一次差异备份 16 2恢复数据库 自动恢复手工恢复 自动恢复 1 每次启动SQLServer时自动执行恢复 2 检查每个数据库 先修复master数据库 之后是model数据库 在清除tempdb数据库中的临时数据后 再修复msdb pubs distribution等数据库 最后修复用户数据库 3 重做已提交的事务 回滚未提交的事务 手工恢复 当系统出现故障时 由系统管理员或数据库所有者执行RESTORE命令进行恢复 数据库的备份权限可由数据库所有者授予其他用户 但恢复操作权限不能授予他人 包括四个方面 恢复前的准备恢复顺序恢复用户数据库恢复系统数据库 恢复前的准备 设置限制访问数据库选项 右击数据库 属性 选项 限制访问 如日志没有损坏 则备份日志 恢复顺序 1 恢复最近的全量数据库备份 2 恢复最近的差异数据库备份 如有 3 恢复自差异数据库备份以后的所有日志备份 按备份的先后顺序 恢复顺序 Restore语句 RESTORE语句有以下几种语法格式 使用数据库备份来恢复整个数据库 使用数据文件备份来恢复数据库中的部分内容 数据库中的文件或文件组 使用事务日志备份来恢复数据库 RESTOREDATABASE 数据库名 数据库名变量 FROM n WITH RESTRICTED USER FILE 备份号 备份号变量 PASSWORD 口令 口令变量 MEDIANAME 介质名 介质名变量 MEDIAPASSWORD 介质口令 介质口令变量 MOVE 逻辑文件名 TO OS文件名 n KEEP REPLICATION NORECOVERY RECOVERY STANDBY 撤销文件名 NOREWIND REWIND NOUNLOAD UNLOAD REPLACE RESTART STATS percentage Restore语句 恢复整个数据库 RESTOREDATABASE 数据库名 数据库名变量 n FROM n WITH PARTIAL FILE 备份号 备份号变量 PASSWORD 口令 口令变量 MEDIANAME 介质名 介质名变量 MEDIAPASSWORD 介质口令 介质口令变量 MOVE 逻辑文件名 TO OS文件名 n NORECOVERY NOREWIND REWIND NOUNLOAD UNLOAD REPLACE RESTRICTED USER RESTART STATS percentage Restore语句 恢复部分数据库 RESTOREDATABASE 数据库名 数据库名变量 n FROM n WITH RESTRICTED USER FILE 备份号 备份号变量 PASSWORD 口令 口令变量 MEDIANAME 介质名 介质名变量 MEDIAPASSWORD 介质口令 介质口令变量 MOVE 逻辑文件名 TO OS文件名 n NORECOVERY NOREWIND REWIND NOUNLOAD UNLOAD REPLACE RESTART STATS percentage Restore语句 恢复数据库文件或文件组 Restore语句 用事务日志恢复数据库 RESTORELOG 数据库名 数据库名变量 FROM n WITH RESTRICTED USER FILE 备份号 备份号变量 PASSWORD 口令 口令变量 MOVE 逻辑文件名 TO OS文件名 n MEDIANAME 介质名 介质名变量 MEDIAPASSWORD 介质口令 介质口令变量 KEEP REPLICATION NORECOVERY RECOVERY STANDBY 撤销文件名 NOREWIND REWIND NOUNLOAD UNLOAD REPLACE RESTART STATS percentage STOPAT date time date time var STOPATMARK mark name AFTERdatetime STOPBEFOREMARK mark name AFTERdatetime 1 数据库名 数据库名变量 待恢复的数据库名称 2 备份设备或文件名称 其格式为 备份设备逻辑名称 备份设备逻辑名称变量 DISK TAPE 物理备份设备名称 物理备份设备名称变量 3 说明数据库中待恢复的数据文件或文件组的逻辑名称 其格式为 FILE 文件逻辑名称 文件逻辑名称变量 FILEGROUP 文件组逻辑名称 文件组逻辑名称变量 next Restore语句 参数说明 4 WITH子句中的各种参数用于设置数据库恢复操作选项 这些参数包括 RESTRICTED USER 说明恢复后的数据库只允许db owner dbcreator或sysadmin成员访问 即数据库恢复后将设置为受限访问模式 FILE 备份号 备份号变量 在一个备份介质上 可能存在多个备份数据集 使用FILE参数指出恢复时使用第几个备份数据集 PASSWORD 口令 口令变量 说明备份集所使用的密码 如果备份时添加了密码 则从备份集执行数据恢复操作时必须提供密码 Restore语句 参数说明 不讲 MEDIANAME 介质名 介质名变量 指出备份集合的介质名称 必须与备份卷上的介质名称相匹配 否则 RESTORE语句将禁止执行 若不给出此参数 RESTORE语句将跳过该项检查 MEDIAPASSWORD 介质口令 介质口令变量 指出介质集口令 若在格式化介质集时设置了口令 则访问该介质集上的所有备份集都必须提供口令 MOVE 逻辑文件名 TO OS文件名 将 逻辑文件名 指定的数据文件移动到 OS文件名 指定的文件位置 默认时 逻辑文件名 被恢复到它原来的位置 NORECOVERY 恢复后不回滚未提交的事务 RECOVERY 恢复后回滚未提交的事务 Restore语句 参数说明 不讲 STANDBY 撤销文件名 指出撤消文件名 使用该文件可以撤消已经执行的恢复操作 若未指出NORECOVERY RECOVERY或STANDBY参数时 则默认RECOVERY KEEP REPLICATION 要求恢复操作在将发布的数据库恢复到创建它的服务器以外的其它服务器时 保留复制设置 若指定了该选项 则不能选择NORECOVERY选项 UNLOAD 恢复完成后自动绕带 并卸载磁带 NOUNLOAD 恢复完成后不自动卸带 这是默认设置 NOREWIND 恢复完成后使磁带保持打开状态 REWIND 恢复完成后释放磁带并倒带 Restore语句 参数说明 不讲 REPLACE 关闭恢复前的安全检查 重建所有数据库及相关文件 不管与其同名的数据库文件是否存在 若未指定此选项 则恢复前要进行安全检查 若发现下列情况 将放弃恢复操作 服务器上存在同名数据库 数据库名称与备份集中记录的数据库名称不同 RESTART 从上次中断点开始重新执行恢复操作 使用此选项时 其他参数设置应与上次恢复时一样 STATS percentage 说明每完成多少恢复工作量显示一个统计信息 默认时 每恢复10 返回一统计值 STOPAT date time date time var 只恢复指定时刻之前的数据库内容 只适用于从日志备份中恢复数据库 Restore语句 参数说明 不讲 STOPATMARK mark name AFTERdatetime 将数据库恢复到mark name指定的事务标记处 包括mark name指定的事务 STOPBEFOREMARK mark name AFTERdatetime 将数据库恢复到mark name指定的事务标记之前 即不包括mark name指定的事务 若无AFTERdatetime 恢复将在mark name指定的第一个事务标记处停止 若有AFTERdatetime 恢复将执行到datetime时间点之后的第一个事务标记处 Restore语句 参数说明 不讲 恢复用户数据库 恢复用户数据库时SQLServer执行以下操作 1 所有备份数据被拷贝到数据库 2 数据库备份操作时所有已写入数据库但未完成的事务被回滚 从而保持数据库中数据的一致性 下面举例说明怎样用RESTORE恢复用户数据库 例1 从备份设备中恢复教学管理数据库RESTOREDATABASE教学管理FROM教学管理 FULL 例2 从全量备份和增量备份中恢复教学管理数据库 设备中第5个备份集是增量备份 RESTOREDATABASE教学管理FROM教学管理 FULLWITHNORECOVERY 恢复后不回滚未提交的事务RESTOREDATABASE教学管理FROM教学管理 FULLWITHFILE 5 恢复用户数据库 例3 从备份文件中恢复数据库中的数据文件pubsRESTOREDATABASEpubsFILE pubs FROMDISK E backup pubs dat 例4 把pubs数据库恢复到2002年12月1日下午6 00以前的状态 RESTOREDATABASEpubsFROMpubsDeviceWITHNORECOVERY 恢复后不回滚未提交的事务RESTORELOGpubsFROMpubsDeviceWITHRECOVERY 恢复后回滚未提交的事务STOPAT 2002 12 16 00PM 恢复用户数据库 系统数据库控制SQLServer的运行 当它们受到损坏时 轻则影响系统的性能 重则造成系统崩溃 使SQLServer无法启动 系统数据库的恢复方法较特殊 不同于一般数据库 下面分别介绍master和msdb的恢复方法 恢复系统数据库 恢复master数据库 master数据库的恢复步骤为 1 若master损坏严重 已致使SQLServer无法启动 则要运行rebuildm exe程序来重构ma