萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 2008 R2 資料庫管理全攻略:建立、修改、檔案組、排序規則、狀態解析

前置知識

  • 權限: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 圖形化介面同樣支援上述所有操作

copyright © 萬盛學電腦網 all rights reserved