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