第 4 章 SQLSERVER 数据库管理

第第 44 章章 SQLSERVER 数据库管

理

第第 44 章章 SQLSERVER 数据库管

理

本章的学习目标：• 了解数据库的存储结构• 理解数据库文件和文件组的基本特征• 了解 SQL Server 2008 R2 系统的数据库• 了解数据库的物理存储方式和大小估算方法• 熟练掌握用 Management Studio 工具和 T-SQL 语句创建数据库• 熟练掌握用 Management Studio 工具和 T-SQL 语句修改数据库• 熟练掌握扩大数据库的原因和方法• 掌握数据库的分离和附加• 掌握收缩数据库的原因和方法• 掌握数据库的删除• 掌握数据库的备份和还原

本章内容• 4.1 SQL SERVER 数据库的存储结构• 4.2 数据库的创建• 4.3 数据库的修改• 4.4 数据库的分离和附加• 4.5 数据库的收缩• 4.6 数据库删除• 4.7 数据库的备份和还原• 4.8 本章小结

4.1 SQL SERVER 数据库的存储结构数据库的两种存储结构 :

逻辑存储结构和物理存储结构。数据库物理存储结构表现为存储数据的各类操作系统

文件， SQL Server 2008 R2 数据库在磁盘上是以文件为单位存储的，由数据文件和事物日志文件组成，每个数据库至少要具有两种操作系统文件：一个数据文件和一个事务日志文件。

数据库的逻辑存储结构，是指数据库有哪些性质的信息组成，即一个数据库由若干用户可视的各种数据库对象构成，诸如表、视图、索引、存储过程等。

4.1.1 数据库对象数据库的逻辑存

储结构数据库的逻辑存储

结构，是指数据库有哪些性质的信息组成，即一个数据库由若干用户可视的各种数据库对象构成，诸如表、视图、索引、存储过程等。

SQL Server 2008 R2 中常用的数据库对象 1. 表：表是数据库中用于容纳所有数据的最常用的数

据库对象，由行和列组成的二维的行列结构。

2. 视图：视图是用户查看数据库表中数据的一种方式，它其实相当于一种虚拟表，但是可以像使用真实表一样使用视图。视图又被称为一种逻辑对象，并不占用物理空间，其作用像相当于查询，所包含的列和行的数据只来源于视图所查询的表（这种实际的表称为视图的基表），在引用视图时动态生成。

3. 索引：数据库中的索引类似于书籍中的目录。对一个没有索引的表进行的查询操作，系统将检查表中的每一个数据行，这就好比在一本没有目录的书中查找信息。

4. 存储过程：存储过程是 SQL Server 2008 R2 服务器上一组预编译的 Transact-SQL 语句，它可以接受参数、返回状态值和参数值，并且可以嵌套调用。使用服务器上存储过程而不使用客户机本地上的 Transact-SQL 程序的优点有：保证各个客户端操作一致性，提供安全机制（有权限的才能操作）及减少网络传输等。

5. 触发器：触发器是一种特殊类型的存储过程，当执行某些操作导致表中的数据被修改时，触发器会自动触发执行。它主要用来实现复杂的数据完整性。

6. 函数： SQL Server 2008 R2 中包含有大量的函数，我们用函数可以完成特定的计算功能。在 SQL

Server 2008 R2 中除了有内置函数，还允许用户自定义函数。

4.1.2 数据库文件及文件组数据库的物理存储结构

数据库的物理存储结构，表现为存储数据的各类操作系统文件， SQL Server 2008 R2 数据库在磁盘上是以文件为单位存储的，由数据文件和事物日志文件组成，每个数据库至少要具有两种操作系统文件：一个数据文件和一个事务日志文件。

数据文件可以隶属于某个文件组中以便于对该文件中数据存储的管理。

数据文件：又可以分成主数据文件和次数据文件两种形式。主数据文件是数据库的起点，用来存储数据库的启动信息和部分或全部数据，每一个数据库都有且仅有一个主数据文件。主数据文件名称的默认后缀是 mdf 。次数据文件是可选的，它们可以存储不在主数据文件中的全部数据和对象。数据库既可能没有次数据文件，也可能有多个次数据文件。次数据文件名称的默认后缀是 ndf 。

事务日志文件：

事务就是一个单元的工作，该单元的工作要么全部完成，要么全部不完成。 SQL Server 2008 R2 系统具有事务功能，可以保证数据库操作的一致性和完整性。

事务日志以操作系统文件的形式存在，在数据库中被称为事务日志文件。每一个数据库都至少有一个事务日志文件。事务日志文件名称的后缀默认是 ldf 。

主数据文件

次数据文件

事务日志文件

文件组

文件组就是文件的逻辑集合。为了方便数据的管理和分配，文件组允许对文件进行分组以便于管理数据的分配或放置，可以把一些指定的文件组合在一起。

也就是说：当你的数据库中包含很多数据文件时，你可以将这些数据文件存储在不同的地方，然后用文件组把它们作为一个单元来管理。文件组对组内的所有文件都使用按比例填充策略，即当将数据写入文件组时， SQL Server 2008 R2 根据文件中的可用空间量将按一定比例的数据写入文件组的每个文件，而不是将所有的数据先写满第一个文件，接着再写入下一个文件。

例如，在某个数据库中， 3 个文件 (data1.ndf 、 data2.ndf 和data3.ndf) 分别创建在 3 个不同的磁盘驱动器中，然后为它们指定一个文件组 fgroup1 。以后，所创建的表可以明确指定放在文件组 fgroup1 上。对该表中数据的查询将分布在这 3 个磁盘上，因此，可以通过执行并行访问而提高查询性能。在创建表时，不能指定将表放在某个文件上，只能指定将表放在某个文件组上。因此，如果希望将某个表放在特定的文件上，那么必须通过创建文件组来实现。

文件组

SQL Server 2008 R2 一共有三种类型的文件组，它们分别是主文件组 (primary) 、用户定义文件组，还可选中一个文件组为默认文件组 (default) 。 SQL Server 2008 R2 至少包含一个文件组，即主文件组。主文件组包含主数据文件，存放系统表格等。 Primary不能被更改。默认文件组（ default ），用来存放任何没有指定文件组的对象。任何时候只能有一个文件组被指定为 default ，默认情况下主文件组被当做默认的文件组。

使用文件和文件组时，应该考虑下列因素：一个文件或者文件组只能用于一个数据库，不能是多个数据库；一个文件只能是某一个文件组的成员，不能是多个文件组的成员；数据库的数据信息和日志信息不能放在同一个文件中，即数据文件和日志文件总是分开的；日志文件永远也不能是任何文件组的一部分。

4.1.3 SQL Server 2008 R2 数据库类型 SQL Server 2008 R2 中的数据库有两种类型：

系统数据库和用户数据库。 1. 系统数据库

系统数据库由 master 、 model 、 msdb 、 tempdb

和隐藏的 Resource 数据库组成。

（ 1 ） master 数据库： master 数据库用于记录 SQL

Server 2008 R2 中所有服务器级别的对象。这包括了服务器登录帐户、链接服务器定义以及端点。 master 数据库同时还记录服务器上其他所有数据库的信息。 SQL Server 2008 R2 并不把系统信息存储在 master 数据库中，而是存储在 Resource 数据库中。不过在 master 数据库中，系统信息逻辑呈现为 sys 架构。

4.1.3 SQL Server 2008 R2 数据库类型（ 2 ） model 数据库： model 数据库是一个模板数据库。每当创建

新的数据库时 ( 包括系统数据库 tempdb) ，就会创建 model 数据库的一个副本，并以新创建数据库的名称重命名该副本。

（ 3 ） msdb 数据库可以把 msdb 数据库看作是 SQL Server代理的数据库，这是因为

SQL Server 2008 R2代理广泛地使用 msdb 数据库存储自动化作业定义、作业计划、操作员定义以及警报定义。

（ 4 ） tempdb 数据库 SQL Server 2008 R2 使用 tempdb 数据库临时性地存储数据。在

SQL Server 2008 R2 操作中， tempdb 数据库有广泛的运用，因此要确保 SQL Server 2008 R2 数据库有效的操作，就需要仔细计划和评估 tempdb 数据库的规模和位置。

（ 5 ） Resource 数据库它是一个只读数据库，包含 SQL Server 2008 R2 实例使用的所有系统

对象。

4.1.3 SQL Server 2008 R2 数据库类型 2. 用户数据库

用户数据库就是用户创建的数据库。它们存储数据应用程序所使用的数据，这也是拥有一个数据库服务器的主要目的。

4.2 数据库的创建数据库创建的方法：

创建数据库的方法有多种：使用 Management Studio

工具创建数据库、可以使用 CREATE DATABASE 语句创建数据库。

在创建数据库之前要需估算所建数据库的大小和增幅。

创建数据库就是确定数据库名称、文件名称、数据文件大小、数据库的字符集、是否自动增长以及如何自动增长等信息的过程。数据库的名称必须满足系统的标识符规则。在命名数据库时，一定要使数据库名称简短和有一定的含义。

4.2.1 使用 Management Studio 工具创建数据库要求：创建一个酒店管理系统所使用的数据库，名为

JdglSys 。因为酒店管理系统中涉及到的容纳数据的表大致有 7 个，估计总数据量为 20000 行记录，根据课本介绍的估算数据库大小的方法，估算 JdglSys 数据库中的数据文件的大小为 10M ，日志文件为 3M 。

步骤 :

（ 1 ）从“开始” -“ 程序” -“Microsoft SQL Server

2008 R2”-“SQL Server 2008 R2 Management Studio”

点击后，打开该工具，首先点击“连接”到 SQL

Server 2008 R2 数据库引擎实例，详见图 4-3连接Management Studio 工具。

4.2.1 使用 Management Studio 工具创建数据库

图 4-3 连接Management Studio工具


（ 2 ）展开该实例后，如图 4-4 Management Studio

工具界面图。

图 4-4 Management Studio 工具界面


（ 3 ）右击上图中的“数据库”，在弹出菜单中选择“新建数据库”，会弹出新建数据库窗口，如图4-5 新建数据库所示。在“数据库名称”项中输入新数据库的名称 JdglSys 。下面数据库文件中的逻辑名称就会自动设置完成，注意：此处要修改一下数据文件的大小为 10M ，日志文件的大小为 3M ，单击“确定”按钮即完成。

图 4-5 新建数据库 JdglSys


（ 4 ）创建完成 JdglSys 数据库后的 Management

Studio 的工具界面如下：

图 4-6 Management Studio 工具界面

新建的 JdglSys 数据库

4.2.2 使用 T-SQL 语言创建数据库 CREATE DATABASE 语句的常用语法格式如下 :

CREATE DATABASE database_nameON{ [ PRIMARY ] ( NAME = logical_file_name , FILENAME = 'os_file_name' , [ , SIZE = size] [ , MAXSIZE = { max_size | UNLIMITED } ] [ , FILEGROWTH = growth_increment ]) [ ,...n ] LOG ON { ( NAME = logical_file_name , FILENAME = 'os_file_name' , [ , SIZE = size] [ , MAXSIZE = { max_size | UNLIMITED } ] [ , FILEGROWTH = growth_increment ]) [ ,...n ]

4.2.2 使用 T-SQL 语言创建数据库Create database 语句中参数说明：database_name ：新数据库的名称。ON ：指定用来存储数据库数据部分的磁盘文件（数据文件）。LOG ON ：指定显式定义用来存储数据库日志的磁盘文件（日

志文件）。NAME logical_file_name ：指定文件的逻辑名称。FILENAME ：指定操作系统（物理）文件名称。SIZE ：指定文件的大小。MAXSIZE ：指定文件可增大到的最大大小。 FILEGROWTH ：指定文件的自动增量。也可以指定百分比。


【例 4-1】创建 JdglSys 数据库，其数据文件初始大小为10MB ，最大值 UNLIMITED ，文件大小增长量为 1MB ，日志文件初始大小为 3MB ，最大大小为 UMLIMITED ，增量为 10% 。文件的位置在默认安装的位置。

4.2.1 使用 Management Studio 工具创建数据库具体的 T-SQL 语句为：

CREATE DATABASE JdglSys ON PRIMARY ( NAME = JdglSys_Data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\JdglSys_Data.mdf', SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 1)LOG ON ( NAME = JdglSys_Log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\JdglSys_Log.ldf', SIZE = 3MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)GO

4.2.2 使用 T-SQL 语言创建数据库连接上 Management Studio 工具，点击右上角的“新建查询”，

出现如下图示，在其中输入该语句，点击“执行”即可。

4.2.3 查看数据库的相关信息 SQL Server 2008 R2 系统中，可以使用一些系统视图、存

储过程查看有关数据库的基本信息。

常用的有以下几个：

1. sys.databases ：可以查看该服务器上所有数据库的基本信息。

图 4-8使用 sys.databases系统视图查看服务器上的数据库

4.2.3 查看数据库的相关信息 2. sp_helpdb ：可以查看该服务器上所有数据库或指定单个数据库的基本信息。如图 4-9 所示存储过程查看所有数据库。使用 sp_helpdb JdglSys命令可以查看单个数据库的详细信息。

图 4-9 使用 sp_helpdb 存储过程查看所有数据库

4.2.3 查看数据库的相关信息使用 sp_helpdb JdglSys命令可以查看单个数据库的详细信息。

图 4-10 使用 sp_helpdb 存储过程查看单个数据库

4.2.3 查看数据库的相关信息3. sp_helpfile ：显示当前数据库关联的文件的名称及属性。如图 4-11 所示。其中， use 语句用来改变当前数据库，把 JdglSys 数据库作为当前查询的数据库。

图 4-11 使用 sp_helpfile 查看相关数据库的文件信息

4.2.3 查看数据库的相关信息 4.sp_helpfilegroup 系统存储过程

查看 JdglSys 数据库中的所有文件组或某一个文件组的信息。

如图 4-12 所示。

图 4-12 使用 sp_helpfilegroup 查看相关数据库的文件组信息

4.2.3 查看数据库的相关信息 5.sp_spaceused 系统存储过程显示由整个数据库保留和使用的磁盘空间。如图 4-13 所示。

图 4-13 使用 sp_spaceused 查看数据库空间使用状况信息

4.3 数据库的修改数据库创建之后，根据需要可以使用 Management

Studio 工具或 ALTER DATABASE 语句对数据库进行修改。修改操作包括更改数据库名称、扩大数据库、修改数据库文件、管理数据库文件组等。

数据库修改的方法：修改数据库的方法也有两种：使用 Management

Studio 工具修改数据库、可以使用 alter DATABASE

语句创建数据库。

4.3.1 使用 Management Studio 工具修改数据库

1.重命名数据库

数据库创建之后，一般情况下不要更改数据库的名称，因为许多应用程序都可能使用了该数据库的名称。数据库名称更改之后，需要修改相应的应用程序。但是，如果确实需要更改数据库名称，可以使用 Management Studio

工具修改数据库很轻松的修改数据库名。比如，修改数据库 JdglSys 为 hotel 。


“选中” 要更名的数据库 “右击”“重命名” JdglSys 可更改为 hotel


2. 扩大数据库

在 SQL Server 2008 R2 系统中，如果数据库的数据量不断膨胀，可以根据需要扩大数据库的尺寸。有 3 种扩大数据库的方式。第一种方式是设置数据库为自动增长方式，可以在创建数据库时设置。第二种方式是直接修改数据库的数据文件或日志文件的大小，第三种方式是在数据库中增加新的次要数据文件或日志文件。

4.3.1 使用 Management Studio 工具修改数据库例如， JdglSys 数据库的大小是 13MB ，如果希望扩大到

20MB （其中，数据文件大小 15M ，日志文件大小 5M ）。有三种方法实现：（ 1 ）、那么可以通过在创建数据库时，设定文件为自动增长的，一旦数据库中的数据量增多，数据文件或日志文件容量不够就会自动扩展增大。（此方法不再说明）（ 2 ）、还可以使用修改文件的大小把数据文件改为 15M ，把日志文件改为 5M 。（介绍该方法）（ 3 ）、还可以使用第三种方法，为该数据库增加一个大小为 5MB 的数据文件、一个 2M 的日志文件来达到。（介绍该方法）


第二种方法：在 Management Studio 工具，“右击” JdglSys ，在弹出菜单中选择“属性”，就会弹出 “数据库属性”对话框。

选择“文件”，就

可以看到该数据库相关

的文件，修改其中的数

据文件的初始大小为 15M

，日志文件的初始大小为

5M ，点击“确定”即可。


第三种方法：

在 Management Studio 工具，“右击” JdglSys ，就会弹出 “数据库属性”对话框，直接点击下面的“添加”按钮，在其中添加一行，把逻辑名称修改为 JdglSys2 ，初始大小设为 5M ，其他不做修改。再点击“添加”按钮，添加一行，把逻辑名称修改为 JdglSys_log2 ，文件类型点击下拉菜单，修改为“日志”，初始大小设定为 2M ，点击确定。也能够实现将数据库扩容为 20M 。


图 4-16 使用 Management Studio 工具增加文件来扩大数据库


3. 添加文件组

在数据库中可以很方便的添加新的文件组，可以在Management Studio 工具，【选中】要添加文件组的数据库，【右击】，在弹出菜单中选中【属性】，选择【文件组】，点击下面的【添加】按钮，就可以添加新的文件组。

例如在数据库 JdglSys 中添加文件组 SECOND 。

4.3.2 使用 T-SQL 语言修改数据库数据库的修改操作还可以使用 T-SQL 语句中的 alter database 来进行修改完成。 Alter database 语句的语法格式是： ALTER DATABASE database_name{ADD FILE <filespec>[,…n][TO FILEGROUP{filegroup_name}]|ADD LOG FILE <filespec>[,…n]|REMOVE FILE logical_file_name [WITH DELETE]|MODIFY FILE < filespec >|MODIFYname= new_database_name|ADD filegroup filegroup_name|MODIFY filegroup filegroup_name DEFAULT|REMOVE filegroup filegroup_name}

4.3.2 使用 T-SQL 语言修改数据库其中：<filespec> 可以是： ( NAME = logical_file_name , FILENAME = 'os_file_name' , [ , SIZE = size] [ , MAXSIZE = { max_size | UNLIMITED } ] [ , FILEGROWTH = growth_increment ]) [ ,...n ]

4.3.2 使用 T-SQL 语言修改数据库可以使用 Alter Database命令来完成数据库修改操作。如：重命名数据库、扩大数据库、修改数据库文件、管理数据库文件组等。

重命名数据库使用 ALTER DATABASE 语句更改数据库名称的语法形式简写如下： ALTER DATABASE database_name Modify Name = new_database_name;

【例 4-2】把 demodb 数据库更名为 Studentdb 。 ALTER DATABASE demodb Modify Name =

Studentdb;

4.3.2 使用 T-SQL 语言修改数据库修改数据库文件

【例 4-3】数据库 JdglSys 的数据文件 JdglSys.mdf' 的初始分配大小为 10MB ，请将其大小扩充 20MB 。 ALTER DATABASE JdglSysMODIFY FILE(NAME =’JdglSys’,FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\JdglSys.mdf',

SIZE=20MB) GO

4.3.2 使用 T-SQL 语言修改数据库添加数据库文件

【例 4-4】给数据库 JdglSys添加一个新的数据文件，逻辑名称为 JdglSys3 ，物理名称及位置为： C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\JdglSys3.ndf 。大小为 5M ，每次增长 1M ，最大值为 15M 。增加一个日志文件逻辑名称为 JdglSys_log3 ，物理名称及位置：C:\Program Files\Microsoft SQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\JdglSys_log3.ldf 。大小为 2M ，每次增长 1M ，最大值 5M 。

4.3.2 使用 T-SQL 语言修改数据库ALTER DATABASE JdglSysADD FILE(NAME=JdglSys3,FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\JdglSys3.ndf', SIZE = 5MB , MAXSIZE = 15MB ,FILEGROWTH = 1MB)GoALTER DATABASE JdglSysADD LOG FILE(NAME=JdglSys_log3,FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\JdglSys_log3.ldf'’, SIZE = 1MB , MAXSIZE = 5MB ,FILEGROWTH = 1MB) Go

4.3.2 使用 T-SQL 语言修改数据库

4.3.2 使用 T-SQL 语言修改数据库添加数据文件到指定的文件组

【例 4-5 】给数据库 JdglSys添加一个新的数据文件到文件组SECOND ，逻辑名称为 JdglSys4 ，物理名称及位置为： C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\JdglSys4.ndf 。大小为 2M ，每次增长 1M ，最大值为 10M 。

4.3.2 使用 T-SQL 语言修改数据库alter database JdglSysadd file(NAME =JdglSys4,FILENAME ='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\JdglSys4.ndf',SIZE = 1MB , MAXSIZE = 10MB ,FILEGROWTH = 1MB)to filegroup secondGo；

4.3.2 使用 T-SQL 语言修改数据库管理数据库文件组

（ 1 ）、添加新的文件组在默认情况下，每一个数据库都有一个默认的 PRIMARY 文件组。这个 PRIMARY 文件组是不能被删除的。用户可以定义自己的文件组。使用 ALTER DATABASE新建文件组的命令如下：ALTER DATABASE database_name ADD FILEGROUP filegroup_name;

【例 4-6】给 JdglSys 数据库添加一个新的文件组 Third 。 ALTER DATABASE JdglSys ADD FILEGROUP third; （ 2 ）、修改数据库默认的文件组默认文件组是指在新增数据库数据文件时，如果没有明确指定，那么该文件将放置在默认文件组中。用户可以使用 ALTER DATABASE 语句设置指定数据库中的默认文件组，其命令如下：ALTER DATABASE database_name MODIFY FILEGROUP filegroup_name DEFAULT;

4.3.2 使用 T-SQL 语言修改数据库设置默认文件组时，只能将现有的文件组设置为默认文件

如图所示。

注意，不能在新建文件组的同时设置该文件组为默认文件组。在下图所示的示例中，在新建 FORTH 文件组的同时将其设置为默认文件组，结果操作失败。


虽然用户可以指定默认文件组，但是系统表等信息总是放在

PRIMARY 文件组中。如果文件组不再需要了，还可以将

该文件组删除。需要注意的是，只有当文件组中不再包含数

据文件时，才可以将该文件组删除。

4.3.2 使用 T-SQL 语言修改数据库【例 4-7】删除 JdglSys数据库的空文件组 Third。语句：ALTER DATABASE JdglSys REMOVE FILEGROUP third;执行结果如图。

4.4 数据库的分离和附加数据库的分离

分离数据库是指将数据库从 SQL Server 2008 R2 实例中删除，但是该数据库的数据文件和事务日志文件依然保持不变。这样可以将该数据库附加到任何的 SQL Server 2008 R2 实例中。

数据库的附加

当希望将分离后的数据库附加到某个 SQL Server 2008

R2 实例中时，可以使用 Management Studio 工具、也可

以使用 CREATE DATABASE 语句。附加数据库时所有的数

据文件必须都是可用的。

4.4.1 数据库的分离数据库的分离可以使用 Management Studio 工具来执

行分离操作，当然也可以使用 sp_detach_db 存储过程来执行数据库分离操作。

例如，如果想分离 JdglSys 数据库，使用 Management Studio 工具操作。步骤如下：

（ 1 ）选中 JdglSys 数据库，【右击】，在弹出的菜单中选中【任务】子菜单中选择【分离 ..】如图 4-25 ，会弹出【分离数据库】的界面。

4.4.1 数据库的分离（ 2 ）在分离数据库页面上，

可以对要分离的数据库做以设定，点击【确定】，即可。如下图所示。

图 4-25 分离数据库

4.4.1 数据库的分离也可以使用 sp_detach_db 系统存储过程来分离数据库，

执行如图 4-27 所示的命令。

图 4-27 使用系统存储过程分离数据库

4.4.2 数据库的附加如果希望将分离后的 JdglSys 数据库附加到指定的 SQL

Server 2008 R2 实例中，在 Management Studio 工具中操作如下：

（ 1 ）在 Management Studio 工具中选中最上端的【数据库】，【右击】在弹出的菜单中选择【附加…】，如图：

图 4-28 使用 Mangement Studio 工具附加数据库

4.4.2 数据库的附加（ 2 ）在弹出的【附加数据库】页面上，点击【添加】按钮，在弹出的【定位数据库文件】对话框中，选择 JdglSys 数据库的主数据文件JdglSys.mdf ，点击【确定】。如图 4-29 所示。

图 4-29 选中要附加数据库的主数据文件

4.4.2 数据库的附加（ 3 ）当选中附加数据库的主数据文件后，其他的数据库相关文件都已经关联在数据库中，如图 4-30 所示。最后，添加【确定】即可。

图 4-30 要附加数据库的其他数据文件

4.4.2 数据库的附加 CREATE DATABASE 语句来附加数据库原有的文件。【例 4-8】写 CREATE DATABASE 语句附加数据库 JdglSys 。语句：CREATE DATABASE JdglSysON(FILENAME = ' C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\JdglSys_Data.mdf')FOR ATTACHGO

4.5 数据库的收缩当用户数据库的数据增长到要超过它的使用空间时，必须增加用户数

据库的容量。相反，如果指派给用户数据库过多的存储空间，可以通过缩减或收缩数据库来减少存储空间的浪费。

SQL Server 2008R2采取预先分配空间的方法来建立数据库的数据文件或日志文件，比如数据文件的空间分配了 100MB ，而实际上只占用了 50MB 空间，这样就会造成存储空间的浪费。为此， SQL

Server 2008 R2 提供了收缩数据库的功能，允许对数据库中的每个文件进行收缩，删除已经分配但没有使用的页。但注意，不能将整个数据库收缩到比其原始大小还要小。因此，如果数据库创建时的大小为10MB ，后来增长到 100MB ，则将该数据库最小能够收缩到 10MB

（即使假定已经删除该数据库中所有的数据）。数据库的收缩可以收缩整个数据库，也可以收缩单个文件（因日志文件收缩和数据文件差别较大，此处暂不考虑单个日志文件的收缩）。

4.5 数据库的收缩当用数据库的收缩可以使用 Mangement Studio 工具，也

可以使用 T-SQL 语句完成。

SQL Server 2008 R2支持对数据库实行自动收缩和手工收缩。

4.5.1 使用 Mangement Studio 工具收缩用户数据库

1.自动收缩数据库：设定 JdglSys 数据库的收缩为自动收缩。（ 1 ）、在 Management Studio 工具中选择 JdglSys 数据库，【右击】该数据库，在弹出的菜单中选择【属性】，就会弹出数据库属性页面。（ 2 ）、在弹出数据库属性页面上选中“选项”，在其他选项中“自动”的“自动收缩”的值选定为“ True” 。点击【确定】即可。如图 4-32 自动收缩数据库。

图 4-32 自动收缩数据库


2.手动收缩数据库：设定 JdglSys 数据库的收缩为手动收缩。（ 1 ）、选中要收缩的数据库，【右击】，在弹出的快捷菜单中选择【任务 —】 > 【收缩 —】 >【数据库】。如左图所示。（ 2 ）、在弹出收缩数据库界面图上，即可设定手动收缩数据库。如右图。


1.自动收缩数据库：设定 JdglSys 数据库的收缩为自动收缩。（ 1 ）、在 Management Studio 工具中选择 JdglSys 数据库，【右击】该数据库，在弹出的菜单中选择【属性】，就会弹出数据库属性页面。（ 2 ）、在弹出数据库属性页面上选中“选项”，在其他选项中“自动”的“自动收缩”的值选定为“ True” 。点击【确定】即可。如图 4-32 自动收缩数据库。

图 4-32 自动收缩数据库

4.5.2 使用 T-SQL 语句收缩数据库在 SQL Server 2008 R2 系统中，使用 T-SQL 语句收缩数

据库也有 3 种方式。第 1 种方式是设置数据库为自动收缩，可以通过设置 AUTO_SHRINK 数据库选项实现。第 2 种方式是收缩整个数据库的容量，可以通过使用 DBCC

SHRINKDATABASE命令完成。第 3 种方式是收缩指定的数据文件，这可以使用 DBCC SHRINKFILE命令实现。

DBCC SHRINKDATABASE 的语句格式如下：DBCC SHRINKDATABASE （ database_name | database_id | 0 [ ,

target_percent ] )其中： database_name | database_id | 0 ：要收缩的数据库的名称或 ID 。

如果指定 0 ，则使用当前数据库。target_percent ：数据库收缩后的数据库文件中所需的剩余可用空间百分比。

4.5.2 使用 T-SQL 语句收缩数据库例如：将 JdglSys 数据库的空间缩减至可用剩余空间为 60% 。语句为：DBCC SHRINKDATABASE('JdglSys',60);

图 4-36 设定手动收缩文件

4.6 数据库删除当不需要数据库时，可以删除它，即删除数据库和数据库

使用的磁盘文件。删除数据库可以通过 Management

Studio 工具删除，也可以使用 T-SQL 语句删除数据库。 1. 使用 Management Studio 工具删除数据库删除数据库的步骤：选中要删除的数据库，【右击】，在弹出的菜单中选择【删除】，就会弹出【删除对象】页面，在其上就是要删除的数据库信息，点击【确定】即可删除该数据库。如图4-38 删除数据库。

图 4-38 删除数据库

4.6 数据库删除

2. 使用 T-SQL 语言删除数据库

删除数据库也可以是用 DROP DATABASE 语句来删除。 DROP DATABASE 语句的语法是： DROP DATABASE database_name[,…n]; 例如：使用 DROP DATABASE 语句删除数据库 Studentdb 。语句为： DROP DATABASE Studentdb;

4.7 数据库的备份和还原尽管 SQL Server 2008 R2 提供了内置的安全性和数据保护，但是病毒破坏、计算机硬件设备故障以及误操作等很可能导致数据的丢失和破坏。为了能尽快恢复系统的正常工作并把损失降低到最低，必须对系统数据进行备份，以便在需要时能够及时恢复数据。

在 SQL Server 2008 R2 中，可以备份整个数据库，或者备份一个或多个文件或文件组。所以， SQL Server 2008

R2支持 4 种备份形式：完全数据库备份（数据库备份）、差异数据库备份（增量备份）、事务日志备份、文件或文件组备份

4.7 数据库的备份和还原完全数据库备份：数据库备份是指对数据库的完整备份，包括所有的数据库对

象、数据和事物日志中的事务。这种备份方式非常简便易行，通常按照一个常规的时间间隔进行。在还原数据库时，只需用简单的操作即可完成数据库的恢复。恢复后的数据库与备份完成时的数据库状态一致。

差异数据库备份（增量备份）：是指将最近一次完全数据库备份以来发生的数据变化备份起

来，因此差异数据库备份实际上是一种增量数据库备份。对于一个经常进行数据操作的数据库而言，需要在完全数据库备份的基础上，进行差异备份。

4.7 数据库的备份和还原事务日志备份：是对数据库发生的事务进行备份，包括从上次事务日志备

份、差异备份和完全数据库备份后，数据库已经执行完成的所有事务。它可以在相应的数据库备份的基础上，将数据库恢复到特定的即时点或恢复到故障点时的状态。

文件或文件组备份指对数据库文件或文件夹进行备份，但其不像完全数据库备份

那样同时也进行事务日志备份。使用该方法可提高数据库恢复的速度，因为仅对遭到破坏的文件或文件组进行恢复。

4.7.1 数据库备份在进行备份之前需要先指定或创建备份设备。备份设备是

指 SQL Server 2008 R2 中存储数据库、事务日志或文件及文件组备份的存储介质。当建立一个备份设备时，需要给其分配一个逻辑名和一个物理名。物理名是操作系统用来标识备份设备的名称。逻辑名是用来标识物理备份设备的别名。逻辑名称永久地存储在 SQL Server 2008 R2 的系统表中，使用逻辑名比物理名简单的多。

通过创建酒店管理数据库 JdglSys 的备份来说明数据库的备份。

4.7.1 数据库备份 1. 创建备份设备方法有两种：使用 Mangement Studio 工具和使用系统存储存储 sp_addumpdevice 。此处仅介绍使用 Mangement Studio 工具创建备份设备。步骤如下：（ 1 ）在“对象资源管理器”窗口中，【单击】服务器名称以展开服务器，找到【服务器对象】并点击展开，然后，选中【备份设备】（ 2 ）选中【备份设备】右击，在弹出的菜单中选择【新建备份设备】，打开【备份设备】对话框，如图 4-39 所示。（ 3 ）在对话框中，输入要创建的设备名称及目标设备或文件，单击【确定】即可。如图 4-40 定位备份设备对应的数据库文件所示。

4.7.1 数据库备份

图 4-39 “ ”备份设备对话框

图 4-40 定位备份设备对应的数据库文件

4.7.1 数据库备份 2. 备份操作创建好某个备份设备后，就可以将该数据库备份到此设备上。备份操作的方法也有两种，可以在 Management Studio 工具中进行，也可以使用 BACKUP DATABASE 语句来进行备份。

（ 1 ）使用 Mangement Studio 工具执行备份操作

1 ）在“对象资源管理器”窗口中，【单击】服务器名称以展开服务器，找到【数据库】并点击展开，然后，选中要备份的数据库。 2 ）右击选中的备份数据库，在弹出的菜单中选择【任务】，级联菜单中选择【备份…】，将弹出【备份数据库】对话框。如图 4-41 所示。 3 ）在“备份类型”列表框中，选择类型“完整”。创建完整数据库备份之后，可以创建差异数据库备份。对于“备份组件”，选择“数据库”，也可以根据需要选择“文件组”。在目标部分，可以选择添加或删除其他备份设备。最后单击【确定】即可。

4.2.2 使用 T-SQL 语言创建数据库图 4-42 “备份数据库”成功

图 4-41 “ ”备份数据库对话框

图 4-42 “ 备份数据库”成功

4.2.2 使用 T-SQL 语言创建数据库（ 2 ）使用 BACKUP DATABASE 语句来执行备份操作执行 BACKUP DATABASE 语句可以创建完整数据库备份，同时指定要备份的数据库名称和写入完整数据库备份的备份设备。完整数据库备份的语法格式如下：BACKUP DATABASE database_name TO <backup_device> [ ,...n ] [ WITH DIFFERENTIAL ][;]备份事务日志的语句为：BACKUP LOG database_name TO <backup_device> [ ,...n ] [ WITH NO_TRUNCATE] [;]

4.2.2 使用 T-SQL 语言创建数据库其中：<backup_device> ：指定用于备份操作的逻辑备份设备名或物理备份设备。WITH DIFFERENTIAL ：指定数据库备份或文件备份应该只包含上次完整备份后更改的数据库或文件部分。WITH NO_TRUNCATE ：指定不截断日志。

4.2.2 使用 T-SQL 语言创建数据库【例 4-9】完整备份数据库 JdglSys到指定的备份设备JdglSys_backup 上。语句为： backup database JdglSys to JdglSys_backup;

图 4-43 完全备份数据库

4.2.2 使用 T-SQL 语言创建数据库【例 4-10】差异备份数据库 JdglSys到指定的备份设备JdglSys_backup 上。语句为： backup database JdglSys to JdglSys_backup with differential;

图 4-44 差异备份数据库

4.2.2 使用 T-SQL 语言创建数据库【例 4-11】备份数据库 JdglSys 的日志到备份设备JdglSys_backup ，不截断日志（默认为截断）。语句为： backup log JdglSys to JdglSys_backup with NO_TRUNCATE;

图 4-45 备份事务日志

4.7.2 数据库还原还原是备份的逆向操作。可以通过 Mangement Studio

工具和使用 SQL 语句两种方法来进行还原。此处仅介绍使用工具还原数据库。

步骤如下：（ 1 ）在“对象资源管理器”窗口中，【单击】服务器名称以展开服务器，找到【数据库】并点击展开，然后，选中要还原的数据库。

（ 2 ）右击选中的还原数据库，在弹出的菜单中选择【任务】，级联菜单中选择【还原…】，在下一级菜单中选择还原类型，将弹出【还原数据库】对话框。

（ 3 ）在“选择用于还原的备份集”中，选择类型还原。注意：此处，必须先做一次完全数据库备份还原，才可以进行差异或事务日志还原。最后单击【确定】即可。

4.8 本章小结本章中阐述了 SQL Server 2008 R2 数据库管理，涉及到

的主要内容包括：

数据库的存储结构，介绍了两方面，包括逻辑存储结构（表现为各种数据库对象）和物理存储结构（指各种类型的数据库文件）并简单介绍 SQL Server 2008 R2 系统中的自带数据库。

接着介绍数据库管理方法的常用管理，包括数据库的创建、修改、分离与附加、收缩、删除、备份和还原。每一种管理基本上是用两种方法来管理，一是 Management Studio 工具、二是 T-SQL 语句。在数据库创建的小节中除了介绍用两种方法创建数据库，还介绍了查看数据库相关信息的方法，这些查看的方法在每做过一种管理之后，都有可能被用到去查看是否达到想要的管理效果。

4.8 本章小结修改数据库涉及到了数据库的更名、扩大容量（ 3 种方法：自动增长、扩大数据库文件、增加数据库文件）、添加文件组。数据库的分离和附加在把数据库从一个实例转移到另一个实例时很有用。收缩数据库可以帮助我们收回分配给数据库的过多的空闲空间。不需要时可以删除数据库，注意删除某用户数据库之后，要及时备份 master 数据库。从安全的角度看，管理数据库还包括数据库的备份和还原。在备份和还原一小节中，介绍了备份和还原的基本知识和操作。

第 4 章 SQLSERVER 数据库管理

Documents

Transcript of 第 4 章 SQLSERVER 数据库管理