前置知識
- 權限:CREATE DATABASE / CREATE ANY DATABASE / ALTER ANY DATABASE
- 上限:單實例 32,767 個資料庫
- 檔案結構:至少 1 個資料檔 + 1 個日志檔;資料檔分 主檔 和 次要檔;日志檔可多個但同一時間只寫一個(循環寫入)。
- 最佳實踐:資料檔與日志檔置於不同磁碟/RAID 組,分離隨機讀寫與循序寫入 I/O。
- 檔案組:邏輯容器,管理多檔案。主檔組含主檔+未歸屬次要檔;可建使用者檔案組,實現表/索引放置特定磁碟(如熱表上 SSD)。
七大資料庫狀態
| 狀態 | 英文 | 可用性 | 典型場景 |
| 線上 | ONLINE | ✅ | 正常服務 |
| 離線 | OFFLINE | ❌ | 維護/移動檔案 |
| 還原中 | RESTORING | ❌ | RESTORE 執行中 |
| 恢復中 | RECOVERING | ❌ | 啟動/還原後 REDO/UNDO 階段 |
| 恢復待完成 | RECOVERY_PENDING | ❌ | 缺檔/損壞,需人工介入 |
| 可疑 | SUSPECT | ❌ | 主檔組損壞,自動恢復失敗 |
| 緊急模式 | EMERGENCY | 唯讀/單用戶 | sysadmin 緊急搶救資料 |
CREATE DATABASE 範例速查
-- 1. 全預設
CREATE DATABASE TestDB;
-- 2. 指定資料檔路徑
CREATE DATABASE TestDB2 ON (NAME=TestDB2, FILENAME='D:\DB\TestDB2.mdf');
-- 3. 完整規格:大小、上限、增長
CREATE DATABASE TestDB3 ON (NAME=TestDB3, FILENAME='D:\DB\TestDB3.mdf', SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=5%);
-- 4. 獨立指定日志檔
CREATE DATABASE TestDB4 ON (NAME=TestDB4_Data, FILENAME='D:\DB\TestDB4_Data.mdf', SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=5%)
LOG ON (NAME=TestDB4_Log, FILENAME='E:\Log\TestDB4_Log.ldf'); -- 日志放不同磁碟 E:
-- 5. 多檔案 + 多檔案組
CREATE DATABASE TestDB5 ON
(NAME=F1, FILENAME='D:\DB\F1.mdf', SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=5%),
(NAME=F2, FILENAME='D:\DB\F2.ndf', SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=5%),
FILEGROUP FG1 (NAME=FG1_F1, FILENAME='D:\DB\FG1_F1.ndf', SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=5%),
FILEGROUP FG2 (NAME=FG2_F1, FILENAME='D:\DB\FG2_F1.ndf', SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=5%),
(NAME=FG2_F2, FILENAME='D:\DB\FG2_F2.ndf', SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=5%)
LOG ON (NAME=Log1, FILENAME='E:\Log\Log1.ldf');
-- 6. 指定排序規則
CREATE DATABASE TestDB6 ON (NAME=TestDB6, FILENAME='D:\DB\TestDB6.mdf')
COLLATE Chinese_PRC_CI_AS; -- 查詢所有排序規則:SELECT * FROM ::fn_helpcollations();
ALTER DATABASE 常用操作
| 操作 | T-SQL 範例 |
| 改名 | ALTER DATABASE OldName MODIFY NAME = NewName; 或 EXEC sp_renamedb 'Old','New'; |
| 新增資料檔 | ALTER DATABASE DB ADD FILE (NAME=NewFile, FILENAME='D:\DB\NewFile.ndf'); |
| 新增日志檔 | ALTER DATABASE DB ADD LOG FILE (NAME=NewLog, FILENAME='E:\Log\NewLog.ldf', SIZE=3MB, MAXSIZE=50MB, FILEGROWTH=10%); |
| 修改檔案名/路徑 | ALTER DATABASE DB MODIFY FILE (NAME=OldLogical, NEWNAME=NewLogical, FILENAME='D:\DB\NewPhysical.ndf'); -- 需 OFFLINE/Detach 移動實體檔後再 Attach |
| 刪除檔案 | ALTER DATABASE DB REMOVE FILE LogicalName; -- 檔案必須為空 |
| 新增檔案組 | ALTER DATABASE DB ADD FILEGROUP FG_New; |
| 改檔案組名 | ALTER DATABASE DB MODIFY FILEGROUP FG_Old NAME = FG_New; |
| 設定預設檔案組 | ALTER DATABASE DB MODIFY FILEGROUP FG_New DEFAULT; |
| 刪除檔案組 | ALTER DATABASE DB REMOVE FILEGROUP FG_Name; -- 組內必須無檔案 |
| 修改排序規則 | ALTER DATABASE DB COLLATE Chinese_PRC_CI_AS_KS; -- 僅影響新建表/欄位,舊物件需重建 |
SSMS 圖形化介面同樣支援上述所有操作