T-SQL ALTER DATABASE 完全手冊：重命名、擴充檔案、檔案群組管理與預設群組切換

前言

資料庫上線後，結構調整在所難免：更名、擴充檔案、新增檔案群組、調整預設群組……本文系統整理 ALTER DATABASE 核心語法與生產環境實戰腳本，助你安全、零停機完成變更。

一、資料庫重命名

-- 方法一：ALTER DATABASE (SQL 2005+)
ALTER DATABASE OldName MODIFY NAME = NewName;

-- 方法二：系統程序 (相容舊版)
EXEC sp_renamedb 'OldName', 'NewName';

• 需單一使用者模式或確保無作用中連線，否則報錯。
• 系統資料庫不可重命名。

二、新增資料檔與記錄檔

2.1 動態取得預設路徑並新增資料檔

USE master; GO
DECLARE @data_path NVARCHAR(256);
SELECT @data_path = physical_name
FROM MyDatabase.sys.database_files
WHERE file_id = 1;  -- 主資料檔

SET @data_path = SUBSTRING(@data_path, 1,
    CHARINDEX(LOWER('MyDatabase.mdf'), LOWER(@data_path)) - 1);

ALTER DATABASE MyDatabase
ADD FILE (
    NAME = 'LogicNameOfDataFile1_1',
    FILENAME = @data_path + 'DataFile1_1.ndf',
    SIZE = 500MB, MAXSIZE = 100GB, FILEGROWTH = 256MB
);

2.2 新增記錄檔

ALTER DATABASE MyDatabase
ADD LOG FILE (
    NAME = 'LogicNameOfLogFile1_1',
    FILENAME = @data_path + 'LogFile1_1.ldf',
    SIZE = 200MB, MAXSIZE = 50GB, FILEGROWTH = 128MB
);

2.3 一次新增多檔案至新檔案群組

-- 1. 建立檔案群組
ALTER DATABASE MyDatabase ADD FILEGROUP UserFG1_1; GO

-- 2. 新增兩個檔案到該群組
ALTER DATABASE MyDatabase
ADD FILE (
    NAME = 'LogicNameOfDataFile1_2',
    FILENAME = @data_path + 'DataFile1_2.ndf',
    SIZE = 1000MB, MAXSIZE = 500GB, FILEGROWTH = 512MB
),
(
    NAME = 'LogicNameOfDataFile1_3',
    FILENAME = @data_path + 'DataFile1_3.ndf',
    SIZE = 500MB, MAXSIZE = 500GB, FILEGROWTH = 256MB
)
TO FILEGROUP UserFG1_1;

注意：未指定 TO FILEGROUP 者預設進 PRIMARY 群組。

三、修改檔案屬性

3.1 修改資料檔：重命名、移動路徑、調整大小/成長

ALTER DATABASE MyDatabase2
MODIFY FILE (
    NAME = 'LogicNameOfDataFile2',
    NEWNAME = 'NewLogicNameOfDataFile2',
    FILENAME = 'D:\NewPath\NewDataFile2.mdf',  -- 需先離線移動實體檔
    SIZE = 25GB, MAXSIZE = 150GB, FILEGROWTH = 10GB
);

• 移動檔案步驟：ALTER ... MODIFY FILE (FILENAME=...) → 離線資料庫 → 移動實體檔 → 上線資料庫。

3.2 修改記錄檔

ALTER DATABASE MyDatabase3
MODIFY FILE (
    NAME = 'MyDatabase3_log',
    FILENAME = 'L:\NewLog\LogFile3.ldf',
    SIZE = 10GB, MAXSIZE = 50GB, FILEGROWTH = 5GB
);

四、檔案群組進階管理

4.1 重命名檔案群組

ALTER DATABASE MyDatabase6
MODIFY FILEGROUP UserFG6_2 NAME = NewUserFG6_2;

4.2 切換預設檔案群組

-- 將新建表預設放入 NewUserFG6_2
ALTER DATABASE MyDatabase6 MODIFY FILEGROUP NewUserFG6_2 DEFAULT;

-- 還原給 PRIMARY
ALTER DATABASE MyDatabase6 MODIFY FILEGROUP [PRIMARY] DEFAULT;

• 僅影響後續 CREATE TABLE 未指定 ON 子句時的行為。
• 系統物件永遠在 PRIMARY。

五、生產環境變更檢核清單

1. 備份先行：變更前執行完整備份 + 記錄備份。
2. 維護視窗：檔案移動需離線，安排低峰期；單純調整 SIZE/FILEGROWTH 可線上執行。
3. 磁碟空間：目標路徑預留 2 倍以上空間。
4. 權限確認：SQL Server 服務帳號對新路徑具讀寫權限。
5. 驗證腳本：先在測試環境跑通全流程。

六、常見錯誤與對策