MSSQL2000资料.doc

此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 MSMS SQLSQL ServerServer 资料编号 资料编号 1 11 1 2005 11 11 第一章第一章 安装安装 1 1 1 1 安装要求 1 1 2 服务帐户 1 1 3 身份验证模式 1 1 4 授权模式 1 1 5 排序规则 1 1 6 多实例安装 2 第二章第二章 连接连接 2 2 第三章第三章 服务器服务器 3 3 3 1 基础知识 3 3 2 内存架构 3 3 3 查询分析架构 4 3 4 服务器设置 5 3 5 服务器一般管理 7 第四章第四章 用户与权限用户与权限 8 8 4 1 基础知识 8 4 2 身份验证 8 4 3 权限验证 9 4 4 安全模型 10 4 5 操作脚本 10 第五章第五章 数据库数据库 1313 5 1 数据库创建 13 5 1 1 简单创建 13 5 1 2 使用次要文件 14 5 1 3 使用文件组 14 5 2 数据库修改 15 5 2 1 添加和删除文件及文件组 15 5 2 2 更改文件及文件组的属性 15 5 2 3 更改数据库选项设置 15 5 3 数据库一般管理 16 5 3 1 数据库信息查看 16 5 3 2 收缩数据库 16 5 3 3 删除数据库 16 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 5 3 4 数据库校验 16 5 3 5 附加数据库 17 5 4 数据库备份恢复 17 5 4 1 备份基本知识 17 5 4 2 数据库备份 17 5 4 3 数据库恢复 17 第六章第六章 表表 1818 6 1 约束 18 6 1 1 not null 18 6 1 2 check 18 6 1 3 unique 19 6 1 4 primary key 19 6 1 5 foreign key 20 6 2 字段属性 20 6 2 1 特殊类型 20 6 2 2 自定义类型 21 6 2 3 规则 21 6 2 4 默认值 22 6 2 5 identity 23 6 2 6 计算列 23 6 2 7 大文本存储 23 6 3 索引 24 6 3 1 索引基础知识 24 6 3 2 建表时创建索引 24 6 3 3 在现有表上创建索引 24 6 3 4 查看索引 24 6 3 5 校验索引 24 6 3 6 重建索引 25 6 3 7 删除索引 25 6 4 表的一般维护 25 6 4 1 查看表信息 25 6 4 2 字段维护 25 6 4 3 数据清除 25 6 4 4 表删除 26 6 4 5 表校验 26 6 4 6 表重命名 26 第七章第七章 SQLSQL 编程编程 2626 7 1 基础知识 26 7 1 1 系统表 26 7 1 2 系统函数 26 7 1 3 系统存储过程 27 7 2 事务 27 7 2 1 事务控制 27 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 7 2 2 事务隔离级别 28 7 2 3 使用事务注意事项 28 7 3 锁 29 7 3 1 锁粒度 29 7 3 2 锁模式 29 7 3 3 锁兼容性 29 7 3 4 锁信息查询 30 7 4 视图 30 7 4 1 视图一般维护 30 7 4 2 分区视图 30 7 4 3 索引视图 31 7 5 临时表 31 7 6 自定义函数 31 7 7 存储过程 31 7 7 1 存储过程一般维护 31 7 7 2 引用存储过程结果集 32 7 8 游标 32 7 8 1 游标的一般定义 32 7 8 2 SQL 92 语法 32 7 8 3 T SQL 扩展语法 33 7 9 触发器 33 7 10 SQL 查询 33 7 10 1 基本语法 33 7 10 2 rowcount 34 7 10 3 distinct 34 7 10 4 case 34 7 10 5 having 34 7 10 6 连接 34 7 10 7 null 35 7 10 8 union 35 7 10 9 子查询 35 7 11 SQL 编程注意事项 36 7 11 1 查询语句 36 7 11 2 索引使用 36 7 11 3 tempdb 使用 36 第八章第八章 工具工具 3737 8 1 ISQL OSQL ISQLW 37 8 2 BCP 37 第九章第九章 高级专题高级专题 3838 9 1 分布式查询 38 9 2 JOB 39 9 3 DTS 40 9 3 1 基础知识 40 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 9 3 2 设计 DTS 包 40 9 3 3 运行 DTS 包 40 9 3 4 调度 DTS 包 41 9 4 复制 41 9 4 1 基础知识 41 9 4 2 复制架构模型 42 9 4 3 配置和管理复制 49 9 5 XML 和 INTERNET支持 50 9 5 1 目的 50 9 5 2 实现方法 50 9 5 3 注意事项 52 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 第一章第一章 安装安装 1 1 1 1 安装要求安装要求 win98 只能装个人版 且在不带网卡的机器上装 要 win98 第二版 在 NT40 上装要 sp5 其 sql server2000 中文版不能装到 NT40 英文版上 最低安装要求 内存 64M 硬盘 300M CPU 奔腾 166MHZ 1 2 1 2 服务帐户服务帐户 MSSQLSERVER 及其代理作为服务运行 控制面板 管理工具 服务 可选择本地帐户或域用户帐户作为服务帐户 本地帐户不需要密码 域用户帐户需要密 码 且域用户帐户密码修改后要同时修改控制面板中服务上的密码设置 使用域用户帐户 模式下 身份验证模式只能使用 WINDOWS 身份验证 以下活动必须使用域用户帐户 远程过程调用 复制 备份到网络驱动器 远程数据源的异 类连接 代理邮件功能和 SQL 邮件 更改服务帐户 企业管理器 注册 右键 属性 菜单 1 3 1 3 身份验证模式身份验证模式 windows 身份验证 通过使用 windows 中的用户信息 混合模式 1 4 1 4 授权模式授权模式 每客户许可证 每终端可连接服务器的设备数 对于客户端不止连接到一个服务器 该 方式划算 处理器许可证 每 CPU 可连接的终端数 查询和修改可通过控制面板 SQL SERVER2000 授权安装实用工具 个人版无 在授权的连接数范围内 还可以进一步限制连接数 方式是通过服务器级设置 sp configure user connections 100 默认由系统自动设置 或 企业管理器中服务器 属性 连接 中设置 通过 select max connections 可以查看当前设置 1 5 1 5 排序规则排序规则 安装时排序规则默认取控制面板 区域设置中的设置 排序规则中二进制最快 排序规则是服务器中所有数据库结构的基础 更改其中的一个或多个设置 必须要重建 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 master 和用户数据库 重建 master 数据库的方法 关闭 SQL SERVER2000 运行 rebuildm exe 80 tools binn 下 1 6 1 6 多实例安装多实例安装 多实例文件位置 c program files 下 80 文件夹 公共文件 所有实例共享 安装路径下 MSSQL 文件夹下 默认实例 包括可执行文件和数据文件 安装路径下 MSSQL instance1 下 实例 1 的安装位置 每个实例监听不同的网络地址 客户端不用专门配置相应的网络连接 使用单实例带多数据库的优点 只维护一个实例 没有重复的组件和处理开销 使用多实例的情况 必须支持需要彼此安全隔离的系统 当需要支持多个排序方式不同 的数据库时候 引用实例的方式 ADO ODBC OLE DB 中连接串 server computername instancename 命令提示工具 osql E Scomputer instanced sqlserver Sinstance 第二章第二章 连接连接 客户端和服务器端通讯顺序 a 客户端应用程序调用 OLE DB ODBC DB LIBRARY 专用于 MS SQL Server 嵌入式 SQL API 或者客户端应用程序调用 ADO ADO 再调用 OLE DB b 以上被调用的组件再调用客户端的 net library obnetlib dll c 客户端的 net library 调用 IPC API 不同进程间通讯组件 d IPC API 和服务器端 net library ssnetlib dll 通讯 如果服务器在本机则启用 windows 的 IPC 如命名管道或共享内存 e 服务器端 net library 将请求传给 SQL SERVER 服务器端和客户端通讯方式设置要对应 如服务器端设置 TCP 和命名管道 如果客户端 只设置了命名管道 则无法通过 TCP 方式连接 这些连接方式的 DLL 驱动在安装时候会全 部安装 即使某种方式未启用 WIN98 作为服务器端时不支持命名管道 客户端和服务器端网络工具用于控制 net library 服务器端 net library 安装在 c program files microsfot SQL Server MSSQL n 文件夹下 客户端 net library 安装 在 c windows system32 或 system 文件夹下 客户端 net library 在安装实用工具时安装 local 和 仅用于 MS SQL Server 实用工具中 如查询分析器和 OSQL 其中 在 WIN98 下不支持 DB LIBRARY 不支持 local 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 第三章第三章 服务器服务器 3 1 3 1 基础知识基础知识 页都是 8K 日志不是以数据块而是以记录形式存储 master 数据库记录 MSSQLSERVER 系统级别信息 数据库结构等修改要及时备份数据库 master 数据库只能进行完全备份 如果 master 数据库损坏且无备份 可以用 rebuildm exe 工具重建 tempdb 保存临时表和临时存储过程 model 是创建新数据库的模 板 msdb 数据库供 SQL Server 代理程序调度警报和作业以及记录操作员时使用 3 2 3 2 内存架构内存架构 SQL Server 启动时只获取 8 到 12MB 内存完成初始化工作 然后根据连接任务增多而动 态获取内存 如果设置了 max server memory 则获取的内存不会超过这个数 当连接任 务减少时候 动态释放内存 如果设置了 min server memory 则不会释放到低于这个数 过程高速缓冲 保存查询计划等 和高速缓冲存储器 实际上为数据高速缓冲 的大小 都由 MSSQLSERVER 动态调整 连接环境 每个连接都有一组数据结构记录连接的当前状态 这些数据结构包括查询和 存储过程的参数值 游标定位信息 当前引用的项目等 对象内存使用情况 锁 每个所有者 64 字节 外加 32 字节 连接 12KB 3 网络包大小 3 内存分配情况 程序占用内存程序占用内存 net library 的 dll 开放服务代码 SQL SERVER 代码 分布式 old db 自动化对象 扩展 sp 内存池内存池 系统数据结构 过程高速缓冲 高速缓冲存储器 日志高速缓冲 连接环境 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 3 3 3 3 查询分析架构查询分析架构 查询语句由 OLE DB ODBC 等驱动封装成 TDS 数据包 表格格式数据流 由 MS SQL Server 专用应用程序级协议来发送 注意和 DTS 数据导入导出工具 及 DTC 分布式事 务协调处理器 区别 发送给服务器 服务器由两大部分组成 关系引擎和存储引擎 如下图 关系引擎将查询计划编译为优 化的执行计划 逻辑计划 由存储引擎完成物理查询 两者以 OLE DB API 进行通讯 关系引擎处理查询语句的过程 a 语法分析器扫描 select 语句 并将其分成逻辑查询单元 如关键字 表达式 运算符或 标识符 b 生成查询数 扫描将源数据转换成结果集需要的格式所使用的逻辑步骤 c 查询优化器进行优化 包括对访问源表的多种方法的成本评估 产生执行计划 d 执行执行计划 从存储引擎返回数据 e 将结果集以指定的格式返回给客户端 批处理 a 应用程序作为一个执行单元发出的一组 SQL 语句属于一个批处理 以 go 结尾 go 不是 T SQL 中的关键字 存储过程中的所有语句属于一个批处理 由 execute 或 sp executesql 执行的 SQL 语句属于一个批处理 b 每个批处理编译为一个查询计划 但是当批处理中包含有 execute sp executesql 或激 发了触发器的时候 后三者与前面批处理的执行计划分开执行 c create default create procedure create rule create trigger create view 语句不 能在批处理中与其它语句一起使用 create 须在批处理开头 所有跟在批处理后的语句作 为第一个 create 语句定义的一部分 不能在同一批处理中更新表然后引用新列 如果 execute 是批处理的第一句 可以省略 工作表 查询中生成的中间表创建在 tempdb 中 执行计划的高速缓冲重新利用 语句生成的查询计划如未老化则可复用 指在过程高速缓冲中仍能找到 所以第二次执行 存储过程往往会快些 另外使用带参数的 SQL 语句也可以较好重用已有的查询计划 如 select from tab1 where id id 第二次的查询只是具体的参数不同 3 4 3 4 服务器设置服务器设置 返回 MS SQL Server 环境信息 xp msver 服务器设置 sp configure 或企业管理器 如 exec sp configure network packet size 8192 reconfigure with override reconfigure with override 关键字指定不检查设置值的有效性 强制更新 要小心使 用 设置在发出 reconfigure 或 reconfigure with override 或重启服务器后生效 有些 设置必须要重启服务器 如 sp cofigure fill factor 100 go reconfigure 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 go 若要配置高级选项 必须先将 show advanced options 设置为 1 如 sp configure show advanced options 1 go sp configure cursor threshold 0 go reconfigure go 主要配置说明 标志中 A 高级 RR 必须重启服务器 SC 由 MSSQL 自动配置 选项标志说明 Allow updates 默认为 0 当为 1 时允许直接更新系统表 为 0 情况 下只能通过系统 SP 更新系统表 Default language 为所有新创建的登录指定默认语言 Fill factorA RR 仅在创建索引时引用 默认值为 0 有效值为 0 100 指定索引页被填满的程度 更新情况越小 则该值应越大 只读表应设置为 100 不为以后的 更新预留任何更新 locksA RR RC 可用锁最大数量 默认为 0 由系统自动配置 Open objects 同上同上 Max server memory Min server memory A SC 内存使用上下限 默认由系统自动配置 Network packet size A 以字节为单位设置网络中使用包大小 默认为 4096 字节 如果应用大量的拷贝或大的数据查询比较频 繁 则设置大些可减少网络读写次数 提高性能 小的数据查询设置为 512 就够了 Recovery intervalA SC 恢复数据库所需的最大分钟数 MSSQLSERVER 根据此 设置算出多长时间要进行一次检查点操作 默认为 0 由系统自动配置 实际上不超过 1 分钟 当由于 频繁的检查点操作影响性能时 可小幅增加该值 User connectionsA RR SC 默认由系统自动配置 通过 select max connections 可查看 User options 所有用户的有效默认值 可以在新的会话期间重新 设置 设置时只会影响那个会话 Implictit transactions 当一个命令执行后 事务是否隐性启动 Ansi nulls 当使用相等运算符时控制 NULL 的处理 Atithabort 在查询中溢出或被 0 除时 终止查询 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 quoted identifier 查询中是否区分单双引号 Xact abort 如果 SQL 语句运行出错 则回滚事务 3 5 3 5 服务器一般管理服务器一般管理 服务器启动 命令说明 服务器管理器或控制面板设置 MSSQLSERVER 随 OS 一起自动启动 企业管理器 服务器管理器或控制 面板 通过图形界面手工启动服务 命令提示符下 适当的目录中 SQLservr c 独立于服务控制器来启动 MSSQLSERVER 实例 并 使其不作为服务运行 从而缩短启动时间 命令提示符下 适当的目录中 SQLservr m 单用户模式启动 只有一个用户可连接 且启 用 allow updates 选项 默认被禁用 一般在 遇到需修复系统数据库时使用该模式 命令提示符下 适当的目录中 SQLservr f 以最低配置启动 MSSQLSERVER 实例 只允许单用 户连接 自动启用 allow updates 选项 通常在 设置不当 如内存设置过大 时使用该模式 以 便更改不当的设置 使用该模式前应先停止代理 服务器 命令提示符下 Net start mssqlserver 将 MSSQLSERVER 实例作为服务启动 服务器停止 命令说明 命令提示符下 Net stop mssqlserver 停止 MSSQLSERVER 服务器 查询分析器或 ISQL 等提示工具中 shutdown 停止 MSSQLSERVER 服务器 查询分析器或 ISQL 等提示工具中 shutdown with nowait 立即停止 MSSQLSERVER 服务器 由于没有对任何 数据库执行 checkpoint 所以下次启动恢复时间 较长 服务器信息查看 sp who sp who2 sp lock DBCC useroptions 显示当前连接的 SET 设置 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 第四章第四章 用户与权限用户与权限 4 1 4 1 基础知识基础知识 MSSQLSERVER2000 安全验证分为两阶段 第一阶段验证是否可以和服务器建立连接 身份 验证 第二阶段验证是否有访问服务器上具体数据库的相应权限 数据库访问验证 4 2 4 2 身份验证身份验证 身份验证又分为 Windows 身份验证模式和混合模式 验证过程如下图 当用户通过 Windows NT 4 0 或 Windows 2000 用户帐户进行连接时 SQL Server 通过 回叫 Windows NT 4 0 或 Windows 2000 以获得信息 重新验证帐户名和密码 如果用户 试图通过提供空白登录名称连接到 SQL Server 的实例 SQL Server 将使用 Windows 身 份验证 与 SQL Server 身份验证相比 Windows 身份验证有某些优点 主要是由于它与 Windows NT 4 0 和 Windows 2000 安全系统的集成 当用户用指定的登录名称和密码从非信任连接进行连接时 SQL Server 通过检查是否已 设置 SQL Server 登录帐户 以及指定的密码是否与以前记录的密码匹配 自己进行身份 验证 提供 SQL Server 身份验证是为了向后兼容性 因为为 SQL Server 7 0 版或更早 的版本编写的应用程序可能要求使用 SQL Server 登录和密码 另外 当 SQL Server 实 例在 Windows 98 上运行时 必须使用 SQL Server 身份验证 因为在 Windows 98 上不 支持 Windows 身份验证模式 因此 SQL Server 在 Windows 98 上运行时使用混合模式 但只支持 SQL Server 身份验证 应用程序开发人员和数据库用户也许更喜欢 SQL Server 身份验证 因为他们熟悉登录和密码功能 对于连接到 Windows NT 4 0 和 Windows 2000 客户端以外的其它客户端 可能也必须使用 SQL Server 身份验证 当使 用命名管道连接到在 Windows NT 4 0 或 Windows 2000 上运行的 SQL Server 实例时 用户必须有连接到 Windows NT 命名管道 IPC IPC 的权限 如果用户 没有连接权限 则不能使用命名管道连接到 SQL Server 实例 除非计算机上的 Windows NT 4 0 或 Windows 2000 guest 帐户已启用 默认情况下禁用 或者给用户帐户授予 从 网络访问该计算机 的权限 维护登录 企业管理器 安全性 登录 右键菜单 如果设置的登录是 Windows 身份验 证模式登录 格式为 域名或计算机名 用户名 如 LONDON Andrew 4 3 4 3 权限验证权限验证 服务器固有角色 服务器固有角色存在于各数据库的外部 包含一系列服务器范围的操 作权限 如进行服务器配置 可以将登录和服务器固有角色建立关联 从而使某一登录具 有相应的服务器级别权限 方法是 企业管理器 安全性 服务器角色 右键 属性 菜 单 数据库用户 登录必须和具体数据库中的用户建立关联 才能访问该数据库 创建数据 库用户并和登录建立关联的方法是 具体数据库 用户 右键菜单 如果数据库中含有 guest 用户帐户 则没有和该数据库中用户建立关联的登录可以缺省以 guest 身份访问数 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 据库 数据库用户授权 数据库用户必须具备相应的权限才能对数据库中对象进行操作 有两 种方法对数据库用户授权 一是直接将对象相应权限分配给数据库用户 方法 数据库用 户 右键 属性 菜单 权限 按钮 二是将数据库用户和数据库角色建立关联 数据 库角色包括数据库固有角色和用户创建的数据库角色 这些角色包含在各自的数据库中 均包含一定的操作权限 数据库用户和数据库角色建立关联方法是 数据库用户 右键 属 性 菜单 4 4 4 4 安全模型安全模型 4 5 4 5 操作脚本操作脚本 登录管理 创建和删除 WINDOWS 身份验证登录 查看 WINDOWS 身份验证登录映射的帐户信息 sp grantlogin Corporate BobJ sp revokelogin Corporate BobJ xp logininfo 创建和删除 MSSQL2000 登录 添加或更改 MSSQLSERVER 登录密码 sp addlogin loginname password defdb sp droplogin loginname sp password old password new password loginname 查看登录信息 sp helplogins 查看登录的安全设置 xp loginconfig 更改登录的默认数据库 sp defaultdb loginname defdb 更改登录的默认语言 sp defaultlanguage loginname language 登录和服务器固有角色关联 添加 删除和查看关联 sp addsrvrolemember loginname rolename sp dropsrvrolemember loginname rolename sp helpsrvrolemember rolename 注 rolename 为 sysadmin securityadmin serveradmin setupadmin processadmin diskadmin dbcr eator bulkadmin 数据库用户管理 包含了数据库用户和登录的关联 添加 删除和查看数据库用户 sp adduser loginname username groupname 注 group 可以是组名或角色名 Sp dropuser username Sp helpuser 或 sp helpuser username 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 数据库用户和登录关联 添加和删除关联 sp grantdbaccess loginname username sp revokedbaccess username 用户定制数据库角色管理 添加 删除和查看用户定制数据库角色 sp addrole rolename sp droprole rolename sp helprole 或 sp helprole rolename 数据库角色和数据库用户关联 添加 删除和查看关联 sp addrolemember rolename username sp droprolemember rolename username sp helprolemember rolename 权限和数据库用户及用户定制数据库角色关联 授予权限 a 语句权限 grant all statement n to security account 其中 statement 指 create database create default create function create procesure create rule create table create view backup database backup log security Account 指当前数据库中的 数据库用户 和 用户定制数据库角色 b 对象权限 GRANT ALL PRIVILEGES permission n column n ON table view ON table view column n ON stored procedure extended procedure ON user defined function TO security account n WITH GRANT OPTION AS group role 其中 security Account 指当前数据库中的 数据库用户 和 用户定制数据库角色 WITH GRANT OPTION 指定被授权的对象也可以把权限授于别人 当这种权限继承发生在组 和角色上时使用 as 语句 Permission SELECT INSERT DELETE REFENENCES 或 UPDATE 拒绝权限 a 语句权限 DENY ALL statement n TO security account n b 对象权限 DENY 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 ALL PRIVILEGES permission n column n ON table view ON table view column n ON stored procedure extended procedure ON user defined function TO security account n CASCADE 其中 CASCADE 指定拒绝来自 security account 的权限时 也将拒绝由 security account 授权的任何其它安全帐户 废除权限 a 语句权限 REVOKE ALL statement n FROM security account n b 对象权限 REVOKE GRANT OPTION FOR ALL PRIVILEGES permission n column n ON table view ON table view column n ON stored procedure extended procedure ON user defined function TO FROM security account n CASCADE AS group role 其中 GRANT OPTION FOR 指定要删除的 WITH GRANT OPTION 权限 在 REVOKE 中使用 GRANT OPTION FOR 关键字可消除 GRANT 语句中指定的 WITH GRANT OPTION 设置的影响 用户仍然具有该权限 但是不能将该权限授予其他用户 如果要废除的权限原先不是通过 WITH GRANT OPTION 设置授予的 则忽略 GRANT OPTION FOR 若指定了此参数 并照例废除权限 如果要废除的权限原先是通过 WITH GRANT OPTION 设置授予的 则指定 CASCADE 和 GRANT OPTION FOR 子句 否则将返回一个错误 CASCADE 指定删除来自 security account 的权限时 也将删除由 security account 授权的任何其它安全帐户 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 第五章第五章 数据库数据库 5 1 5 1 数据库创建数据库创建 5 1 1 5 1 1 简单创建简单创建 create database db name 全部使用默认值创建数据库 文件路径默认为安装路径下 data 子目录 物理文件名和逻辑文件名分别为 db name mdf 和 db name log ldf size 为 modal 数据库大小 Maxsize 只受磁盘大小限制 Filegrowth 为每次 10 增长 create database db name on name db name data filename c data1 mdf size 100MB maxsize 500MB filegrowth 5MB log on name db name log filename c log1 ldf size 100MB maxsize 500MB filegrowth 10 5 1 2 5 1 2 使用次要文件使用次要文件 使用目的 1 解决 WIN 下文件大小限制 2 在不同磁盘上创建数据文件可以分散 IO create database db name on primary name data1 filename c data1 mdf size name data2 filename c data2 ndf size log on name log1 filename d log1 ldf size name log2 filename d log2 ldf size 5 1 3 5 1 3 使用文件组使用文件组 使用目的 1 在不同磁盘上创建不同的数据文件 然后指派到一个文件组 可在创 建数据库对象时指定创建到该文件组上 多个磁盘会并行写入 从而分散 IO 2 将 竞争激烈的多个表 或某个表和它的非聚集索引 指定创建到不同的文件组上 避免 IO 竞争 3 可以将不允许修改的表放置到单独的文件组上 并将文件组设置为只读 保护数据 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 一些约定 1 文件只能属于一个文件组 文件组只能被一个数据库使用 2 不要 将事务日志文件和其它文件或文件组置于同一磁盘 事务日志文件不能属于任何文件 组 主文件组和默认文件组 1 包含主文件 primary 指定 无该关键字的第一个数据 文件是主文件 的文件组是主文件组 主文件组包含主文件和任何未指派文件组的文 件 所有系统表都分配在主文件组中 2 创建数据库对象时未指定文件组的创建在默 认文件组上 3 初始情况下主文件组就是默认文件组 Create database db name On Primary name data1 filename c data1 mdf size 1MB maxsize 100MB Filegrowth 1MB Filegroup FG1 name data2 filename c data2 ndf size name data3 filename c data3 ndf size log on name log1 filename c log1 ldf size 在指定的文件组上创建表 use db name go create table tab1 col1 int primary key on FG1 go 5 2 5 2 数据库修改数据库修改 5 2 1 5 2 1 添加和删除文件及文件组添加和删除文件及文件组 alter database db name add file n to filegroup filegroupname add log file n remove file 逻辑文件名 add filegroup filegroupname remove filegroup filegroupname 注 name local file name filename 文件及文件组只有在为空情况下才能移除 5 2 2 5 2 2 更改文件及文件组的属性更改文件及文件组的属性 alter database db name modify file modify file name 旧逻辑名 newname 新逻辑名 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 modify name new db name 或 sp renamedb dbname dbname newname newname modify filegroup filegroupname readonly 或 readwrite modify filegroup filegroupname default 注 modify file 一次只能改变一个属性 如 size maxsize 等 5 2 3 5 2 3 更改数据库选项设置更改数据库选项设置 use master go alter database db name set online 或 offline set single user 或 multi user 或 restricted user set read only 或 read write set auto shrink on set quoted identifier on set recovery full 或 simple 或 bulk logged 旧的设置模式 以后版本可能不继续支持 use master go exec sp dboption db name trunc log on chkpt true go checkpoint go 注 sp dboption 更改数据库设置 使用 sp configure 更改服务器级别设置 使用 SET 语句更改仅影响当前会话的设置 checkpoint 使设置立即生效 关于 set quoted identifier 选项 1 标识符 数据库对象的名称被看成是该对象的标识符 例如 下面的语句创建一个标识符为 TableX 的表 该表中有两列的标识符分别是 KeyCol 和 Description CREATE TABLE TableX KeyCol INT PRIMARY KEY Description NVARCHAR 80 如果标识符符合标识符的格式规则 在 Transact SQL 语句中使用常规标识符时不用将其 分隔 如 SELECT FROM TableX WHERE KeyCol 124 如果标识符不符合标识符的格式规则 在 Transact SQL 语句中 对不符合标识符规则的 标识符必须 用 引号等 进行分隔 如 SELECT FROM My Table 标识符包含了一个空格和一个保留字 WHERE order 10 标识符包含了一个保留字 2 SET QUOTED IDENTIFIER 设置 当 SET QUOTED IDENTIFIER 为 ON 时 双引号内被认为是标识符 单引号内被认为是文字 在这种情况下 如果单引号 是文字字符串的一部分 则可以由两个单引号 表示 当 SET QUOTED IDENTIFIER 为 OFF 时 标识符不可加引号 这就要求必须遵守 Transact SQL 标识符规则 单双引号内均被认为是文字 在这种情况下 但引号可以作 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 为文字字符串一部分包含在双引号中 当在计算列或索引视图上创建或操作索引时 SET QUOTED IDENTIFIER 必须为 ON 在进行连接时 SQL Server ODBC 驱动程序和用于 SQL Server 的 Microsoft OLE DB 提 供程序自动将 QUOTED IDENTIFIER 设置为 ON 这可以在 ODBC 数据源 ODBC 连接特性或 OLE DB 连接属性中进行配置 对来自 DB Library 应用程序的连接 SET QUOTED IDENTIFIER 设置默认为 OFF SET QUOTED IDENTIFIER 可以用于当前会话设置 也可用于数据库设置 查看当前会话选项设置 DBCC USEROPTIONS 设置当前会话 SET QUOTED IDENTIFIER ON OFF 查看当前数据库设置 sp helpdb dbname 设置当前数据库 ALTER DATABASE dbname SET QUOTED IDENTIFIER ON OFF 5 3 5 3 数据库一般管理数据库一般管理 5 3 1 5 3 1 数据库信息查看数据库信息查看 sp helpdb sp helpdb db name sp helpfile sp helpfilegroup sp spaceused select from sysdatabases 5 3 2 5 3 2 收缩数据库收缩数据库 dbcc shrinkdatabase db name 或 use db name dbcc shrinkfile 数据文件或日志文件的逻辑文件名 5 3 3 5 3 3 删除数据库删除数据库 drop database db name 5 3 4 5 3 4 数据库校验数据库校验 dbcc checkdb db name 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 注 时间太久 慎用 5 3 5 5 3 5 附加数据库附加数据库 sp attach db db name c datafilename mdf c logfilename ldf 5 4 5 4 数据库备份恢复数据库备份恢复 5 4 1 5 4 1 备份基本知识备份基本知识 数据修改模式 数据由磁盘被装入数据缓冲 内存 在数据缓冲中进行数据修改 同时 修改被写入日志缓冲 被修改的数据被成为脏页 在处理检查点时 系统自动生成的隐式 检查点或人为发出的 CHECKPOINT 命令 先是日志被写入磁盘 然后是数据被写入磁盘 该过程被称为刷新 日志活动部分 检查点或最旧的活动事务起点或最早的复制起点中最小的一个到日志的 末尾成为日志活动部分 永远不能截断该部分 重启服务器会处理该日志部分 如回滚未 提交的部分 恢复模式与日志截断 不管哪种恢复模式 备份日志将截断日志不活动部分 使用简单 恢复模式如果将数据库设置 trunc log on chkpt 置为 TRUE 则还会在每个检查点自动截 断日志不活动部分 在简单恢复模式 当日志满时候 还可以用以下命令手工截断日志的 不活动部分 backup log db name with no log 或 truncate only 截断日志后应立即备 份整个数据库 在非简单恢复模式下 不能使用 no log 和 truncate only 来手工截断日志 5 4 2 5 4 2 数据库备份数据库备份 数据库完整备份 backup database dn name to disk c db backup bak 数据库差异备份 backup database dn name to disk c db backup2 bak with differential 备份日志 会截断日志非活动部分 backup log dn name to disk c db log backup bak 只备份日志 不截断日志非活动部分 用于数据库损坏 日志未损坏情况下 backup log dn name to disk c db log backup bak with no truncate 只截断日志 用于简单恢复模式下 其日志已满 需要释放日志空间情况下 backup log dn name to disk c db log backup bak with no log 或 with truncate only 备份特定的文件或文件组 backup database dn name file 文件逻辑名 filegroup 文件组逻辑名 to disk c db backup1 bak disk c db backup2 bak 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 5 4 3 5 4 3 数据库恢复数据库恢复 一般数据库恢复步骤 恢复最后一次的完整备份 restore database db name from disk c db backup bak with move 备份的数据文 件逻辑名to d db name mdf move 备份的日志文件逻辑名 to d db name ldf no recovery replace 恢复最后一次的差异备份 restore database db name from disk c db backup2 bak with move 备份的数据文 件逻辑名 to d db name mdf move 备份的日志文件逻辑to d db name ldf no recovery 依备份顺序恢复日志 只在最后一个日志的恢复中带 with recovery 参数 restore log d