SQL Server数据库创建及维护

1、SQL Server数据库概述

• SQL Server的发展史

SQL Server 7.0（1999年，正式跻身企业数据库行列）

SQL Server 2000（2000年，代表产品）

SQL Server 2005（2005年，代号“Yukon”）

SQL Server 2008（2008年，重大的产品版本）

SQL Server 2012

SQL Server 2014

• SQL Server的特色

保护数据库查询

在服务器的管理操作上花费更少的时间

增加应用程序稳定性

系统执行效能优化与预测功能

• SQL Server的版本

企业版（Enterprise Edition）：为企业级应用提供综合数据平台。

标准版（Standard Edition）：为部门级应用提供支持。

开发版（Developer Edition）：拥有企业版特性，使用限制。

工作组版（Workgroup Edition）：为各分支应用提供支持。

网络版（Web Edition）：提供低成本的WEB应用解决方案。

移动版（Compact） 支持移动设备。

免费版（Express)：适用于学习及构建小型应用。

• SQL Server中数据库的分类

系统数据库（自动创建）

master：所有系统级信息，元数据、端点、连接服务器和系统配置。
  msdb：代理服务调度报警和作业、记录操作员时使用保存此类信息。
  tempdb：用于保存临时对象和中间结果，每次启动会重新创建。
  model:  所有数据库的模板，存放用户数据库公共信息。
  resource：只读数据库。在对象资源管理器中看不到。
           

用户数据库（保存与用户业务有关的数据）

2、SQL Server数据库的组成

• SQL Server将数据库映射为一组操作系统文件：

数据文件

.mdf：主要数据文件。只有一个，大小不得小于3MB。

.ndf ：次要数据文件。有0个或多个，可在一个磁盘或多个磁盘存放。

日志文件

.ldf：事务日志文件。至少有一个日志文件。

• 数据库存储空间的分配

创建用户数据库时，model数据库被自动复制到新建库。

数据存储的最小单位：数据页（Page，简称页）。

1页是一块8KB的连续磁盘空间。

页的大小决定了数据库表中一行数据的最大大小。

行不能垮页存储。

例：一个数据表10000行数据，每行3000字节，计算表需要的存储空间。

答案：10000/2*8KB=40MB

空间利用率75%

3、数据库文件组

• 两种类型的文件组：

主文件组（PRIMARY）

系统定义，包含主要数据文件和任何没有明确分配的其他文件组的其他数据文件，系统表所有页均分配在主文件组中。

用户定义文件组

在定义或修改数据库时用FILEGROUP关键字指定。

• 注意：

1. 日志文件不在文件组中，日志空间与数据空间分开管理。
2. 一个文件不可以是多个文件组成员。
3. 若文件组包含多个文件，则在所有文件被填满后才会自动循环增长。
4. 文件加入数据库中后，不能移动到其他文件组。
5. 只能指定一个文件组为默认文件组。

4、数据库文件的属性

• 定义数据库的数据文件和日志文件所需信息：

文件名及其位置

逻辑文件名，物理文件名

初始大小

不能小于model数据库主要数据文件的大小

增长方式

可指定文件是否自增长（默认）

最大大小

文件增长的最大限制。默认无限制

5、用T-SQL创建数据库

• 创建数据库一般有两种方式：

通过SQL Server Management Studio创建数据库

通过T-SQL语句创建数据库

• 说明：

PRIMARY ：指定为主要数据库文件，没有指定默认第一个文件是主要数据文件。

LOG ON ：自动创建日志文件，大小为数据文件总和25%或512KB中大的。

NAME ：逻辑文件名，唯一。

FILENAME ：物理文件名。

SIZE：初始大小，.mdf大小不小于model，.ndf默认为1MB。

MAXSIZE：最大大小，未指定则文件自动增长到磁盘满。

UNLIMITED ：增长无限制，一般指定为日志文件2TB，数据文件16TB.

FILEGROWTH：指定文件自动增量，不超过MAXSIZE.默认数据文件1MB，日志文件为当前文件的10%。

FILEGROUP ：文件组逻辑名，唯一，不能是系统名。

DEFAULE ：指定该文件组为默认文件组。

6、修改数据库

• 扩大数据库空间

扩大数据库中已有文件的大小

为数据库添加新的文件

• 收缩数据库空间

即释放数据库中未使用的空间，文件的收缩从末尾开始。

自动收缩：AUTO_SHRINK，默认false。

手工收缩：收缩数据库中某个文件大小；

按比例收缩整个数据库大小。

• 添加和删除数据库文件

ALTER DATABASE database 
{ ADD FILE < filespec > [ ,...n ] [ TO FILEGROUP filegroup_name ] 
| ADD LOG FILE < filespec > [ ,...n ] 
| REMOVE FILE logical_file_name 
| 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 > 
}
           

• 扩大指定文件的大小：

ALTER DATABASE STU_DB 
MODIFY FILE（NAME=student_data,  SIZE=8MB）
           

• 添加新的数据文件：

ALTER DATABASE STU_DB
ADD FILE（NAME=student_data2，
FILENAME=‘E:\Data\student_data2.ndf’,
SIZE=6MB,FILEGROWTH=0）
           

• 收缩整个数据库的大小：

DBCC SHRINKDATABASE

• 收缩指定文件的大小：

DBCC SHRINKFILE

1. 例1：DBCC SHRINKDATABASE（students，20）

   收缩数据库，该数据库所用文件都有20%可用空间

2. 例2：DBCC SHRINKFILE（students_data1，4）

   收缩数据库到4MB大小

• 删除数据库文件：

ALTER DATABASE STU_DB

REMOVE FILE student_log1

• 注意：

添加文件时，每个文件组中的数据文件按比例填充，日志文件是依次增加的。

文件为空才能删除。

7、分离和附加数据库

• 分离数据库

作用：实现将数据库从一台数据库服务器移到另一台，不需要重建。

从实例中删除，不删除数据文件和日志文件，保持了数据文件和日志文件完整一致。

使用sp_detach_db系统存储过程实现

如：EXEC sp_detach_db‘student’，‘true’

• 附加数据库

将分离的数据库重新附加到数据库管理系统中。

必须指定主要数据文件的物理存储位置和文件名。

CREATE DATABASE ……

FOR ATTACH|ATTACH_REBUILD_LOG

例：

CREATE DATABASE students
On（FILENAME=‘F：\Data\Students_data1.mdf’）
FOR ATTACH