ALTER DATABASE 檔案及檔案群組選項 (Transact-SQL)

發行項
08/27/2013

修改與資料庫相關聯的檔案和檔案群組。在資料庫中新增或移除檔案和檔案群組，以及變更資料庫或其檔案和檔案群組的屬性。如需其他 ALTER DATABASE 選項，請參閱＜ALTER DATABASE (Transact-SQL)＞。

語法

ALTER DATABASE database_name 
{
    <add_or_modify_files>
  | <add_or_modify_filegroups>
}
[;]

<add_or_modify_files>::=
{
    ADD FILE <filespec> [ ,...n ] 
        [ TO FILEGROUP { filegroup_name } ]
  | ADD LOG FILE <filespec> [ ,...n ] 
  | REMOVE FILE logical_file_name 
  | MODIFY FILE <filespec>
}

<filespec>::=  
(
    NAME = logical_file_name  
    [ , NEWNAME = new_logical_name ] 
    [ , FILENAME = {'os_file_name' | 'filestream_path' } ] 
    [ , SIZE = size [ KB | MB | GB | TB ] ] 
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] 
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ] 
    [ , OFFLINE ]
)  

<add_or_modify_filegroups>::=
{
    | ADD FILEGROUP filegroup_name 
        [ CONTAINS FILESTREAM ]
    | REMOVE FILEGROUP filegroup_name 
    | MODIFY FILEGROUP filegroup_name
        { <filegroup_updatability_option> 
        | DEFAULT
        | NAME = new_filegroup_name 
        }
}
<filegroup_updatability_option>::=
{
    { READONLY | READWRITE } 
    | { READ_ONLY | READ_WRITE }
}

引數

<add_or_modify_files>::=

指定要新增、移除或修改的檔案。

database_name
這是要修改的資料庫名稱。
ADD FILE
將檔案加入資料庫中。
- TO FILEGROUP { filegroup_name }
  指定要加入指定之檔案的檔案群組。若要顯示目前檔案群組及目前是預設值的檔案群組，請使用 sys.filegroups 目錄檢視。
ADD LOG FILE
將記錄檔加入指定的資料庫中。
REMOVE FILE logical_file_name
從 SQL Server 執行個體中移除邏輯檔案描述和刪除實體檔案。除非檔案是空的，否則無法移除檔案。
- logical_file_name
  這是在參考檔案時，SQL Server 所用的邏輯名稱。
MODIFY FILE
指定應該修改的檔案。每次只能變更一個 <filespec> 屬性。您必須在 <filespec> 中指定 NAME，以識別要修改的檔案。如果指定了 SIZE，新的大小必須大於目前檔案大小。

若要修改資料檔或記錄檔的邏輯名稱，請在 NAME 子句中指定要重新命名的邏輯檔案名稱，並在 NEWNAME 子句中指定檔案的新邏輯名稱。例如：
```
MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name ) 
```
若要將資料檔或記錄檔移至新的位置，請在 NAME 子句中指定目前的邏輯檔案名稱，並在 FILENAME 子句中指定新的路徑和作業系統檔案名稱。例如：
```
MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )
```
當您移動全文檢索目錄時，只能在 FILENAME 子句中指定新的路徑。請勿指定作業系統檔案名稱。

如需詳細資訊，請參閱＜移動資料庫檔案＞。

如果是 FILESTREAM 檔案群組，可以在線上修改 NAME。可以在線上修改 FILENAME；但是，要等到實際重新放置容器且伺服器關閉並重新啟動之後，變更才會生效。

您可以將 FILESTREAM 檔案設定為 OFFLINE。當 FILESTREAM 檔案離線時，它的父檔案群組會在內部標示為離線；因此，該檔案群組內 FILESTREAM 資料的所有存取都將失敗。

[!附註]

自主資料庫中無法使用 <add_or_modify_files> 選項。

<filespec>::=

控制檔案屬性。

NAME logical_file_name
指定檔案的邏輯名稱。
- logical_file_name
  這是在參考檔案時，SQL Server 執行個體所用的邏輯名稱。
NEWNAME new_logical_file_name
指定檔案的新邏輯名稱。
- new_logical_file_name
  這是要取代現有邏輯檔案名稱的名稱。這個名稱在資料庫內必須是唯一的，且必須符合識別碼的規則。這個名稱可以是字元或 Unicode 常數、正規識別碼，或分隔的識別碼。
FILENAME { 'os_file_name' | 'filestream_path' }
指定作業系統 (實體) 檔案名稱。
- ' os_file_name '
  如果是標準 (ROWS) 檔案群組，這就是當您建立檔案時，作業系統所用的路徑和檔案名稱。這個檔案必須在安裝了 SQL Server 的伺服器中。在執行 ALTER DATABASE 陳述式之前，指定的路徑必須已經存在。
  
  當指定檔案的 UNC 路徑時，無法設定 SIZE、MAXSIZE 和 FILEGROWTH 參數。
  
  [!附註]
  
  系統資料庫無法位於 UNC 共用目錄。
  
  除非檔案是唯讀的次要檔案，或資料庫是唯讀的，否則，不應將資料檔放在壓縮的檔案系統中。記錄檔永遠不應放在壓縮的檔案系統中。
  
  如果檔案在原始資料分割中，os_file_name 只能指定現有原始資料分割的磁碟機代號。每個原始資料分割只能放置一個檔案。
- ' filestream_path '
  如果是 FILESTREAM 檔案群組，FILENAME 會參考儲存 FILESTREAM 資料所在的路徑。到最後一個資料夾為止的路徑必須存在，而最後一個資料夾則不得存在。例如，如果您指定 C:\MyFiles\MyFilestreamData 路徑，則在您執行 ALTER DATABASE 之前，C:\MyFiles 必須存在，但是 MyFilestreamData 資料夾不得存在。
  
  SIZE 和 FILEGROWTH 屬性不會套用到 FILESTREAM 檔案群組。
SIZE size
指定檔案大小。 SIZE 不會套用到 FILESTREAM 檔案群組。
- size
  這是檔案的大小。
  
  當設定 ADD FILE 來指定時，size 是檔案的起始大小。當設定 MODIFY FILE 來指定時，size 是檔案的新大小，且必須大於目前的檔案大小。
  
  當未提供主要檔案的 size 時，SQL Server 會採用 model 資料庫中主要檔案的大小。當指定了次要資料檔或記錄檔，但未指定檔案的大小 (size ) 時，Database Engine 會使檔案成為 1 MB。
  
  您可以利用 KB、MB、GB 和 TB 後置詞來指定千位元組、百萬位元組、十億位元組或兆位元組。預設值是 MB。請指定不包括小數的整數。若要指定 MB 的小數，請將數字乘以 1024，以便將值轉換成 KB。例如，請指定 1536 KB，而不要指定 1.5 MB (1.5 x 1024 = 1536)。
MAXSIZE { max_size| UNLIMITED }
指定檔案所能成長的大小上限。
- max_size
  這是檔案大小上限。您可以利用 KB、MB、GB 和 TB 後置詞來指定千位元組、百萬位元組、十億位元組或兆位元組。預設值是 MB。請指定不包括小數的整數。如果未指定 max_size，檔案大小會增加到磁碟已滿。
- UNLIMITED
  指定檔案可成長到磁碟已滿。在 SQL Server 中，指定為無限成長的記錄檔大小上限是 2 TB，資料檔的大小上限是 16 TB。為 FILESTREAM 容器指定這個選項時，沒有大小上限。它會繼續成長，直到磁碟已滿。
FILEGROWTH growth_increment
指定檔案的自動成長遞增。檔案的 FILEGROWTH 設定不能超過 MAXSIZE 設定。 FILEGROWTH 不會套用到 FILESTREAM 檔案群組。
- growth_increment
  這是每次需要新空間時，檔案所增加的空間量。
  
  您可以利用 MB、KB、GB、TB 或百分比 (%) 來指定這個值。如果指定的數字不含 MB、KB 或 % 後置詞，預設值是 MB。當指定 % 時，成長遞增大小便是遞增發生時，檔案大小的指定百分比。指定的大小會捨入到最接近 64 KB。
  
  0 值表示將自動成長設為關閉，且不允許任何其他空間。
  
  如果未指定 FILEGROWTH，資料檔的預設值是 1 MB，記錄檔的預設值是 10%，最小值是 64 KB。
  
  [!附註]
  
  從 SQL Server 2005 開始，資料檔的預設成長遞增已從 10% 改成 1 MB。記錄檔預設值 10% 維持不變。

OFFLINE
將檔案設成離線，使檔案群組中的所有物件都無法存取。

注意
請只在檔案損毀且可以還原時，才使用這個選項。設為 OFFLINE 的檔案，只能藉由從備份中還原檔案來設成線上狀態。如需有關還原單一檔案的詳細資訊，請參閱＜RESTORE (Transact-SQL)＞。

[!附註]

自主資料庫中無法使用 <filespec> 選項。

<add_or_modify_filegroups>::=

在資料庫中新增、修改或移除檔案群組。

ADD FILEGROUP filegroup_name
將檔案群組加入資料庫中。
CONTAINS FILESTREAM
指定檔案群組會將 FILESTREAM 二進位大型物件 (BLOB) 儲存在檔案系統中。
REMOVE FILEGROUP filegroup_name
從資料庫中移除檔案群組。除非檔案群組是空的，否則無法移除檔案群組。請先移除檔案群組中的所有檔案。如需詳細資訊，請參閱本主題前面的＜REMOVE FILE logical_file_name＞。

[!附註]

除非 FILESTREAM 記憶體回收行程已移除 FILESTREAM 容器中的所有檔案，否則移除 FILESTREAM 容器的 ALTER DATABASE REMOVE FILE 作業會失敗並傳回錯誤訊息。請參閱本主題後面的＜備註＞中的＜移除 FILESTREAM 容器＞一節。
MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME **=**new_filegroup_name }
將狀態設成 READ_ONLY 或 READ_WRITE，使檔案群組成為資料庫的預設檔案群組，或變更檔案群組名稱，即可修改檔案群組。
- <filegroup_updatability_option>
  將檔案群組的屬性設成唯讀或讀取/寫入。
- DEFAULT
  將預設資料庫檔案群組改為 filegroup_name。資料庫中只能有一個檔案群組是預設檔案群組。如需詳細資訊，請參閱＜資料庫檔案與檔案群組＞。
- NAME = new_filegroup_name
  將檔案群組名稱改為 new_filegroup_name。

<filegroup_updatability_option>::=

將檔案群組的屬性設成唯讀或讀取/寫入。

READ_ONLY | READONLY
將檔案群組指定成唯讀狀態。不允許更新其中的物件。主要檔案群組不能設為唯讀。若要變更這個狀態，您必須具有資料庫的獨佔存取權。如需詳細資訊，請參閱 SINGLE_USER 子句。

由於唯讀資料庫不允許修改資料，因此，會出現下列情況：
- 在系統開機時跳過自動復原。
- 不可能壓縮資料庫。
- 唯讀資料庫不會出現鎖定。因此，查詢效能會比較快。
[!附註]

在未來的 Microsoft SQL Server 版本中，將移除 READONLY 關鍵字。請避免在新的開發工作中使用 READONLY，並規劃修改目前在使用 READONLY 的應用程式。請改用 READ_ONLY。
READ_WRITE | READWRITE
將群組指定成 READ_WRITE 狀態。檔案群組中的物件可以更新。若要變更這個狀態，您必須具有資料庫的獨佔存取權。如需詳細資訊，請參閱 SINGLE_USER 子句。

[!附註]

在未來的 Microsoft SQL Server 版本中，將移除 READWRITE 關鍵字。請避免在新的開發工作中使用 READWRITE，並規劃修改目前在使用 READWRITE 的應用程式。請改用 READ_WRITE。

您可以檢查 sys.databases 目錄檢視中的 is_read_only 資料行或 DATABASEPROPERTYEX 函數的 Updateability 屬性來判斷這些選項的狀態。

備註

若要縮小資料庫大小，請使用 DBCC SHRINKDATABASE。

當 BACKUP 陳述式在執行中，您不能新增或移除檔案。

每個資料庫最多可以指定 32,767 個檔案和 32,767 個檔案群組。

在 SQL Server 2005 或更新版本中，資料庫檔案狀態 (如線上或離線) 的維護與資料庫狀態無關。如需詳細資訊，請參閱＜檔案狀態＞。檔案群組內的檔案狀態決定了整個檔案群組的可用性。若要能夠使用檔案群組，檔案群組內的所有檔案都必須在線上。如果檔案群組離線，SQL 陳述式存取檔案群組的任何嘗試都會失敗，且會出現錯誤。當您建置 SELECT 陳述式的查詢計畫時，查詢最佳化工具會避開在離線檔案群組中的非叢集索引和索引檢視表。這樣會讓這些陳述式能夠執行成功。不過，如果這個離線檔案群組包含目標資料表的堆積或叢集索引，SELECT 陳述式便會失敗。另外，當資料表有任何索引在離線檔案群組中時，任何修改這份資料表的 INSERT、UPDATE 或 DELETE 陳述式都會失敗。

移動檔案

在 SQL Server 2005 或更新版本中，您可以在 FILENAME 中指定新位置來移動系統或使用者定義資料檔和記錄檔。在下列狀況中，這非常有用：

失敗復原。例如，資料庫處於可疑模式或硬體故障所造成的關閉。
計畫的重新放置
排程的磁碟維護重新放置

如需詳細資訊，請參閱＜移動資料庫檔案＞。

初始化檔案

依預設，資料檔和記錄檔初始化的方式是在您執行下列作業之一時，在檔案中填入 0：

建立資料庫
將檔案加入現有的資料庫
增加現有檔案的大小
還原資料庫或檔案群組

資料檔可以立即初始化。這可以加快這些檔案作業的執行速度。

移除 FILESTREAM 容器

即使 FILESTREAM 容器已使用 "DBCC SHRINKFILE" 作業來清空，基於各種系統維護原因，資料庫可能仍然需要維護已刪除之檔案的參考。可安全執行時，sp_filestream_force_garbage_collection (Transact-SQL) 會執行 FILESTREAM 記憶體回收行程來移除這些檔案。除非 FILESTREAM 記憶體回收行程已移除 FILESTREAM 容器中的所有檔案，否則移除 FILESTREAM 容器的 ALTER DATABASE REMOVE FILE 作業會失敗並傳回錯誤訊息。以下是移除 FILESTREAM 容器的建議處理序。

執行 DBCC SHRINKFILE (Transact-SQL) 搭配 EMPTYFILE 選項，將此容器中的使用中內容移至其他容器。
確定已透過 FULL 或 BULK_LOGGED 復原模式建立記錄備份。
確定已執行複寫記錄讀取器作業 (如果相關)。
執行 sp_filestream_force_garbage_collection (Transact-SQL)，強制記憶體回收行程刪除此容器中任何不再需要的檔案。
執行 ALTER DATABASE 搭配 REMOVE FILE 選項，移除這個容器。
再次重複步驟 2 到 4，以完成記憶體回收。
使用 ALTER Database...REMOVE FILE 移除此容器。

範例

A.將檔案加入資料庫中

下列範例會將 5 MB 的資料檔加入 AdventureWorks2012 資料庫中。

USE master;
GO
ALTER DATABASE AdventureWorks2012 
ADD FILE 
(
    NAME = Test1dat2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat2.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

B.將含有兩個檔案的檔案群組加入資料庫中

下列範例會在 AdventureWorks2012 資料庫中建立 Test1FG1 檔案群組，且會將兩個 5 MB 的檔案加入檔案群組中。

USE master
GO
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2012 
ADD FILE 
(
    NAME = test1dat3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1;
GO

C.將兩個記錄檔加入資料庫中

下列範例會將兩個 5 MB 的記錄檔加入 AdventureWorks2012 資料庫中。

USE master;
GO
ALTER DATABASE AdventureWorks2012 
ADD LOG FILE 
(
    NAME = test1log2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test2log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1log3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test3log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

D.從資料庫中移除檔案

下列範例會移除 B 範例中所加入的其中一個檔案。

USE master;
GO
ALTER DATABASE AdventureWorks2012
REMOVE FILE test1dat4;
GO

E.修改檔案

下列範例會增加 B 範例中所加入的其中一個檔案的大小。

USE master;
GO
ALTER DATABASE AdventureWorks2012 
MODIFY FILE
    (NAME = test1dat3,
    SIZE = 20MB);
GO

F.將檔案移到新位置

下列範例會將 A 範例中所建立的 Test1dat2 檔移至新目錄中。

[!附註]

您必須實際上將檔案移到新目錄之後，才能執行這個範例。之後，請停止再啟動 SQL Server 的執行個體，或使 AdventureWorks2012 資料庫離線 (OFFLINE) 再連接 (ONLINE) 來實作變更。

USE master;
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILE
(
    NAME = Test1dat2,
    FILENAME = N'c:\t1dat2.ndf'
);
GO

G.將 tempdb 移到新位置

下列範例會將 tempdb 從磁碟中目前的位置移到另一個磁碟位置。由於在每次啟動 MSSQLSERVER 服務時都會重新建立 tempdb，因此您不需要實際移動資料和記錄檔。在步驟 3 重新啟動此服務時，將會建立檔案。在重新啟動此服務之前，tempdb 會繼續在現有的位置運作。

決定 tempdb 資料庫的邏輯檔案名稱，及它們目前在磁碟中的位置。

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO

請利用 ALTER DATABASE 來變更每個檔案的位置。

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE  tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
GO

停止和重新啟動 SQL Server 執行個體。

確認檔案變更。

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

將 tempdb.mdf 和 templog.ldf 檔案從它們的原始位置刪除。

H.使檔案群組成為預設值

下列範例會使 B 範例所建立的 Test1FG1 檔案群組成為預設檔案群組。之後，預設檔案群組會重設為 PRIMARY 檔案群組。請注意，您必須用方括號或引號來分隔 PRIMARY。

USE master;
GO
ALTER DATABASE AdventureWorks2012 
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks2012 
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO

I.使用 ALTER DATABASE 加入檔案群組

下列範例會將包含 FILESTREAM 子句的 FILEGROUP 加入 FileStreamPhotoDB 資料庫。

--Create and add a FILEGROUP that CONTAINS the FILESTREAM clause to
--the FileStreamPhotoDB database.
ALTER DATABASE FileStreamPhotoDB
ADD FILEGROUP TodaysPhotoShoot
CONTAINS FILESTREAM
GO

--Add a file for storing database photos to FILEGROUP 
ALTER DATABASE FileStreamPhotoDB
ADD FILE
(
    NAME= 'PhotoShoot1',
    FILENAME = 'C:\Users\Administrator\Pictures\TodaysPhotoShoot.ndf'
)
TO FILEGROUP TodaysPhotoShoot
GO