您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2008 R2 資料庫管理全攻略：建立、修改、檔案群組、狀態、排序規則與權限實戰

編輯：關於SqlServer

SQL Server 2008 R2 資料庫管理核心知識體系：從建立資料庫語法細節、檔案與檔案群組架構設計、7 種資料庫狀態解析，到 ALTER DATABASE 修改實戰 19 個範例 (改名、增減檔案、檔案群組操作、唯讀、單用戶、自動收縮、排序規則變更)，並說明建立資料庫所需權限與上限限制，適合 DBA 系統性掌握與日常操作速查。

一、預備知識提要

1. 建立資料庫所需權限

CREATE DATABASE
CREATE ANY DATABASE
ALTER ANY DATABASE

2. 資料庫數量上限

單一執行個體最多 32,767 個資料庫，超過建立失敗。

3. 檔案與檔案群組架構

類型	邏輯名	副檔名	說明
主要資料檔	任意	.mdf	每資料庫僅一個，含系統資料表、配置頁
次要資料檔	任意	.ndf	可多個，分散 I/O、跨磁碟擴展
主要記錄檔	任意	.ldf	至少一個，含交易記錄
次要記錄檔	任意	.ldf	可多個，循環寫入不分散 I/O

最佳實務：資料檔 (.mdf/.ndf) 與記錄檔 (.ldf) 放置於不同實體磁碟/RAID 群，分離隨機讀寫 (資料) 與循序寫入 (記錄)，大幅提升效能。

檔案群組：邏輯容器，管理多個資料檔。
• PRIMARY：預設群組，含主要資料檔 + 未指定群組的次要檔。
• 使用者自訂群組：將特定表/索引放入指定群組，實現熱點資料放 SSD、歸檔資料放 HDD 等分級存儲。

4. 7 種資料庫狀態

狀態	英文	可用性	典型觸發情境
線上	ONLINE	✅ 正常存取	正常運行
離線	OFFLINE	❌ 不可存取	`ALTER DATABASE SET OFFLINE`
還原中	RESTORING	❌	RESTORE DATABASE 進行中
復原中	RECOVERING	❌ 暫時	啟動/還原完成後自動轉 ONLINE
復原等待	RECOVERY PENDING	❌ 需人工介入	檔案遺失、記錄檔損壞
可疑	SUSPECT	❌ 損壞	啟動復原失敗，需緊急修復
緊急	EMERGENCY	⚠️ 唯讀、單用戶、sysadmin	人工設定，用於災難搶救

二、建立資料庫實戰範例 (CREATE DATABASE)

範例 1：全預設值

CREATE DATABASE 測試資料庫;

範例 2：指定資料檔路徑

CREATE DATABASE 範例二資料庫
ON (NAME = N'範例二資料庫', FILENAME = N'D:\DBtest\範例二資料庫.mdf');

範例 3：指定初始/最大大小與成長量

CREATE DATABASE 範例三資料庫
ON (NAME = N'範例三資料庫', FILENAME = N'D:\DBtest\範例三資料庫.mdf',
    SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 5%);

範例 4：同時指定資料檔與記錄檔

CREATE DATABASE 範例四資料庫
ON (NAME = N'範例四資料檔', FILENAME = N'D:\DBtest\範例四資料檔.mdf',
    SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 5%)
LOG ON (NAME = N'範例四記錄檔', FILENAME = N'D:\DBtest\範例四記錄檔.ldf');

範例 5：5 個資料檔 + 2 個自訂檔案群組 (分級存儲典範)

CREATE DATABASE 範例五資料庫
ON 
(NAME = N'檔案1', FILENAME = N'D:\DBtest\檔案1.mdf', SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=5%),
(NAME = N'檔案2', FILENAME = N'D:\DBtest\檔案2.ndf', SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=5%),
FILEGROUP 群組1
(NAME = N'群組1檔案', FILENAME = N'D:\DBtest\群組1檔案.ndf', SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=5%),
FILEGROUP 群組2
(NAME = N'群組2檔案1', FILENAME = N'D:\DBtest\群組2檔案1.ndf', SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=5%),
(NAME = N'群組2檔案2', FILENAME = N'D:\DBtest\群組2檔案2.ndf', SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=5%)
LOG ON (NAME = N'記錄檔', FILENAME = N'D:\DBtest\記錄檔.ldf');

範例 6：指定排序規則

CREATE DATABASE 範例六資料庫
ON (NAME = N'範例六資料庫', FILENAME = N'D:\DBtest\範例六資料庫.mdf')
COLLATE Chinese_PRC_CI_AS;  -- 簡體中文、不區分大小寫、區分重音

查詢所有可用排序規則：SELECT * FROM ::fn_helpcollations()

三、修改資料庫實戰範例 (ALTER DATABASE) — 19 例全覆蓋

SSMS 資料庫屬性-檔案頁面

#	操作	關鍵語法	注意事項
7	改名	`ALTER DATABASE 舊名 MODIFY NAME = 新名` 或 `EXEC sp_renamedb '舊','新'`	需獨佔模式或單用戶模式
8	新增資料檔	`ADD FILE (NAME=..., FILENAME=...)`	可指定 FILEGROUP
9	新增記錄檔	`ADD LOG FILE (NAME=..., FILENAME=..., SIZE=3MB, MAXSIZE=50MB, FILEGROWTH=10%)`	記錄檔不屬檔案群組
10	修改檔案 (改名/移動)	`MODIFY FILE (NAME=舊邏輯名, NEWNAME=新邏輯名, FILENAME=新路徑)`	路徑變更需 OFFLINE → 移動檔案 → ONLINE
11	變更排序規則	`COLLATE Chinese_PRC_CI_AS_KS`	僅影響新建物件，現有欄位需 ALTER TABLE ALTER COLUMN
12	移除資料檔	`REMOVE FILE 邏輯名`	檔案必須為空 (資料已遷移)
13	新增檔案群組	`ADD FILEGROUP 群組名`
14	檔案群組改名	`MODIFY FILEGROUP 舊名 NAME=新名`
15	新增檔案到群組並設為預設	兩條語句：`ADD FILE ... TO FILEGROUP 群組` + `MODIFY FILEGROUP 群組 DEFAULT`	`ALTER DATABASE` 一次僅能修改一個屬性
16	刪除檔案群組 (含預設群組)	三步驟： 1. `MODIFY FILEGROUP [PRIMARY] DEFAULT` 2. `REMOVE FILE 檔案` 3. `REMOVE FILEGROUP 群組`	PRIMARY 為保留字需括號 `[PRIMARY]`
17	設定檔案群組唯讀	三步驟： 1. `ADD FILEGROUP 群組` 2. `ADD FILE ... TO FILEGROUP 群組` 3. `MODIFY FILEGROUP 群組 READ_ONLY`	PRIMARY 不可設唯讀；空群組不可設唯讀
18	設定單用戶模式	`SET SINGLE_USER`	立即斷開其他連線 (WITH ROLLBACK IMMEDIATE)
19	啟用自動收縮	`SET AUTO_SHRINK ON`	生產環境強烈不建議，產生碎片、CPU 飆升、計畫失效

四、關鍵操作補充說明

檔案移動標準流程 (範例 10 進階)

ALTER DATABASE db MODIFY FILE (NAME=logical, FILENAME='new_path')
ALTER DATABASE db SET OFFLINE
OS 層移動實體檔案至新路徑
ALTER DATABASE db SET ONLINE

排序規則變更影響範圍

資料庫層級 COLLATE：僅作為新建表/欄位的預設值。
現有欄位排序規則 不自動變更，需逐欄 ALTER TABLE ALTER COLUMN col TYPE COLLATE new_collate。
表達式比較、排序、索引鍵順序均受影響，變更前務必完整測試。

AUTO_SHRINK 為何有害

背景執行緒週期性檢查並縮減檔案 → 大量頁面移動 → 索引嚴重碎片化。
縮減後檔案再成長 → 檔案系統碎片 + VLF 碎片。
觸發時機不可控，可能衝突生產尖峰負載。
替代方案：手動排程 DBCC SHRINKFILE (TRUNCATEONLY) 於維護窗口，並設定合理 FILEGROWTH (固定 MB 而非 %)。

掌握 CREATE/ALTER DATABASE 完整語法與檔案群組設計哲學，是 DBA 實現儲存分級、效能隔離、高可用架構的核心技能。