数据库恢复SQL Server 认证模式.docx

寒山sql数据库修复中心 / 第8章 SQL Server 系统管理 8.1安全性管理 8.2数据库备份 8.3 数据库恢复 8.4 数据复制 8.5 事务 8.6 锁 8.7 SQL Server 自动化管理 8.8 数据的导入与导出 主要内容 SQL Server 2005的安全机制数据库管理系统相关知识 ?备份与恢复数据库SQL SERVER 2005的新特性 ?了解事务和锁 ?掌握SQL Server 2005数据导入与导出的方法 8.1安全性管理 8.1.1 认证模式与访问权限 SQL Server 2005支持两种身份验证模式： Windows身份验证模式和混合身份验证模式 1. 2. Windows认证模式：采用Windows认证进程来确认用户的身 份。 SQL Server认证模式:用户必须提供SQL Server管理员为其提 供的登录名和登录密码，用户身份的验证由SQL Server自身完 成。 8.1.2 用户管理 用户账号与登录账号:一个合法的登录账号只表明该账号 通过了Windows认证或SQL Server认证，但不能表明其 可以对数据库数据和数据对象进行某种或某些操作。一个 登录账号总是与一个或多个数据库用户账号相对应，这样 才可以访问数据库。 用户账号操作: 1. 2. 3. 添加用户 删除用户 授权 8.1.3 角色管理 角色提供了一种把用户汇集成一个单元，以便进行 许可管理的方法。一个角色包含了许多成员，这些成员 都继承了角色所拥有的许可。在SQL Server中，角色有 两种主要类型：服务器角色和数据库角色。 1）Public角色 2）固定服务器角色 3）固定数据库角色 4）用户自定义的角色 5）应用程序角色 8.1.4 架构 架构是形成单个命名空间的数据库实体的集合。命名 空间是一个集合，其中每个元素的名称都是唯一的。 在 SQL Server 2005 中，架构独立于创建它们的数据 库用户而存在。可以在不更改架构名称的情况下转让架构 的所有权。 完全限定的对象名称现在包含四部分： server.database.schema.object。 默认架构：用于解析未使用其完全限定名称引用的对象的 名称。 8.2数据库备份 为了防止计算机出现灾难事故而导致数据库被破坏， 对数据库的备份工作是不可忽视的，要采取一定的备份策 略来保证数据库的安全，一旦计算机发生事故时，能够采 取及时的恢复操作。 8.2.1 备份简介 备份就是制作数据库结构和数据的副本，以便在数据 库遭到破坏的时候能够修复数据库。会造成数据库破坏的常 见原因包括： ? 用户不正确的操作破坏了数据或数据被病毒破坏 ? 硬件故障，如磁盘损坏等 ? 遭遇自然灾害 ? 遭遇盗窃等意外事故 备份的内容不仅包括用户的数据库内容，还包括系统数 据库的内容。 8.2.2 备份许可及介质 可以备份数据库的角色成员有： ?固定的服务器角色sysadmin ?固定的数据库角色db_owner ?固定的数据库角色db_backupoperator?用户自定义角色，并且授权这些角色执行备份数据库的 许可 备份介质： 1. 磁盘 2. 磁带 3. 网络 8.2.3 备份类型 数据库备份包括完整备份和完整差异备份。完整备份 包含数据库中的所有备份，并且可以用作完整差异备份的 “基准备份”。完整差异备份仅记录自前一完整备份发生 更改的数据扩展盘区数据。在SQL server中，主要的备份 类型有： 1. 完整备份 2. 完整差异备份 3. 部分备份 4. 部分差异备份 5. 文件和文件组备份 6. 文件差异备份 7. 事务日志备份 8.2.4执行备份 1完整备份 2. 完整差异备份 打开SQL Server Management Studio，在【对象资源管理 器】中展开【数据库】，选择【教务管理】数据库，右键 单击鼠标，在弹出的快捷菜单上选择【任务】、【备份】 选项。进入数据库备份窗口。在【备份数据库教务管 理】窗口中，选择备份类型为【差异】，在下面的【目标】 窗格中，默认为【备份到：磁盘】，指定备份的文件位置， 例如指定为D:diffBackUp，然后单击【确定】按钮。 3使用backup命令备份 例8-6 使用backup命令完整备份 “教务管理”数据库。 Backup database 教务管理 To disk=f:MSSQLBACKUP教务管理备份.bak ?例8-7 使用backup命令对 “教务管理”数据库进行差异备份。 Backup database 教务管理 To disk=f:MSSQLBACKUP教务管理差异备份1.bak with differential 8.2.5 备份策略及性能考虑 在对数据库进行备份时可以选择某种备份策略来进行备份。 选择备份策略，一方面要基于数据库本身特点来考虑，另一 方面要考虑性能。 ?数据库本身比较小的情况：若此数据库的数据是只读的 或很少修改，则选用完整数据库备份策略是合适的。 ?频繁修改的数据库：完整数据库备份和事务日志备份相 结合的策略是比较流行的备份选择。 ?需要很短的时间内恢复数据库：可以选择差异备份的策 略。 ?海量数据的数据库：选用文件或文件组备份比较适合。 8.3 数据库恢复 8.3.1 恢复的概念及模式 恢复是在数据库遭遇破坏后从一个或多个备份中还原数 据库。还原的操作是在“恢复模式”下进行的。在SQL Server 2005数据库管理系统中有三种恢复模式： ? 简单恢复模式 ? 完整恢复模式 ? 大容量日志恢复模式 8.3.2 恢复数据库注意事项 准备工作： 1、恢复的权限 2、检查系统 3、检查文件 4、限制用户对数据库的访问，并备份事务日志 8.3.3 在SQL Server Management Studio 中恢复数据库 1还原完整备份 2还原完整差异备份： 还原完整差异备份的步骤与还原完整备份的步骤大体相同，只是在进入【还原数据 库】的【常规】页窗口后，指定用于还原的备份集为先前进行了备份的差异备份集 8.3.4 用T-SQL语句恢复系统数据库 RESTORE DATABASE语句用于恢复数据库，其语法格式为： RESTORE DATABASE database_name | database_name_var FROM ,n WITH CHECKSUM | NO_CHECKSUM , CONTINUE_AFTER_ERROR | STOP_ON_ERROR , FILE = file_number | file_number , KEEP_REPLICATION , MEDIANAME = media_name | media_name_variable , MEDIAPASSWORD = mediapassword | mediapassword_variable , MOVE logical_file_name TO operating_system_file_name ,n , PASSWORD = password | password_variable , RECOVERY | NORECOVERY | STANDBY = standby_file_name | standby_file_name_var 8.3.5 建立自备份的维护计划 SQL Server 2005的数据库维护计划可以指定作业运行 的时间，多个作业可以根据一个计划运行，多个计划也可以 应用到一个作业。根据作业运行的时间的不现可将计划类型 分为以下如图8.22所示的四种： 8.4 数据复制 8.4.1 复制的概念 将数据和数据库对象从一个数据库复制和分发到另一个数据库，然后在数据 库间进行同步，以维持一致性。 1发布服务器 发布服务器是一种数据库实例，它通过复制向其他位置提供数据。 2分发服务器 分发服务器也是一种数据库实例，它起着存储区的作用，用于复制与一个或多个 发布服务器相关联的特定数据。 3订阅服务器 订阅服务器是接收复制数据的数据库实例。 4项目 项目用于识别发布中包含的数据库对象。 5发布 发布是来自一个数据库的一个或多个项目的集合，将多个项目分组成一个发布更 便于指定一组作为一个单元复制的、具有逻辑关系的数据库对象和数据。 6订阅 订阅是把发布副本传递到订阅服务器的请求。 8.4.2复制类型 Microsoft SQL Server提供3种复制类型： 1事务性复制 2合并复制 3快照复制 8.4.3复制代理 复制使用许多称为代理的独立程序执行与跟踪更改和分发数据关联的任务。默 认情况下，复制代理作为SQL Server代理安排的作业运行。 1SQL Server 代理 SQL Server 代理保存并安排复制中使用的代理，并提供运行复制代理的简便方法。 SQL Server 代理还控制和监视复制之外的操作。 2快照代理 快照代理准备已发布表的架构和初始数据文件以及其他对象、存储快照文件并记录 分发数据库中的同步信息。 3日志读取器代理 它将发布服务器上的事务日志中标记为复制的事务移至分发数据库中。 4分发代理 它将初始快照应用于订阅服务器，并将分发数据库中保存的事务移至订阅服务器。 5合并代理 合并代理与合并复制一起使用。它将初始快照应用于订阅服务器，并移动和协调所 发生的增量数据更改。 6队列读取器代理 队列读取器代理与包含排队更新选项的事务性复制一起使用。该代理运行于分发服 务器，并将订阅服务器上所做更改移回至发布服务器。 8.4.4 发布服务器与发布 默认情况下，分发服务器与发布服务器是同一台服务 器（本地发布服务器），也可以是与发布服务器不同的服 务器（远程发布服务器）。创建本地发布服务器的配置步 骤： 1. 启动【新建发布向导】 2. 选择要发布数据的数据库 3. 选择发布类型 4. 选择发布项目 5. 设置筛选表行和快照代理，创建快照并设置快照运行 时间 6. 设置快照代理安全性 7. 完成发布向导，创建本地发布 8.4.5 订阅服务器与订阅 订阅是对发布中的数据和数据库对象的副本进行请求。 订阅定义将接收哪个发布以及接收的时间和位置。创建本 地订阅的步骤： 1. 通过【订阅向导】生成本地订阅 2. 选择发布服务器中的数据库和发布 3. 选择发布代理位置 4. 选择订阅服务器，指定订阅数据库 5. 设置分发代理安全性，指定同步此订阅时，运行分发 代理服务器的域或计算机账户 6. 指定代理的同步计划 7. 初始化订阅 8. 创建订阅 8.5 事务 8.5.1 事务及工作原理 1事务 事务是用户定义的一个数据库操作序列，这些 操作要么全做要么全不做，是一个不可分割的工作单位。 2事务特性 原子性(Atomicity)、一致性(Consistency)、 隔离性(Isolation)和持续性(Durability)。这四个特性简称为 ACID特性。 3SQL Server通过记录事务日志保持事务的持久性。 数据库系统一般采用记录日志和恢复机制来保证事务的 一致性；采用并发控制机制来保证多个事务的交叉运行不影 响这些事务的原子性。 4事务的工作原理 （COMMIT,ROLLBACK,UNDO,REDO） 8.5.2执行事务及执行模式 1事务的执行 应用程序主要通过指定事务启动和结束的时间来控制 事务。 2事务的类型 根据运行模式，SQL Server 2005将事务分为4种类型： 自动提交事务、显式事务、隐式事务和批处理事务。 1） 显式事务 显式事务指每个事务通过API函数或通过发出Transact-SQL BEGIN TRANSACTION 语句来显式启动事务，以COMMIT或ROLLBACK语句显式结束。 2） 自动提交事务 自动提交事务是指每条语句都是一个事务。 3） 隐式事务 隐式事务是指在前一个事务完成时新事务隐式启动，但每个事务仍以COMMIT或 ROLLBACK语句显式完成。 4） 批范围的事务 只适用于多个活动的结果集 (MARS)，在 MARS 会话中启动的 Transact-SQL 显 式或隐式事务将变成批范围的事务。 8.5.3 编写事务 控制事务用到的T-SQL语句: 1BEGIN TRANSACTION 语句 2COMMI T TRANSACTION语句 3ROLLBACK TRANSACTION 或 ROLLBACK WORK 语句 例8-12定义一个事务，将所有外聘教师的考评成绩定为良好，并提交该 事务。 BEGIN TRANSACTION EX_teacher_grade UPDATE 考评 SET 得分情况=良好 WHERE 档案类型=外聘 GO COMMIT TRANSACTION GO 8.5.4 事务保存点 保存点提供了一种机制，用于回滚部分事务。在创建 保存点后，可以执行 ROLLBACK TRANSACTION savepoint_name 语句以回滚到保存点，而不是回滚到事 务的起点。 语法格式为： SAVE TRAN | TRANSACTION savepoint_name | savepoint_variable ; 8.5.5事务隔离级别 隔离级别定义一个事务必须与其他事务所进行的资源 或数据更改相隔离的程度。 Microsoft SQL Server 2005 的隔离级别由低到高包括： 未提交读（隔离事务的最低级别，只能保证不读取物理上 损坏的数据） 已提交读（数据库引擎的默认级别） 可重复读 可序列化（隔离事务的最高级别，事务之间完全隔离） Transact-SQL使用 SET TRANSACTION ISOLATION LEVEL 语句设置隔离级别 8.6 锁 8.6.1 锁及其作用 当多个用户并发地存取数据库时会产生多个事务同时存 取同一数据的情况，如果对并发操作不加控制就可能会存取 和存储不正确的数据，破坏事务的一致性和数据库的致性。 由多用户并发事务执行所导致的数据不一致可能会导致 下面几种情况： 1丢失修改 2不可重复读 3脏读 (Dirty Read) 锁是防止其他事务访问指定的资源控制、实现并发控制的一种主要 手段。封锁是指一个事务在对某个数据对象操作之前，先向系统提出请 求，对其加锁，在事务结束之后释放锁。在事务释放它的锁之前，其他 事务不能更新此数据对象。 8.6.2 锁的对象与模式 1锁的对象 SQL Server具有多粒度锁定，允许一个事务锁定不同 类型的资源。锁的对象包括下表所列的资源： 资源 RID KEY PAGE EXTENT HOBT TABLE FILE APPLICATION METADATA ALLOCATION_UNIT DATABASE 说明 用于锁定堆中的单个行的行标识符。 索引中用于保护可序列化事务中的键范围的行锁。 数据库中的 8 KB 页，例如数据页或索引页。 一组连续的八页，例如数据页或索引页。 堆或B树。保护索引或没有聚集索引的表中数据页堆的锁。 包括所有数据和索引的整个表。 数据库文件。 应用程序专用的资源。 元数据锁。 分配单元。 整个数据库。 8.6.2 锁的对象与模式 2锁模式 1） 共享锁 共享锁（S 锁）允许并行事务读取同一种资源。 2）排他锁 排他锁（X 锁）可以防止并发事务对资源进行访问。 3） 更新锁 更新锁（U 锁）可以防止常见的死锁。一次只有一个事务可以获得资源 的更新锁（U 锁）。如果事务修改资源，则更新锁（U 锁）转换为排 他锁（X 锁）。 4） 意向锁 数据库引擎使用意向锁来保护共享锁（S 锁）或排他锁（X 锁）放置在 锁层次结构的底层资源上。 意向锁有两种用途： 防止其他事务以会使较低级别的锁无效的方式修改较高级别资源。 ? 提高数据库引擎在较高的粒度级别检测锁冲突的效率。 8.6.3 死锁问题 死锁是有两个或以上的事务处于等待状态，每个事务 都在等待另一个事务解除封锁，它才能继续执行下去，结 果任何一个事务都无法执行，这种现象就是死锁。 下面两种情况下可能会出现死锁： 1两个事务同时锁定了两个单独的对象，并且第一个事务要求在另外一个 事务锁定的对象上获得一锁； 2在一个数据库中有若干个长时间运行的事务执行并操作。 降低死锁的原则： 1按同一顺序访问对象 2避免事务中的用户交互 3保持事务简短并在一个批处理中 4使用低隔离级别 8.6.4 查看锁信息 Microsoft SQL Server 2005 提供的获取有关数据库引 擎实例中的当前锁活动的信息的方法： 使用 SQL Server Profiler，可以指定用来捕获有关跟 踪中锁事件的信息的锁事件类别。在系统监视器中，可以 从锁对象指定计数器来监视数据库引擎实例中的锁级别。 操作步骤： 1. 2. 3. 4. 进入SQL Server Profiler窗口 在SQL Server Profiler窗口中，选择【文件】菜单【新建跟踪】。 在【跟踪属性】对话框中设置跟踪，设置跟踪名称和使用模板，可以 将跟踪指定到文件存储，也可以保存到指定的表。 完成跟踪的建立后，即可在SQL Server Profiler窗口中查处跟踪的事 件。 8.7 SQL Server 自动化管理 8.7.1 作业