Microsoft Word - µÚ06ÕÂ.doc

Size: px

Start display at page:

Download "Microsoft Word - µÚ06ÕÂ.doc"

萍吏胡
5 years ago
Views:

1 第 6 章数据库管理主要内容返回总目录创建数据库数据库属性设置浏览数据库更改数据库删除数据库压缩数据库移动数据库数据库维护计划系统数据库实例数据库管理数据库及其对象是 SQL Server 的主要任务 SQL Server 来管理和操作数据库的基本知识本章将介绍使用

2 6.1 创建数据库文件与文件组在 SQL Server 中数据库是由数据库文件和事务日志文件组成的包含一个数据库文件和一个事物日志文件一个数据库至少应 1 数据库文件 Database File 数据库文件是存放数据库数据和数据库对象的文件一个数据库可以有一个或多个数据库文件一个数据库文件只属于一个数据库当有多个数据库文件时有一个文件被定义为主数据库文件 Primary Database File 扩展名为 mdf 它用来存储数据库的启动信息和部分或全部数据一个数据库只能有一个主数据库文件其它数据库文件被称为次数据库文件 Secondary Database File 扩展名为 ndf 用来存储主文件没存储的其它数据采用多个数据库文件来存储数据的优点体现在数据库文件可以不断扩充而不受操作系统文件大小的限制可以将数据库文件存储在不同的硬盘中这样可以同时对几个硬盘做数据存取提高了数据处理的效率对于服务器型的计算机尤为有用 2 事务日志文件 Transaction Log File 事务日志文件是用来记录数据库更新情况的文件扩展名为 ldf 例如使用 INSERT UPDATE DELETE 等对数据库进行更改的操作都会记录在此文件中而如 SELECT 等对数据库内容不会有影响的操作则不会记录在案一个数据库可以有一个或多个事务日志文件 SQL Server 中采用 Write-Ahead 提前写方式的事务即对数据库的修改先写入事务日志中再写入数据库其具体操作是系统先将更改操作写入事务日志中再更改存储在计算机缓存中的数据为了提高执行效率此更改不会立即写到硬盘中的数据库而是由系统以固定 4 的时间间隔执行 CHECKPOINT 命令将更改过的数据批量写入硬盘 SQL Server 有个特点它在执行数据更改时会设置一个开始点和一个结束点如果尚未到达结束点就因某种原因使操作中断则在 SQL Server 重新启动时会自动恢复已修改的数据使其返回未被修改的状态由此可见当数据库破坏时可以用事务日志恢复数据库内容 3 文件组 File Group 文件组是将多个数据库文件集合起来形成的一个整体每个文件组有一个组名与数据库文件一样文件组也分为主文件组 Primary File Group 和次文件组 Secondary File Group 一个文件只能存在于一个文件组中一个文件组也只能被一个数据库使用主文件组中包含了所有的系统表当建立数据库时主文件组包括主数据库文件和未指定

3 组的其它文件在次文件组中可以指定一个缺省文件组那么在创建数据库对象时如果没有指定将其放在哪一个文件组中就会将它放在缺省文件组中如果没有指定缺省文件组则主文件组为缺省文件组! 事务日志文件不属于任何文件组用 Enterprise Manager 创建数据库在 Enterprise Manager 中可以按下列步骤来创建数据库 1 单击工具栏中的图标或在服务器的 Databases 文件夹或其下属数据库图标上单击右键选择 New Database 选项即会出现如图 6-1 所示的对话框 2 指定数据库的名称和编码方式如图 6-1 所示指定数据库名称选择编码方式图 6-1 创建数据库对话框 3 点击 Transaction Log 页框指定数据库文件的名称存储位置初始容量大小和所属文件组如图 6-2 所示 4 进行数据库文件大小扩充方式和容量限制设置如图 6-2 所示 5 点击 Transaction Log 页框指定事务日志文件的名称存储位置和初始容量大小如图 6-3 所示 6 进行事务日志文件大小扩充方式和容量限制设置如图 6-3 所示

4 指定文件的初始容量指定所属文件组自动增长文件选项指定文件存放位置指定文件容量无限制或应在多少兆字节以内以兆字节或百分比方式增长文件图 6-2 创建数据库文件图 6-3 创建数据库事务日志文件 7 单击确定按钮则建新数据库! 数据库的名称最长为 128 个字符且不区分大小写

5 ! 在此指定的文件容量是以 MB 为单位的数据库文件的最小容量为 1MB 最大容量为 1,048,516TB 这么大的硬盘还没出世事务日志文件的最小容量为 1MB 一个服务器在理论上可以管理 32,767 个数据库用 CREATE DATABASE 命令创建数据库 CREATE DATABASE 命令语法如下 CREATE DATABASE database_name [ ON [PRIMARY] [ <filespec> [,...n] ] [, <filegroupspec> [,...n] ] ] [ LOG ON { <filespec> [,...n]} ] [ COLLATE collation_name ] [ FOR LOAD FOR ATTACH ] <filespec> ::= ( [ NAME = logical_file_name, ] FILENAME = 'os_file_name' [, SIZE = size] [, MAXSIZE = { max_size UNLIMITED }] [, FILEGROWTH = growth_increment] ) [,...n] <filegroupspec> ::= FILEGROUP filegroup_name <filespec> [,...n] 各参数说明如下 database_name 数据库名称不能超过 128 个字符由于系统会在其后添加 5 个字符的逻辑后缀因此实际能指定的字符数为 123 个 ON 指明数据库文件和文件组的明确定义 PRIMARY 指明主数据库文件或主文件组主文件组的第一个文件被认为是主数据库文件其中包含了数据库的逻辑启动信息和数据库的系统表如果没有 PRIMARY 项则在 CREATE DATABASE 命令中列出的第一个文件将被默认为主文件 filespec 文件说明 n 占位符表明可以指定多个类似的对象 filegroupspec 文件组说明 LOG ON

6 指明事务日志文件的明确定义如果没有 LOG ON 选项则系统会自动产生一个文件名前缀与数据库名相同容量为所有数据库文件大小 1/4 的事务日志文件 COLLATE 指明数据库使用的校验方式 collation_name 可以是 Windows 的校验方式名称也可以是 SQL 校验方式名称如果省略此子句则数据库使用当前的 SQL Server 设置的校验方式 FOR LOAD 此选项是为了与 SQL Server 7.0 以前的版本兼容而设定的读者可以不用管它 REST- ORE 命令可以更好地实现此功能 FOR ATTACH 用于附加已经存在的数据库文件到新的数据库中而不用重新创建数据库文件使用此命令必须指定主文件被附加的数据库文件的代码页 Code Page 和排序次序 Sort Order 必须和目前 SQL Server 所使用的一致建议使用 sp_attach_db 系统存储过程来代替此命令关于 sp_attach_db 系统存储过程的用法请参见本章的第 7 节移动和复制数据库 CREATE DATABASE FOR ATTACH 命令只有在指定的文件数目超过 16 个时才必须使用 NAME 指定文件在 SQL Server 中的逻辑名称当时用 FOR ATTACH 选项时就不需要使用 NAME 选项了 FILENAME 指定文件在操作系统中存储的路径名和文件名称 SIZE 指定数据库的初始容量大小如果没有指定主文件的大小则 SQL Server 默认其与模板数据库中的主文件大小一致其它数据库文件和事务日志文件则默认为 1 MB 指定大小的数字 size 可以使用 KB MB GB 和 TB 后缀默认的后缀是 MB size 中不能使用小数其最小值为 512 KB 默认值是 1MB 主文件的 size 不能小于模板数据库中的主文件关于模板数据库的介绍请参见本章最后一节 MAXSIZE 指定文件的最大容量如果没有指定 MAXSIZE 则文件可以不断增长直到充满磁盘 UNLIMITED 指明文件无容量限制 FILEGROWTH 指定文件每次增容时增加的容量大小增加量可以为确定的以 KB MB 作后缀的字节数或以 % 作后缀的被增容文件的百分比来表示缺省后缀为 MB 如果没有指定 FILEG- ROWTH 则缺省值为 10% 每次扩容的最小值为 64 KB CREATE DATABASE 命令在 SQL Server 中执行时使用模板数据库来初始化新建的数据库使用 FOR ATTACH 选项时除外在模板数据库中的所有用户定义的对象和数据库的设置都会被复制到新数据库中每个数据库都有一个所有者 Database Owner 简称 DBO 创建数据库的用户被默认为数据库所有者可以通过 sp_changedbowner 系统

7 存储过程来更改数据库所有者例 6-1 创建一个与上节中用 Enterprise Manager 创建的数据库类似的数据库 create database mydb on primary ( name = mydb_data1, filename = 'd:\sql data\mydb_data1.mdf', size = 1, maxsize = unlimited, filegrowth = 10% ), filegroup data2 ( name = mydb_data2, filename = 'd:\sql data\mydb_data2.ndf', size = 2, maxsize = 100, filegrowth = 1 ), filegroup data3 ( name = mydb_data3, filename = 'd:\sql data\mydb_data3.ndf', size = 3, maxsize = 50, filegrowth = 500kb ) log on ( name = 'mydb_log1', filename = 'd:\sql data\mydb_log1.ldf', size = 1mb, maxsize = 25mb, filegrowth = 10% ), ( name = 'mydb_log2', filename = 'd:\sql data\mydb_log2.ldf', size = 1mb, maxsize = 10mb, filegrowth = 10% ), ( name = 'mydb_log3', filename = 'd:\sql data\mydb_log3.ldf', size = 1mb, maxsize = 5mb, filegrowth = 512kb )

The CREATE DATABASE process is allocating 1.00 MB on disk 'mydb_data1'. The CREATE DATABASE process is allocating 2.00 MB on disk 'mydb_data2'. The CREATE DATABASE process is allocating 3.

8 The CREATE DATABASE process is allocating 1.00 MB on disk 'mydb_data1'. The CREATE DATABASE process is allocating 2.00 MB on disk 'mydb_data2'. The CREATE DATABASE process is allocating 3.00 MB on disk 'mydb_data3'. The CREATE DATABASE process is allocating 1.00 MB on disk 'mydb_log1'. The CREATE DATABASE process is allocating 1.00 MB on disk 'mydb_log2'. The CREATE DATABASE process is allocating 1.00 MB on disk 'mydb_log3'. 6.2 数据库属性设置在创建数据库以后还可以设置其属性以更改创建时的某些设置和创建时无法设置的属性用右键单击所要设置属性的数据库从快捷菜单中选择选项属性 Properties 就会出现如图 6-4 所示的数据库属性设置对话框图 6-4 数据库属性设置对话框在 General 页框中可以看到数据库的状态所有者创建时间容量备份维护等属性信息在 Data Files 和 Transaction Log 页框中可以看到类似于图 6-2 和图 6-3 的对话框此时可以像在创建数据库时那样重新指定数据库文件和事务日志文件的名称存储位置初始容量大小等属性在 Filegroups 页框中如图 6-5 所示可以添加或删除文件组不过如果文件组中有文件则不能删除必须先将文件移出文件组

access 访问限制 Members of db_owner, dbcreator, or sysadmin

9 图 6-5 文件组设置对话框在 Options 选项页框中可以设置数据库的许多属性如图 6-6 所示在图 6-6 中选中的选项都是系统默认为 True 的图 6-6 数据库选项设置各选项选中时的含义如下 Restrict access 访问限制 Members of db_owner, dbcreator, or sysadmin 只有数据库所有者数据库创建者和系统管理员才有权使用数据库 Single user 数据库在同一时间只能供一个用户使用 Read only

10 数据库只读即可查看而不可修改 ANSI Null default 允许在数据库表的列中输入 Null 值 Recursive triggers 允许触发器递归调用 SQL Server 设定的触发器递归调用的层数上限为 32 Select into / bulk copy 允许使用 SELECT INTO 或 BCP WRITETEXT UPDATETEXT 命令向表中大量插入数据用上述命令插入数据在事务日志中不会有记录在开发数据库时时常将此选项设置为 True Truncate log on checkpoint 当执行 CHECKPOINT 命令时如果事务日志文件超过其大小的 70% 则将其内容清除在开发数据库时时常将此选项设置为 True Torn page detection 自动检测有损坏的页 Torn Page 页 Page 是数据库内容的基本存储单位每个页的大小为 8KB 由于 SQL Server 对页的读写单位是大小为 512 字节的扇区 Sector 当每个页的第一个扇区被成功写入后 SQL Server 就认为此页已经被成功写入了因此如果写完第一个扇区后发生突发事件导致写入中断就会产生有损坏的页需要通过备份来恢复数据库 Auto close 当数据库中无用户时自动关闭此数据库并将所占用资源交还给操作系统对那些不间断使用的数据库不要使用此选项否则会额外增加开关数据库的运行开销得不偿失 Auto shrink 定期对数据库进行检查当数据库文件或日志文件的未用空间超过其大小的 25% 时系统将会自动缩减文件使其未用空间等于 25% 当文件大小没有超过其建立时的初始大小时不会缩减文件缩减后的文件也必须大于或等于其初始大小对事务日志文件的缩减只有在对其作备份时或将 Truncate log on checkpoint 选项设为 True 时才能进行 Auto create statistics 启动数据库列的使用统计以提供给查询优化器 Query Optimizer 使用 Auto update statistics 当数据库表更新时更新所有的统计 Use quoted identifiers 标识符必须用双引号扩起来且可以不遵循 Transact-SQL 命名标准 Compatibility Level 数据库兼容性级别可以指定与何种版本以前的数据库兼容其它数据库选项还有一些数据库选项没有被显示出来如 ANSI warning merge publish offline published subscribed 等这些选项在一般情况下最好由 SQL Server 自己来控制它实在需要更改时可以通过 Sp_dboption 系统存储过程来实现系统存储过程 Sp_dboption 用于显示或更改数据库选项但它不能在系统数据库 master 或 tempdb 数据库中使用其语法

11 如下 sp_dboption =] 'database_name'] =] 'option_name'] =] 'value'] 各参数说明如下 =] 'database' 指定所要操作的数据库名 =] 'option_name' 指定要显示或修改的数据库选项可以不输入选项的全名如果省略此项就会列出所有设定为 True 的选项 =] 'value' 指定选项的值 value 的值可以为 True False 或 On Off 例 6-2 显示 mytest 数据库的选项状态 exec sp_dboption 'mytest' The following options are set: select into/bulkcopy trunc. log on chkpt. autoshrink auto create statistics auto update statistics 例 6-3 设置 mytest 数据库的 offline 状态为 True 让一般用户无法使用此数据库 exec sp_dboption 'mytest', 'offline', 'true' sp_dboption command succeeded. 系统管理员可以在模板数据库 model 中执行 Sp_dboptin 命令来设置数据库选项所有新建数据库的初始选项设置这将改变在 Permissions 页框中可以设定用户对此数据库的权限如图 6-7 所示有关权限设置的详细介绍请参见安全性与用户管理章节

1 用 Enterprise Manager 浏览数据库 SQL Server 提供了目录树的浏览方式使得浏览数据库信息非常方便快捷在

12 图 6-7 数据库权限设置 6.3 浏览数据库用 Enterprise Manager 浏览数据库 SQL Server 提供了目录树的浏览方式使得浏览数据库信息非常方便快捷在 Enterprise Manager 中单击要浏览的数据库文件夹就可在右边的任务板 Task pad 窗口中看到数据库的基本信息 General 表和索引信息 Tables & Indexes 数据库文件的配置情况 Space Allocated 如图 6-8 所示在打开数据库文件夹目录树后可以选择各种数据库对象进行信息浏览图 6-8 浏览数据库

13 6.3.2 用系统存储过程显示数据库信息 SQL Server 提供了许多很有用的系统存储过程可以用它们来得到许多从 Enterprise Manager 界面中所不易或不能看到的信息有关存储过程的详细介绍请参见存储过程章节读者如果目前不了解存储过程也不要紧可以把它当作函数或命令来用 1 用系统存储过程显示数据库结构可以使用系统提供的系统存储过程 Sp_helpdb 来显示数据库结构其语法如下 sp_helpdb [[@dbname=] 'name'] 使用 Sp_helpdb 系统存储过程可以显示指定数据库的信息如果不指定 [@dbname=] 'name' 子句则会显示在 master.dbo.sysdatabases 表中存储的所有数据库信息命令执行成功会返回 0 否则返回 1 例 6-4 显示上一节中创建的 mydb 数据库的信息 exec sp_helpdb mydb name db_size owner dbid created status compatibility_level mydb 9.00MB XUJIN\Administrator 9 Sep Status=ONLINE, 80 name fileid filename filegroup size maxsize growth usage only only mydb_data1 1 d:\sql Data\mydb_Data1.mdf PRIMARY 1024KB Unlimited 10% data mydb_log1 2 d:\sql Data\mydb_Log1.ldf NULL 1024KB 25600KB 10% log only mydb_data2 3 d:\sql Data\mydb_Data2.ndf data2 2048KB KB 1024KB data only mydb_data3 4 d:\sql Data\mydb_Data3.ndf data3 3072KB 51200KB 512KB data mydb_log2 5 d:\sql Data\mydb_Log2.ldf NULL 1024KB 10240KB 10% log only mydb_log3 6 d:\sql Data\mydb_Log3.ldf NULL 1024KB 5120KB 512KB log only 2 用系统存储过程显示文件信息可以使用系统提供的系统存储过程 Sp_helpfile 来显示当前数据库中的文件信息其语法如下 sp_helpfile [[@filename =] 'name'] 如果不指定文件名称则会显示当前数据库中所有的文件信息命令执行成功会返回 0 否则返回 1

14 例 6-5 显示 mydb 数据库中的 mydb_data1 文件的信息 use mydb exec sp_helpfile mydb_data1 name filename filegroup size maxsize growth usage mydb_data1 d:\sqldata\mydb_data1.mdf PRIMARY 1024KB Unlimited 10% data only 3 用系统存储过程显示文件组信息可以使用系统提供的系统存储过程 Sp_helpfilegroup 来显示当前数据库中文件组信息其语法如下 sp_helpfilegroup [[@filegroupname =] 'name'] 如果不指定文件组名称则会显示当前数据库中所有的文件组命令执行成功会返回 0 否则返回 1 例 6-6 显示 mydb 数据库中的 data2 文件组的信息 use mydb exec sp_helpfilegroup data2 groupname groupid filecount data2 2 1 file_in_group fileid filename size maxsize growth mydb_data2 3 d:\sqldata\mydb_data2.ndf 2048KB KB 1024KB 例 6-7 显示 mydb 数据库中的所有文件组 use mydb exec sp_helpfilegroup groupname groupid filecount PRIMARY 1 1 data2 2 1 data3 3 1

15 6.4 更改数据库更改文件及其属性可以在 Enterprise Manager 中利用数据库属性设置更改数据库文件和事务日志文件也可以用 ALTER DATABASE 命令来更改数据库 ALTER DATABASE 命令可以增加或删除数据库中的文件也可以修改文件的属性应注意的是只有数据库管理员 Database Administration 简称 DBA 或具有 CREATE DATABASE 权限的数据库所有者才有权执行此命令 ALTER DATABASE 命令的语法如下 ALTER DATABASE databasename { ADD FILE <filespec> [,...n] [TO FILEGROUP filegroup_name] ADD LOG FILE <filespec> [,...n] REMOVE FILE logical_file_name [WITH DELETE] ADD FILEGROUP filegroup_name REMOVE FILEGROUP filegroup_name MODIFY FILE <filespec> MODIFY NAME = new_dbname MODIFY FILEGROUP filegroup_name {filegroup_property NAME = new_filegroup_name } SET < optionspec > [,...n ] [ WITH < termination > ] COLLATE < collation_name > } <filespec> ::= ( NAME = logical_file_name [, NEWNAME = new_logical_name ] [, FILENAME = 'os_file_name'] [, SIZE = size ] [, MAXSIZE = { max_size UNLIMITED }] [, FILEGROWTH = growth_increment ]) < optionspec > ::= < state_option > < cursor_option > < auto_option > < sql_option > < recovery_option > 各参数说明如下 ADD FILE 指定要增加的数据库文件

16 TO FILEGROUP 指定要增加文件到哪个文件组 ADD LOG FILE 指定要增加的事务日志文件 REMOVE FILE 从数据库系统表中删除指定文件的定义并且删除其物理文件文件只有为空时才能被删除 ADD FILEGROUP 指定要增加的文件组 REMOVE FILEGROUP 从数据库中删除指定文件组的定义并且删除其包括的所有数据库文件文件组只有为空时才能被删除 MODIFY FILE 修改指定文件的文件名容量大小最大容量文件增容方式等属性但一次只能修改一个文件的一个属性使用此选项时应注意在文件格式 filespec 中必须用 NAME 明确指定文件的名称如果文件大小是已经确定了的那么新定义的 size 必须比当前的文件容量大 FILENAME 只能指定在 tempdb database 中存在的文件并且新的文件名只有在 SQL Server 重新启动后才发生作用 MODIFY FILEGROUP filegroup_name filegroup_property 修改文件组属性 filegroup_property filegroup_property 可取值为 READONLY 指定文件组为只读主文件组 Primary File Group 不能指定为只读只有对数据库有独占访问权限的用户才可以将一个文件组标志为只读 READWRITE 使文件组为可读写只有对数据库有独占访问权限的用户才可以将一个文件组标志为可读写 DEFAULT 指定文件组为默认文件组一个数据库中只能有一个默认文件组 SET 设置数据库属性 <state_option> 控制用户对数据库访问的属性选项如 SINGLE_USER RESTRICTED_USER MULTI_USER OFFLINE ONLINE READ_ONLY READ_WRITE 等 <cursor_option> 控制游标的属性选项如 CURSOR_CLOSE_ON_COMMIT ON OFF CURSOR _DEFAULTLOCAL GLOBAL 等 <auto_option> 控制数据库的自动属性选项如 AUTO_CLOSE ON OFF AUTO_CREATE_STATIS- TICS ON OFF AUTO_SHRINK ON OFF AUTO_UPDATE_STATISTICS ON OFF 等 <sql_option> 控制 ANSI 一致性的属性选项如 ANSI_NULL_DEFAULT ON OFF ANSI_NULLS ON OFF ANSI_PADDING ON OFF RECURSIVE_TRIGGERS ON OFF 等

17 <recovery_options> 控制数据库恢复的选项如 RECOVERY FULL BULK_LOGGED SIMPLE TORN _PAGE_DETECTION ON OFF 等其余的参数如 NAME FILENAME SIZE MAXSIZE UNLIMITED FILEGROWTH 等与 CREATE DATABASE 命令中的含义相同个别没讲到的参数将在后面的章节中介绍例 6-8 添加一个包含两个文件的文件组到 mydb 数据库中去并将此文件组指定为默认文件组 alter database mydb add filegroup data4 /* 要首先定义文件组然后才能添加文件到文件组中 */ alter database mydb add file ( name = mydb_data4, filename = 'd:\sql data\mydb_data4.ndf', size = 2mb, maxsize = 100mb, filegrowth = 5%), ( name = mydb_data5, filename = 'd:\sql data\mydb_data5.ndf', size = 2mb, maxsize = 50mb, filegrowth = 5%) to filegroup data4 alter database mydb modify filegroup data4 default Extending database by 2.00 mb on disk 'mydb_data4'. Extending database by 2.00 mb on disk 'mydb_data5'. The filegroup property 'default' has been set. 例 6-9 添加两个事务日志文件到 mydb 数据库中去 alter database mydb add log file ( name = mydb_log4, filename = 'd:\sql data\mydb_log4.ndf', size = 1mb, maxsize = 50mb,

18 filegrowth = 5%), ( name = mydb_log5, filename = 'd:\sql data\mydb_log5.ndf', size = 1mb, maxsize = 50mb, filegrowth = 5%)go Extending database by 1.00 MB on disk 'mydb_log4'. Extending database by 1.00 MB on disk 'mydb_log5'. 例 6-10 修改 mydb 数据库中的 mydb_data5 文件增容方式为一次增加 2MB alter database mydb modify file ( name = mydb_log5, filegrowth = 2mb) The command(s) completed successfully. 例 6-11 删除 mydb 数据库中的事务日志文件 mydb_log5 alter database mydb remove file mydb_log5 The file 'mydb_log5' has been removed 更改数据库名重命名数据库需要使用系统存储过程 Sp_renamedb 其语法如下 sp_renamedb [@old_name =] 'old_name', [@new_name =] 'new_name' 例 6-12 更改 mydb 数据库的名称为 My DB exec sp_renamedb 'mydb', 'My DB' The database name ' My DB ' has been set.! 在使用此命令前应将要改名的数据库的访问选项设为 Single user mode 并关闭该数据库在更改数据库名称后在 Enterprise Manager 中所看到的仍然是原来的数据库名称

19 只有选择 Databases 目录文件夹再点击工具栏中的按钮刷新界面或在 SQL Server 重新启动后才会看到更改后的数据库名另外还应注意的是数据库名不区分大小写 6.5 删除数据库用 Enterprise Manager 删除数据库在 Enterprise Manager 中在所要删除的数据库上单击右键从快捷菜单中选择删除 Delete 选项即可删除数据库也可以选择数据库文件夹或图标后从工具栏中选择图标来删除数据库系统会提示确认是否要删除数据库如图 6-9 所示删除数据库一定要慎重因为删除数据库后与此数据库有关联的数据库文件和事务日志文件都会被删除存储在系统数据库中的关于该数据库的所有信息也会被删除! 当数据库处于以下状态时不能被删除数据库正在使用数据库正在被恢复数据库包含用于复制的已经出版的对象图 6-9 确认删除数据库对话框用 DROP DATABASE 命令删除数据库 DROP DATABASE 命令可以从 SQL Server 中一次删除一个或几个数据库有者 DBO 和数据库管理员 DBA 才有权执行此命令其语法如下 DROP DATABASE database_name [,...n] 数据库所例 6-13 drop database abc 删除数据库 abc Deleting database file 'E:\Program Files\Microsoft SQL Server\MSSQL\data\abc_Log.LDF'. Deleting database file 'E:\Program Files\Microsoft SQL Server\MSSQL\data\abc_Data.MDF'.

6.6 压缩数据库数据库在使用一段时间后时常会出现因数据删除而造成数据库中空闲空间太多的情况这时就需要减少分配给数据库文件和事务日志文件的磁盘空间以免浪费磁盘空间当数据库中没有数据时可以修改数据库文件属性直接改变其占用空间但当数据库中有数据时这样做会破坏数据库中的数据因此需要使用压缩的方式来缩减数据库空间可以在数据库属性选项中选择 Auto shrink

20 6.6 压缩数据库数据库在使用一段时间后时常会出现因数据删除而造成数据库中空闲空间太多的情况这时就需要减少分配给数据库文件和事务日志文件的磁盘空间以免浪费磁盘空间当数据库中没有数据时可以修改数据库文件属性直接改变其占用空间但当数据库中有数据时这样做会破坏数据库中的数据因此需要使用压缩的方式来缩减数据库空间可以在数据库属性选项中选择 Auto shrink 选项让系统自动压缩数据库也可以用人工的方法来压缩人工压缩数据库有以下两种方式用 Enterprise Manager 压缩数据库在 Enterprise Manager 中在所要压缩的数据库上单击右键从快捷菜单中的所有任务 All Tasks 中选择 Shrink Database 压缩数据库选项就会出现如图 6-10 所示的对话框可以在图 6-10 所示的对话框中选择数据库的压缩方式也可以选择使用压缩计划或压缩单个文件设定空闲空间占文件大小的百分比单独指定数据库中各数据文使用设定的压缩计划来压缩数据库件和事务日志文件的压缩操作图 6-10 压缩数据库对话框单击图 6-10 中的 Files 按钮会出现如图 6-11 所示的压缩数据库文件对话框可以针对每个数据库文件进行不同的压缩设置

21 指定压缩操作延迟压缩操作图 6-11 压缩数据库文件对话框单击图 6-10 中的 Change 按钮会出现如图 6-12 所示的压缩计划编辑对话框可以指定压缩计划的执行方式单击图 6-12 中的 Change 按钮会出现如图 6-13 所示的循环工作计划编辑对话框可以编辑计划执行的周期或时间点设置完成后单击 OK 按钮就开始压缩数据库在压缩结束后会显示一个压缩情况信息框图 6-12 压缩计划编辑对话框

$SHRINKDATABASE 命令语法如下 DBCC SHRINKDATABASE (database_name [, target_percent] [, {NOTRUNCATE TRUNCATEONLY}] ) 各参数说明如下 target_percent 指定将数据库压缩后未使用的空间占数据库大小的百分之几如果指定的百分比过大超过了压缩前未使用空间所占的比例则数据库不会被压缩$

22 图 6-13 循环工作计划编辑对话框用 Transact-SQL 命令压缩数据库可以使用 DBCC SHRINKDATABASE 和 DBCC SHRINKFILE 命令来压缩数据库其中 DBCC SHRINKDATABASE 命令对数据库进行压缩 DBCC SHRINKFILE 命令对数据库中指定的文件进行压缩 1 DBCC SHRINKDATABASE DBCC SHRINKDATABASE 命令语法如下 DBCC SHRINKDATABASE (database_name [, target_percent] [, {NOTRUNCATE TRUNCATEONLY}] ) 各参数说明如下 target_percent 指定将数据库压缩后未使用的空间占数据库大小的百分之几如果指定的百分比过大超过了压缩前未使用空间所占的比例则数据库不会被压缩并且压缩后的数据库不能比数据库初始设定的容量小 NOTRUNCATE 将数据库缩减后剩余的空间保留在数据库中不返还给操作系统如果不选择此选项则剩余的空间返还给操作系统 TRUNCATEONLY 将数据库缩减后剩余的空间返还给操作系统使用此命令时 SQL Server 将文件缩减到最后一个文件分配区域但不移动任何数据文件选择此项后 target_percent 选项就无效了例 6-14 压缩数据库 mytest 的未使用空间为数据库大小的 20% dbcc shrinkdatabase (mytest, 20)

23 DBCC execution completed. If DBCC printed error messages, contact your system administrator. 2 DBCC SHRINKFILE DBCC SHRINKFILE 命令压缩当前数据库中的文件其语法如下 DBCC SHRINKFILE ( {file_name file_id } {[, target_size] [, {EMPTYFILE NOTRUNCATE TRUNCATEONLY}] } ) 各参数说明如下 file_id 指定要压缩的文件的鉴别号 Identification number 即 ID 文件的 ID 号可以通过 FILE_ID 函数或如本章前面所讲述的 Sp_helpdb 系统存储过程来得到 target_size 指定文件压缩后的大小以 MB 为单位如果不指定此选项 SQL Server 就会尽最大可能地缩减文件 EMPTYFILE 指明此文件不再使用将移动所有在此文件中的数据到同一文件组中的其它文件中去执行带此参数的命令后此文件就可以用 ALTER DATABASE 命令来删除了其余参数 NOTRUNCATE 和 TRUNCATEONLY 与 DBCC SHRINKDATABASE 命令中的含义相同例 6-15 压缩数据库 mydb 中的数据库文件 mydb_data2 的大小到 1MB use mydb dbcc shrinkfile (mydb_data2, 1) DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages (1 row(s) affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator. 6.7 移动数据库在 SQL Server 中可以使用拆分 Detach 和附加 Attach 的方法来移动数据库拆分数据库是从服务器中移去逻辑数据库但不会将操作系统中的数据库文件删除附加数据库将会创建一个新的数据库并复制存储在已有的数据库文件和事务日志文件中的数据使用系统存储过程 Sp_detach_db 来拆分数据库用系统存储过程 Sp_attach_db 来附加数据库 Sp_detach_db 系统存储过程的语法如下

24 sp_detach_db =] 'database_name' =] 'skipchecks'] 其中 =] 'skipchecks' 子句中 Skipchecks 的值为 True 或 False 当 Skipchecks 的值为 True 时指定在执行此过程之前不需要对数据库中的所有表执行 UPDATE STATIS- TICS 命令为 False 时则需要执行 UPDATE STATISTICS 命令 Sp_attach_db 系统存储过程的语法如下 sp_attach_db =] 'dbname', =] 'filename_n' [,...16] 其中 filename_n 包括文件的路径和物理名称最多可指定 16 个文件文件中必须包含主数据库文件如果需要附加的文件超过了 16 个就必须使用带 FOR ATTACH 子句的 CREATE DATABASE 命令来代替! Sp_attach_db 系统存储过程只能作用于那些已经用 Sp_detach_db 系统存储过程从服务器中拆分出来的数据库例 6-16 移动数据库 mytest 到 E:\SQL Data 目录下 1 在 SQL Server Query Analyzer 中运行系统存储过程 Sp_helpdb 得到 mytest 数据库所包含的文件名称数量类型存放位置等信息命令语句如下 exec sp_helpdb mytest name db_size owner dbid created status compatibility_level mytest 6.00MB XUJIN\Administrator 10 Jun Status=ONLINE, 80 name fileid filename filegroup size maxsize growth usage mytest_data 1 D:\SQL Data\mytest_Data.MDF PRIMARY 3072KB Unlimited 10% data only mytest_log 2 D:\SQL Data\mytest_Log.LDF NULL 3072KB Unlimited 10% log only 2 在 SQL Server Query Analyzer 中运行 Sp_detach_db 系统存储过程拆分 mytest 数据库命令语句如下 exec sp_detach_db mytest, true Successfully detached database 'mytest1'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. 3 在操作系统的文件夹窗口中用剪切或粘贴的方式直接将与数据库相关的操作系统文件移动到 E:\ SQL Data 2000 Server 目录下即将 D:\SQL Data\mytest_

$Data.MDF 文件和 D:\SQLData\mytest_Log.$

25 Data.MDF 文件和 D:\SQLData\mytest_Log.LDF 文件移到 E:\ SQL Data 2000 Server 目录下 4 在 SQL Server Query Analyzer 中运行 Sp_attach_db 系统存储过程附加文件到 mytest 数据库命令语句如下 exec = = 'e:\sql = 'e:\sql data\mytest_log.ldf' Successfully attached database 'mytest'. 至此已完成了数据库的移动工作可在 Enterprise Manager 中查看 mytest 数据库的信息也可以在 SQL Server Query Analyzer 中运行系统存储过程 Sp_helpdb 来查看移动是否成功 6.8 数据库维护计划数据库维护可以使数据库保持运行在最佳状态创建数据库维护计划可以让 SQL Server 有效地自动维护数据库为管理员节省不少时间也可以防止延误数据库的维护工作创建数据库维护计划的过程如下 1 在 Enterprise Manager 中选择要创建维护计划的数据库单击右键从快捷菜单中选择所有任务 All Tasks 子菜单中的 Maintenance Plan 维护计划菜单项则出现如图 6-14 所示的欢迎界面图 6-14 数据库维护计划向导 2 单击下一步按钮出现如图 6-15 所示的选择数据库对话框可以根据需要选择所要维护的数据库

6-13 所示的编辑循环工作计划对话框 4 在图 6-16 中单击下一步按钮出现如图 6-17

26 图 6-15 选择要维护的数据库对话框 3 单击下一步按钮出现如图 6-16 所示的更新数据最优化信息对话框可以根据需要选择是否要对数据库的数据页或索引页做重整工作单击 Change 按钮出现如图 6-13 所示的编辑循环工作计划对话框 4 在图 6-16 中单击下一步按钮出现如图 6-17 所示的数据库完整性检查对话框可以选择是否要对数据库做完整性测试以便查找是否有因软件或硬件错误所造成的数据库错误重组数据和索引页删除未使用空间编辑循环工作计划图 6-16 更新数据最优化信息对话框

27 备份数据库前先执行完整性检查编辑循环工作计划图 6-17 数据库完整性检查对话框 5 单击下一步按钮出现如图 6-18 所示的指定数据库备份计划对话框可以选择是否将数据库备份到磁带或硬盘一般都选择将数据库备份到硬盘备份完成后是否验证备份是否成功图 6-18 指定数据库备份计划对话框 6 单击下一步按钮出现如图 6-19 所示的指定数据库备份文件存放目录对话框可以选择将数据库备份文件存放到指定的硬盘目录还可以给每个数据库创建一个子目录

28 删除存储时间超过某一限定的备份文件图 6-19 指定数据库备份文件存放目录对话框 7 单击下一步按钮出现如图 6-20 所示的指定事务日志文件备份计划对话框可以选择是否将事务日志备份加入到数据库维护计划中 8 单击下一步按钮出现如图 6-21 所示的指定事务日志文件备份存放目录对话框可以选择是否将事务日志备份存放的相关选项图 6-20 指定事务日志文件备份计划对话框

所示的指定维护计划执行历史记录存放位置对话框可以选择将其存放在本地服务器或远程服务器上 11 单击下一步按钮出现如图 6-24

29 图 6-21 指定事务日志文件备份存放目录对话框 9 单击下一步按钮出现如图 6-22 所示的产生维护计划报告对话框可以选择报告的存放目录等选项可以利用 SQL Mail 将报告以 E- mail 的方式发送给指定的操作员图 6-22 产生维护计划报告对话框 10 单击下一步按钮出现如图 6-23 所示的指定维护计划执行历史记录存放位置对话框可以选择将其存放在本地服务器或远程服务器上 11 单击下一步按钮出现如图 6-24 所示的最终界面指定维护计划名称对话框输入名称后单击完成按钮完成维护计划的设置在设置好维护计划后可以在 Enterprise Manager 中查看修改它在企业管理器中服务器文件夹下的 Management 子目录下选择 Database Maintenance Plans 就可看到所设置的计划如图 6-25 所示

30 执行记录超过限定行时覆盖旧记录图 6-23 指定维护计划执行历史记录存放位置对话框图 6-24 指定维护计划名称对话框在图 6-25 窗口中选择要修改的数据库维护计划名称单击右键从快捷菜单中选择属性 Properties 选项则出现如图 6-26 所示的数据库维护计划编辑对话框可以编辑在计划生成向导中所设置的几乎所有信息

31 图 6-25 维护计划对象图 6-26 数据库维护计划编辑 6.9 系统数据库 SQL Server 2000 有四个系统数据库 System Databases Master Model Msdb Tempdb 这些系统数据库的文件存储在 Microsoft SQL Server 默认安装目录的 MSSQL 子目录的 Data 文件夹中各系统数据库所包含的文件如表 6-1 所示表 6-1 系统数据库的文件数据库文件物理文件名默认大小典型安装

32 master primary data Master.mdf 7.5 MB master log Mastlog.ldf 1.0 MB model primary data Model.mdf 0.75 MB model log Modellog.ldf 0.75 MB msdb primary data Msdbdata.mdf 3.5 MB msdb log Msdblog.ldf 0.75 MB tempdb primary data Tempdb.mdf 8.0 MB tempdb log Templog.ldf 0.5 MB 如果在企业管理器中看不到系统数据库则可用鼠标右键单击服务器从快捷菜单中选择 Edit SQL Server Registration Properties 会出现如图 5-5 所示的编辑服务器注册属性对话框从中选择 Show system databases and system objects 选项再刷新企业管理器界面即可 Master 数据库 Master 数据库记录了一个 SQL Server 系统的所有系统信息这些系统信息主要有所有的登录信息系统设置信息 SQL Server 初始化信息系统中其它系统数据库和用户数据库的相关信息包括其主文件的存放位置等 Model 数据库 Model 数据库是所有用户数据库和 Tempdb 数据库的创建模板当创建数据库时系统会将 Model 数据库中的内容复制到新建的数据库中去由此可见利用 Model 数据库的模板特性通过更改 Model 数据库的设置并将时常使用的数据库对象复制到 Model 数据库中可以大大简化数据库及其对象的创建设置工作为用户节省大量的时间通常可以将以下内容添加到 Model 数据库中数据库的最小容量数据库选项设置经常使用的数据库对象如用户自定义的数据类型函数规则缺省值等 Msdb 数据库 SQL Server SQL Server Enterprise Manager 和 SQL Server Agent 使用 Msdb 数据库来存储计划信息以及与备份和恢复相关的信息尤其是 SQL Server Agent 需要使用它来执行安排工作和警报记录操作者等操作 Tempdb 数据库 Tempdb 数据库用作系统的临时存储空间其主要作用有

33 存储用户建立的临时表和临时存储过程存储用户说明的全局变量值为数据排序创建临时表存储用户利用游标说明所筛选出来的数据在 Tempdb 数据库中所做的存在不会被记录因而在 Tempdb 数据库中的表上进行数据操作比在其它数据库中要快得多! 当退出 SQL Server 时用户在 Tempdb 数据库中建立的所有对象都将被删除每次 SQL Server 启动时 Tempdb 数据库都将被重建回复到系统设定的初始状态因此千万不要将 Tempdb 数据库作为数据的最终存放处 6.10 实例数据库 SQL Server 2000 有两个实例数据库 Sample Databases pubs 和 Northwind 与系统数据库一样实例数据库的文件也存储在 Microsoft SQL Server 默认安装目录的 MSSQL 子目录的 Data 文件夹中实例数据库所包含的文件如表 6-2 所示表 6-2 实例数据库的文件数据库文件物理文件名默认大小典型安装 pubs primary data Pubs.mdf 1.75 MB pubs log Pubs_log.ldf 0.75 MB Northwind primary data Northwnd.mdf 3.25 MB Northwind log Northwnd.ldf 1.0 MB pubs 和 Northwind 数据库可以作为 SQL Server 的学习工具 SQL Server Books Online 中的实例基本上都是基于这两个数据库来讲解的其中 pubs 实例数据库存储了一个虚构的图书出版公司的基本情况 Northwind 实例数据库包含了一个公司的销售数据此公司名为 Northwind 商人 Northwind Traders 是一个虚构的公司从事食品的进出口业务如果改变了实例数据库的内容或不小心删除了实例数据库想将其回复到初始状态可以用 SQL Server Analyzer 运行安装目录 \MSSQL\Install 中的 Instpubs.sql 或 Instnwnd.sql 程序来重建 pubs 或 Northwind 数据库 6.11 本章小结本章介绍了数据库的创建与管理知识以及系统数据库和实例数据库的基本情况强大的数据库管理功能是 SQL Server 的特点掌握本章内容是对数据库管理员的基本要求

四川天一学院信息工程系毛玉环

四川天一学院信息工程系毛玉环教学目标 1 1 1 掌握 SQL Server 2005 数据库系统的数据库类型了解系统数据库的功能了解数据库中的基本对象的特点 1 1 1 掌握数据库文件及文件组的概念及配置策略掌握使用 Transact-SQL 创建数据库的方法掌握查看数据库的基本方法 1 1 掌握修改文件组增大数据库及收缩数据库方法掌握删除数据库的方法 3.1 SQL Server