移動系統資料庫

適用於：SQL Server

本文描述了如何在 SQL Server 中移動系統資料庫。 在下列情況下，移動系統資料庫可能有用：

• 失敗復原。 例如，資料庫因硬體失敗而處於可以模式或被關閉。

• 計畫的重新放置。

• 排程的磁碟維護重新放置。

下列程序適用於在相同 SQL Server 執行個體內移動資料庫檔案。 若要將資料庫移至另一個 SQL Server 執行個體或另一部伺服器，請使用備份與還原作業。

本文中的程式需要資料庫檔案的邏輯名稱。 若要取得名稱，請在 sys.master_files 目錄檢視中查詢名稱資料行。

重要

如果您移動系統資料庫，並且稍後重建 master 資料庫，您必須再次移動系統資料庫，因為重建作業會將所有系統資料庫安裝到其預設位置。

移動此系統資料庫

若要將系統資料庫資料或記錄檔移動為計劃內重新放置或排程維護作業的一部分，請遵循下列步驟。 這包括 model、msdb 和 tempdb 系統資料庫。

重要

此程式適用於 master 和 Resource 資料庫以外的所有系統資料庫。 如需了解移動 master 資料庫的步驟，請參閱本文稍後的內容。 無法移動 Resource 資料庫。

1. 檢閱 sys.master_files 目錄檢視，以記錄您想要移動之資料庫檔案的現有位置。

2. 確認 SQL Server 資料庫引擎的服務帳戶具有檔案新位置的完整權限。 如需詳細資訊，請參閱設定 Windows 服務帳戶與權限。 如果資料庫引擎服務帳戶無法控制其新位置中的檔案，SQL Server 執行個體不會啟動。

3. 若要移動每個資料庫檔案，請執行下列陳述式。

   ALTER DATABASE database_name
       MODIFY FILE (NAME = logical_name, FILENAME = 'new_path\os_file_name');
   

   在服務重新啟動之前，資料庫會繼續使用現有位置中的資料和記錄檔。

4. 停止 SQL Server 執行個體以執行維護。 如需詳細資訊，請參閱啟動、停止、暫停、繼續和重新啟動 SQL Server 服務。

5. 將資料庫檔案或檔案複製到新位置。 對於 tempdb 系統資料庫不需要此步驟，系統資料庫會自動在新位置中建立這些檔案。

6. 重新啟動 SQL Server 或伺服器的執行個體。 如需詳細資訊，請參閱啟動、停止、暫停、繼續和重新啟動 SQL Server 服務。

7. 執行下列查詢來確認檔案變更。 系統資料庫應報告新的實體檔案位置。

   SELECT name,
          physical_name AS CurrentLocation,
          state_desc
   FROM sys.master_files
   WHERE database_id = DB_ID(N'<database_name>');
   

8. 由於在步驟 5 中，您已複製而不是移動資料庫檔案，因此您現在可從之前的位置安全地刪除未使用的資料庫檔案。

後續追蹤：移動 msdb 系統資料庫之後

如果移動 msdb 資料庫並設定 Database Mail，請完成下列額外步驟。

1. 執行下列查詢，確認已針對 msdb 資料庫啟用 Service Broker。

   SELECT is_broker_enabled
   FROM sys.databases
   WHERE name = N'msdb';
   

   如果未針對 msdb 啟用 Service Broker，則必須重新啟用 Database Mail 才能運作。 如需詳細資訊，請參閱 ALTER DATABASE ... SET ENABLE_BROKER。

   ALTER DATABASE msdb
       SET ENABLE_BROKER
       WITH ROLLBACK IMMEDIATE;
   

   確認 is_broker_enabled 的值現在是 1。

2. 藉由傳送測試郵件來確認 Database Mail 是否正常運作。

失敗復原程序

如果由於硬體故障而必須移動檔案，請遵循下列步驟，將檔案重新放置到新位置。 此程式適用於 master 和 Resource 資料庫以外的所有系統資料庫。 下列範例使用 Windows 命令列提示字元和 sqlcmd 公用程式。

重要

如果無法啟動資料庫，且資料庫處於可疑模式或未復原狀態，則只有系統管理員固定角色的成員才能移動檔案。

1. 確認 SQL Server 資料庫引擎的服務帳戶具有檔案新位置的完整權限。 如需詳細資訊，請參閱設定 Windows 服務帳戶與權限。 如果資料庫引擎服務帳戶無法控制其新位置中的檔案，SQL Server 執行個體不會啟動。

2. 如果 SQL Server 執行個體已啟動，請停止。

3. 在命令提示字元中輸入下列其中一個命令，以僅限 master 修復模式啟動 SQL Server 的執行個體。 使用啟動參數 3608 防止 SQL Server 自動啟動並復原任何資料庫，但 master 資料庫除外。 如需詳細資訊，請參閱啟動參數和 TF3608。

   這些命令中指定的參數會區分大小寫。 如果沒有如下所示指定參數，命令會失敗。

   針對預設的 (MSSQLSERVER) 執行個體，執行下列命令：

   NET START MSSQLSERVER /f /T3608
   

   若是具名執行個體，請執行下列命令：

   NET START MSSQL$instancename /f /T3608
   

   如需詳細資訊，請參閱啟動、停止、暫停、繼續和重新啟動 SQL Server 服務。

4. 在具有追蹤旗標 3608 和 /f 的服務啟動之後，立即啟動與伺服器的 sqlcmd 連線，以宣告可用的單一連線。 例如，在與預設 (MSSQLSERVER) 執行個體相同的伺服器上本機執行 sqlcmd，並使用 Active Directory 整合驗證來連線時，請執行下列命令：

   sqlcmd
   

   使用 Active Directory 整合驗證連線至本機伺服器上的具名執行個體：

   sqlcmd -S localhost\instancename
   

   如需有關 sqlcmd 語法的詳細資訊，請參閱 sqlcmd utility。

   若要移動每個檔案，請使用 sqlcmd 命令或 SQL Server Management Studio 來執行下列陳述式。 如需有關使用 sqlcmd 公用程式的更多資訊，請參閱 sqlcmd 使用公用程式。 開啟 sqlcmd 工作階段之後，請針對要移動的每個檔案執行下列陳述式一次：

   ALTER DATABASE database_name
       MODIFY FILE (NAME = logical_name, FILENAME = 'new_path\os_file_name');
   GO
   

5. 離開 sqlcmd 公用程式或 SQL Server Management Studio。

6. 停止 SQL Server 的執行個體。 例如，在命令列提示字元中執行 NET STOP MSSQLSERVER。

7. 將一個或多個檔案複製到新位置。

8. 重新啟動 SQL Server 的執行個體。 例如，在命令列提示字元中執行 NET START MSSQLSERVER。

9. 執行下列查詢來確認檔案變更。

   SELECT name,
          physical_name AS CurrentLocation,
          state_desc
   FROM sys.master_files
   WHERE database_id = DB_ID(N'<database_name>');
   

10. 由於在步驟 7 中，您已複製而不是移動資料庫檔案，因此您現在可從之前的位置安全地刪除未使用的資料庫檔案。

移動master資料庫

若要移動 master 資料庫，請遵循下列步驟。

1. 確認 SQL Server 資料庫引擎的服務帳戶具有檔案新位置的完整權限。 如需詳細資訊，請參閱設定 Windows 服務帳戶與權限。 如果資料庫引擎服務帳戶無法控制其新位置中的檔案，SQL Server 執行個體不會啟動。

2. 從開始功能表中，找出並啟動 SQL Server 組態管理員。 如需預期位置的詳細資訊，請參閱 SQL Server 組態管理員。

3. 在 SQL Server 服務節點中，以滑鼠右鍵按一下 SQL Server 執行個體 (例如 SQL Server (MSSQLSERVER)，然後選擇屬性。

4. 在 SQL Server (instance_name) 屬性對話框中，選取 [啟動參數] 索引標籤。

5. 在 [現有參數] 方塊中，選取 -d 參數。 在 [指定啟動參數] 方塊中，將參數變更為 master 資料檔案的新路徑。 選取 [更新] 來儲存變更。

6. 在 [現有參數] 方塊中，選取 -l 參數。 在 [指定啟動參數] 方塊中，將參數變更為 master 記錄檔案的新路徑。 選取 [更新] 來儲存變更。

   資料檔案的參數值必須遵循 -d 參數，而記錄檔的值必須遵循 -l 參數。 下列範例顯示 master 資料檔案預設位置的參數值。

   -dC:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA\master.mdf
   -lC:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
   

   如果 master 資料檔案的計劃內重新放置為 E:\SQLData，參數值將會如下所示變更：

   -dE:\SQLData\master.mdf
   -lE:\SQLData\mastlog.ldf
   

7. 選取 [確定] 以永久儲存變更，並關閉 SQL Server (instance_name) 屬性對話框。

8. 以滑鼠右鍵按一下執行個體名稱並選擇 [停止]，以停止 SQL Server 的執行個體。

9. 將 master.mdf 和 mastlog.ldf 檔案複製到新位置。

10. 重新啟動 SQL Server 的執行個體。

11. 藉由執行下列查詢來確認 master 資料庫的檔案變更。

    SELECT name,
           physical_name AS CurrentLocation,
           state_desc
    FROM sys.master_files
    WHERE database_id = DB_ID('master');
    

12. 此時，SQL Server 應正常執行。 不過，Microsoft 還建議調整位於 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance_ID\Setup 的登錄項目，其中 instance_ID 類似 MSSQL13.MSSQLSERVER。 在該 Hive 中，將 SQLDataRoot 值變更為 master 資料庫檔案新位置的新路徑。 無法更新登錄可能會導致修補和升級失敗。

13. 由於在步驟 9 中，您已複製而不是移動資料庫檔案，因此您現在可從之前的位置安全地刪除未使用的資料庫檔案。

移動資源資料庫

Resource 資料庫的位置為 \<drive>:\Program Files\Microsoft SQL Server\MSSQL\<version>.<instance_name>\MSSQL\Binn\。 無法移動資料庫。

後續追蹤：移動所有系統資料庫之後

如果您將所有系統資料庫移至新的磁碟驅動器或磁碟區，或具有不同磁碟機代號的另一部伺服器，請進行下列更新。

• 變更 SQL Server Agent 記錄路徑。 如果您不更新此路徑，SQL Server Agent 無法啟動。

• 變更資料庫預設位置。 如果指定預設位置的磁碟機代號和路徑不存在，建立新的資料庫可能會失敗。

變更 SQL Server Agent 記錄路徑

如果您已將所有系統資料庫移至新的磁碟區，或已移轉至具有不同磁碟機代號的另一部伺服器，且 SQL Agent 錯誤記錄檔 SQLAGENT.OUT 的路徑已不存在，請進行下列更新。

1. 在 SQL Server Management Studio 的 [物件總管] 中，展開 [SQL Server Agent]。

2. 以滑鼠右鍵按一下 [錯誤記錄檔] 資料夾，然後選取 [設定]。

3. 在 [設定 SQL Server Agent 錯誤記錄檔] 對話框中，指定 SQLAGENT.OUT 檔案的新位置。 預設位置為 C:\Program Files\Microsoft SQL Server\MSSQL\<version>.<instance_name>\MSSQL\Log\。

變更資料庫預設位置

1. 在 SQL Server Management Studio 的 [物件總管] 中，連線至所需的 SQL Server 執行個體。 在執行個體上按一下滑鼠右鍵，然後選取 [屬性]。

2. 在 [伺服器屬性] 對話框中，選取 [資料庫設定]。

3. 在 [資料庫預設位置] 下，瀏覽至資料和記錄檔的新位置。

4. 停止並啟動 SQL Server 服務以完成變更。

範例

A. 移動tempdb資料庫

做為計劃內重新放置的一部分，下列範例會將 tempdb 資料和記錄檔移至新位置。

提示

請利用此機會來檢閱您的 tempdb 檔案，以獲得最佳大小和位置。 如需詳細資訊，請參閱最佳化 SQL Server 中的 tempdb 效能。

由於每次啟動 SQL Server 的執行個體時都會重新建立 tempdb，因此不需要實際移動資料和記錄檔。 在步驟 4 重新啟動此服務時，將會在新位置建立檔案。 在服務重新啟動之前，tempdb 會繼續使用現有位置中的資料和記錄檔。

1. 判定 tempdb 資料庫的邏輯檔案名稱，及其目前在磁碟中的位置。

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

2. 確認 SQL Server 資料庫引擎的服務帳戶具有檔案新位置的完整權限。 如需詳細資訊，請參閱設定 Windows 服務帳戶與權限。 如果資料庫引擎服務帳戶無法控制其新位置中的檔案，SQL Server 執行個體不會啟動。

3. 請利用 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 = 'F:\SQLLog\templog.ldf');
   GO
   

   在服務重新啟動之前，tempdb 會繼續使用現有位置中的資料和記錄檔。

4. 停止 SQL Server 的執行個體並重新啟動。

5. 確認檔案變更。

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

6. 從其原始位置刪除未使用的 tempdb 檔案。

相關內容

• 資源資料庫
• tempdb 資料庫
• master 資料庫
• msdb 資料庫
• 模型資料庫
• 移動使用者資料庫
• 移動資料庫檔案
• 啟動、停止、暫停、繼續和重新啟動 SQL Server 服務
• ALTER DATABASE (Transact-SQL)
• 重建系統資料庫